-- Create tables section -------------------------------------------------

-- Table REGIONS
CREATE TABLE REGIONS(
  REGION_ID Number NOT NULL,
  REGION_NAME Varchar2(200 CHAR),
  REGION_TYPE Number
);

-- Add keys for table REGIONS
ALTER TABLE REGIONS ADD CONSTRAINT PK_REGIONS PRIMARY KEY (REGION_ID);

-- Table and Columns comments section
COMMENT ON TABLE REGIONS IS 'Регионы';
COMMENT ON COLUMN REGIONS.REGION_ID IS 'ID региона (области, края, республики).';
COMMENT ON COLUMN REGIONS.REGION_NAME IS 'Название региона (области, края, республики)';
COMMENT ON COLUMN REGIONS.REGION_TYPE IS 'Тип региона (1 - область, 2 - республика, 3 - автономный округ, 4 - край, 5 - автономная область, 6 - город федерального значения)';

-- Table REGION2CODE
CREATE TABLE REGION2CODE(
  REGION_ID Number NOT NULL,
  REGION_CODE Number NOT NULL
);

-- Add keys for table REGION2CODE

ALTER TABLE REGION2CODE ADD CONSTRAINT PK_REGION2CODE PRIMARY KEY (REGION_ID,REGION_CODE);

-- Table and Columns comments section
  
COMMENT ON TABLE REGION2CODE IS 'Коды регионов';
COMMENT ON COLUMN REGION2CODE.REGION_ID IS 'ID региона (ссылка на REGIONS.REGION_ID)';
COMMENT ON COLUMN REGION2CODE.REGION_CODE IS 'Код региона';

-- Table CITIES
CREATE TABLE CITIES(
  CITY_ID Number NOT NULL,
  REGION_ID Number,
  CITY_NAME Varchar2(200 CHAR) NOT NULL,
  ADM_CENTER Number,
  IS_FEDERAL Number
);

-- Add keys for table CITIES
ALTER TABLE CITIES ADD CONSTRAINT PK_CITIES PRIMARY KEY (CITY_ID);

-- Table and Columns comments section
COMMENT ON TABLE CITIES IS 'Города (населенные пункты)';
COMMENT ON COLUMN CITIES.CITY_ID IS 'ID города';
COMMENT ON COLUMN CITIES.REGION_ID IS 'ID региона (ссылка на REGIONS.REGION_ID)';
COMMENT ON COLUMN CITIES.CITY_NAME IS 'Наименование города (населенного пункта)';
COMMENT ON COLUMN CITIES.ADM_CENTER IS 'Признак административного центра (0 - не является административным центром, 1 - является административным центром)';
COMMENT ON COLUMN CITIES.IS_FEDERAL IS 'Город федерального значения (0 - не является городом федерального значения, 1 - является городом федерального значения)';

-- Create relationships section ------------------------------------------------- 
ALTER TABLE REGION2CODE ADD CONSTRAINT Relationship2 FOREIGN KEY (REGION_ID) REFERENCES REGIONS (REGION_ID);
ALTER TABLE CITIES ADD CONSTRAINT Relationship5 FOREIGN KEY (REGION_ID) REFERENCES REGIONS (REGION_ID);

INSERT INTO SCHEMATEST.REGIONS VALUES (1, 'Москва', 6);
INSERT INTO SCHEMATEST.REGIONS VALUES (2, 'Санкт-Петербург', 6);
INSERT INTO SCHEMATEST.REGIONS VALUES (3, 'Севастополь', 6);
INSERT INTO SCHEMATEST.REGIONS VALUES (4, 'Свердловская область', 1);
INSERT INTO SCHEMATEST.REGIONS VALUES (5, 'Ненецкий автономный округ', 3);
INSERT INTO SCHEMATEST.REGIONS VALUES (6, 'Московская область', 1);
INSERT INTO SCHEMATEST.REGIONS VALUES (7, 'Ленинградская область', 1);

INSERT INTO SCHEMATEST.REGION2CODE VALUES (1, 77);
INSERT INTO SCHEMATEST.REGION2CODE VALUES (1, 97);
INSERT INTO SCHEMATEST.REGION2CODE VALUES (1, 99);
INSERT INTO SCHEMATEST.REGION2CODE VALUES (1, 177);
INSERT INTO SCHEMATEST.REGION2CODE VALUES (1, 199);
INSERT INTO SCHEMATEST.REGION2CODE VALUES (1, 197);
INSERT INTO SCHEMATEST.REGION2CODE VALUES (1, 777);
INSERT INTO SCHEMATEST.REGION2CODE VALUES (2, 78);
INSERT INTO SCHEMATEST.REGION2CODE VALUES (2, 98);
INSERT INTO SCHEMATEST.REGION2CODE VALUES (2, 178);
INSERT INTO SCHEMATEST.REGION2CODE VALUES (4, 66);
INSERT INTO SCHEMATEST.REGION2CODE VALUES (4, 96);
INSERT INTO SCHEMATEST.REGION2CODE VALUES (5, 83);
INSERT INTO SCHEMATEST.REGION2CODE VALUES (6, 50);
INSERT INTO SCHEMATEST.REGION2CODE VALUES (6, 90);
INSERT INTO SCHEMATEST.REGION2CODE VALUES (6, 150);
INSERT INTO SCHEMATEST.REGION2CODE VALUES (6, 190);
INSERT INTO SCHEMATEST.REGION2CODE VALUES (7, 47);

INSERT INTO SCHEMATEST.CITIES VALUES (1, 1, 'Москва', 1, 1);
INSERT INTO SCHEMATEST.CITIES VALUES (2, 2, 'Санкт-Петербург', 1, 1);
INSERT INTO SCHEMATEST.CITIES VALUES (3, 3, 'Севастополь', 1, 1);
INSERT INTO SCHEMATEST.CITIES VALUES (4, 4, 'Екатеринбург', 1, 0);
INSERT INTO SCHEMATEST.CITIES VALUES (5, 4, 'Нижний Тагил', 0, 0);
INSERT INTO SCHEMATEST.CITIES VALUES (6, 5, 'Нарьян-Мар', 1, 0);
INSERT INTO SCHEMATEST.CITIES VALUES (7, 6, 'Сергиев Посад', 0, 0);