/**{
"name":"Справочники",
    "1":"Основные"
}*/
CREATE GRAIN nci VERSION '1.10';
--Адреса, которые мы используем для организаций, ссылка на фиас
/**{"name":"Адреса организаций, ссылка на фиас","folderId":"1","dirTypeId":"1","isHierarchical":"false"}*/
CREATE TABLE org_address(
 /**{"name":"ИД","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"1"}*/
  address_id VARCHAR(36) NOT NULL,
 /**{"name":"Автономная область (ФИАС)","fieldTypeId":"7","refTable":"vw_fias_addrobj","refTableColumn":"offname","visualLength":"NULL","fieldOrderInSort":"2"}*/
  aoid VARCHAR(36),
 /**{"name":"Houde ID","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"3"}*/
  house_id VARCHAR(36),
 /**{"name":"Регион (ФИАС)","fieldTypeId":"7","refTable":"vw_fias_region","refTableColumn":"offname","visualLength":"NULL","fieldOrderInSort":"4"}*/
  region_id VARCHAR(36),
 /**{"name":"Наименование региона","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"5"}*/
  region_name VARCHAR(250),
 /**{"name":"Город (ФИАС)","fieldTypeId":"7","refTable":"vw_fias_city","refTableColumn":"offname","visualLength":"NULL","fieldOrderInSort":"6"}*/
  city_id VARCHAR(36),
 /**{"name":"Наименование города","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"7"}*/
  city_name VARCHAR(250),
 /**{"name":"Улица (ФИАС)","fieldTypeId":"7","refTable":"vw_fias_street","refTableColumn":"offname","visualLength":"NULL","fieldOrderInSort":"8"}*/
  street_id VARCHAR(36),
 /**{"name":"Наименование улицы","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"9"}*/
  street_name VARCHAR(250),
 /**{"name":"Адрес","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"10"}*/
  address VARCHAR(1000),
 /**{"name":"Адрес (дополнительно)","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"11"}*/
  address_additional VARCHAR(100),
 /**{"name":"Почтовый индекс","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"12"}*/
  postcode VARCHAR(6),
  CONSTRAINT pk_org_address PRIMARY KEY (address_id)
);
--
 /**{"name":"Центр аккредитации","folderId":"1","dirTypeId":"1","isHierarchical":"false"}*/
CREATE TABLE accred_center(
  /**{"name":"Идентификатор","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"1"}*/
  uid VARCHAR(36) NOT NULL,
  /**{"name":"Тип центра","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"2"}*/
  type VARCHAR(20) NOT NULL,
  /**{"name":"Сайт центра","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"3"}*/
  website VARCHAR(255),  
  /**{"name":"Телефон","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"4"}*/
  phone VARCHAR(255),
  /**{"name":"Эл.почта","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"5"}*/
  email VARCHAR(255),
  /**{"name":"Адрес","fieldTypeId":"7","refTable":"org_address","refTableColumn":"address","visualLength":"NULL","fieldOrderInSort":"6"}*/
  address_id VARCHAR(36),
  CONSTRAINT pk_accred_center PRIMARY KEY (uid)
);
--
/**{"name":"Тип аттестации","folderId":"1","dirTypeId":"1","isHierarchical":"false"}*/
CREATE TABLE attestation_type(
/**{"name":"Идентификатор","fieldTypeId":"5","visualLength":"NULL","fieldOrderInSort":"0"}*/
  id INT NOT NULL IDENTITY,
  /**{"name":"Наименование","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"1"}*/
  name VARCHAR(255) NOT NULL,
  CONSTRAINT pk_attestation_form PRIMARY KEY (id)
);
--
/**{"name":"Формы контроля","folderId":"1","dirTypeId":"1","isHierarchical":"false"}*/
CREATE TABLE control_form_type(
  /**{"name":"Идентификатор","fieldTypeId":"5","visualLength":"NULL","fieldOrderInSort":"0"}*/
  id INT NOT NULL IDENTITY,
 /**{"name":"Наименование","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"1"}*/
  name VARCHAR(255),
  CONSTRAINT pk_control_form_type PRIMARY KEY (id)
);
--
/**{"name":"Дополнительные атрибуты КДПП","folderId":"1","dirTypeId":"1","isHierarchical":"false"}*/
CREATE TABLE cprogram_attr_list(
/**{"name":"Идентификатор","fieldTypeId":"5","visualLength":"NULL","fieldOrderInSort":"0"}*/
  id INT NOT NULL IDENTITY,
 /**{"name":"Наименование","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"1"}*/
  name VARCHAR(255) NOT NULL,
 /**{"name":"Описание","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"2"}*/
  description TEXT,
  /**{"name":"Обязательный (да/нет)", "fieldTypeId":"1", "visualLength":"NULL", "fieldOrderInSort":"3"}*/
  required BIT DEFAULT 'TRUE',
/**{"name":"Порядок отображения в карточке","fieldTypeId":"5","visualLength":"NULL","fieldOrderInSort":"4"}*/
  sort INT,
 --Тип значения - храним json с типом и параметрами выбора (для селектора, мультиселектора, селекта)
 /**{"name":"Тип значения ","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"5"}*/
  value_type TEXT,
  CONSTRAINT pk_cprogram_attr_list PRIMARY KEY (id)
);
--
/**{"name":"Типы образовательных мероприятий","folderId":"1","dirTypeId":"1","isHierarchical":"false"}*/
CREATE TABLE edu_event_type(
/**{"name":"Идентификатор","fieldTypeId":"5","visualLength":"NULL","fieldOrderInSort":"0"}*/
  id INT NOT NULL IDENTITY,
 /**{"name":"Наименование","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"1"}*/
  name VARCHAR(100) NOT NULL,
  CONSTRAINT pk_edu_event_type PRIMARY KEY (id)
);
--
/**{"name":"Образовательные организации","folderId":"1","dirTypeId":"1","isHierarchical":"false"}*/
CREATE TABLE edu_organization(
/**{"name":"Идентификатор","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"1"}*/
  uid VARCHAR(36) NOT NULL,
 /**{"name":"Наименование","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"2"}*/  
  name TEXT NOT NULL,
 /**{"name":"Краткое наименование","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"3"}*/
  shortname VARCHAR(255),
 /**{"name":"Аббревиатура","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"4"}*/
  abbreviation VARCHAR(255),
 /**{"name":"Сайт","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"5"}*/
  website VARCHAR(255),
 /**{"name":"Телефон","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"6"}*/
  phone VARCHAR(255),
 /**{"name":"Эл.почта","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"7"}*/
  email VARCHAR(255),
  /**{"name":"Актуальная", "fieldTypeId":"1", "visualLength":"NULL", "fieldOrderInSort":"8"}*/
  actual BIT DEFAULT 'TRUE',
 /**{"name":"Адрес","fieldTypeId":"7","refTable":"org_address","refTableColumn":"address","visualLength":"NULL","fieldOrderInSort":"9"}*/
  address_id VARCHAR(36),
  /**{"name":"Вид","fieldTypeId":"7","refTable":"org_type","refTableColumn":"name","visualLength":"NULL","fieldOrderInSort":"10"}*/
  org_type_id INT NOT NULL DEFAULT 1,
 /**{"name":"Подразделение, отвечающее за ДПО","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"11"}*/
  dpo_department VARCHAR(255),
 /**{"name":"ФИО ответственного лица (ОЛ), подающего данные","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"12"}*/
  resp_person VARCHAR(255),
 /**{"name":"Должность ОЛ","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"13"}*/
  resp_person_post VARCHAR(255),
 /**{"name":"Контактный телефон ОЛ","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"14"}*/
  resp_person_phone VARCHAR(255),
 /**{"name":"Эл.почта ОЛ","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"15"}*/
  resp_person_email VARCHAR(255),
   /**{"name":"Подчинение Минздраву", "fieldTypeId":"1", "visualLength":"NULL", "fieldOrderInSort":"16"}*/
  subordinate_ministry bit,
  CONSTRAINT pk_edu_organization PRIMARY KEY (uid)
);
--
/**{"name":"Подвид организации","folderId":"1","dirTypeId":"1","isHierarchical":"false"}*/
CREATE TABLE org_subtype(
/**{"name":"Идентификатор","fieldTypeId":"5","visualLength":"NULL","fieldOrderInSort":"0"}*/
  id INT NOT NULL IDENTITY,
  /**{"name":"Основной вид","fieldTypeId":"7","refTable":"org_type","refTableColumn":"name","visualLength":"NULL","fieldOrderInSort":"9"}*/
  main_type int not null,
 /**{"name":"Наименование","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"2"}*/
  name VARCHAR(255) NOT NULL,
  CONSTRAINT pk_org_subtype PRIMARY KEY (id)
);
--
/**{"name":"Вид организации","folderId":"1","dirTypeId":"1","isHierarchical":"false"}*/
CREATE TABLE org_type(
/**{"name":"Идентификатор","fieldTypeId":"5","visualLength":"NULL","fieldOrderInSort":"0"}*/
  id INT NOT NULL IDENTITY,
 /**{"name":"Наименование","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"1"}*/
  name VARCHAR(255) NOT NULL,
  CONSTRAINT pk_org_type PRIMARY KEY (id)
);
--
/**{"name":"Организации по подтипам","folderId":"1","dirTypeId":"1","isHierarchical":"false"}*/
CREATE TABLE subtype_by_org(
/**{"name":"Организация","fieldTypeId":"7","refTable":"edu_organization","refTableColumn":"name","visualLength":"NULL","fieldOrderInSort":"1"}*/
org_id VARCHAR(36) NOT NULL,
/**{"name":"Основной вид","fieldTypeId":"7","refTable":"org_subtype","refTableColumn":"name","visualLength":"NULL","fieldOrderInSort":"2"}*/
subtype_id INT NOT NULL,
CONSTRAINT pk_subtype_by_org PRIMARY KeY (org_id, subtype_id)
);
--
/**{"name":"Тип активности","folderId":"1","dirTypeId":"1","isHierarchical":"false"}*/
CREATE TABLE activity_type(
/**{"name":"Идентификатор","fieldTypeId":"5","visualLength":"NULL","fieldOrderInSort":"0"}*/
  id INT NOT NULL IDENTITY,
 /**{"name":"Наименование","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"1"}*/
  name VARCHAR(255) NOT NULL,
  CONSTRAINT pk_activity_type PRIMARY KEY (id)
);
--
/**{"name":"Вид активности","folderId":"1","dirTypeId":"1","isHierarchical":"false"}*/
CREATE TABLE activity_kind(
/**{"name":"Идентификатор","fieldTypeId":"5","visualLength":"NULL","fieldOrderInSort":"0"}*/
  id INT NOT NULL IDENTITY,
/**{"name":"Основной вид","fieldTypeId":"7","refTable":"activity_type","refTableColumn":"name","visualLength":"NULL","fieldOrderInSort":"1"}*/  
  type_id int not null,
 /**{"name":"Наименование","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"2"}*/
  name VARCHAR(255) NOT NULL,
  CONSTRAINT pk_activity_king PRIMARY KEY (id)
);
--
/**{"name":"Тип активности организации","folderId":"1","dirTypeId":"1","isHierarchical":"false"}*/
CREATE TABLE org_activity_type(
/**{"name":"Организация","fieldTypeId":"7","refTable":"edu_organization","refTableColumn":"name","visualLength":"NULL","fieldOrderInSort":"1"}*/
  org_id VARCHAR(36) NOT NULL,
/**{"name":"Основной вид","fieldTypeId":"7","refTable":"activity_type","refTableColumn":"name","visualLength":"NULL","fieldOrderInSort":"2"}*/  
  type_id int not null,
  CONSTRAINT pk_org_activity_type PRIMARY KEY (org_id,type_id)
);
--
/**{"name":"Вид активности организации","folderId":"1","dirTypeId":"1","isHierarchical":"false"}*/
CREATE TABLE org_activity_kind(
/**{"name":"Организация","fieldTypeId":"7","refTable":"edu_organization","refTableColumn":"name","visualLength":"NULL","fieldOrderInSort":"1"}*/
  org_id VARCHAR(36) NOT NULL,
/**{"name":"Основной вид","fieldTypeId":"7","refTable":"activity_kind","refTableColumn":"name","visualLength":"NULL","fieldOrderInSort":"2"}*/  
  kind_id int not null,
  CONSTRAINT pk_org_activity_kind PRIMARY KEY (org_id,kind_id)
);
--
/**{"name":"Формы обучения","folderId":"1","dirTypeId":"1","isHierarchical":"false"}*/
CREATE TABLE education_form(
/**{"name":"Идентификатор","fieldTypeId":"5","visualLength":"NULL","fieldOrderInSort":"0"}*/
  id INT NOT NULL IDENTITY,
 /**{"name":"Наименование","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"1"}*/
  name VARCHAR(255),
  CONSTRAINT pk_education_form PRIMARY KEY (id)
);
--
/**{"name":"Основы обучения","folderId":"1","dirTypeId":"1","isHierarchical":"false"}*/
CREATE TABLE education_base(
/**{"name":"Идентификатор","fieldTypeId":"5","visualLength":"NULL","fieldOrderInSort":"0"}*/
  id INT NOT NULL IDENTITY,
 /**{"name":"Наименование","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"1"}*/
  name VARCHAR(255),
  CONSTRAINT pk_education_base PRIMARY KEY (id)
);
--
/**{"name":"Уровень образования","folderId":"1","dirTypeId":"1","isHierarchical":"false"}*/
CREATE TABLE education_level(
/**{"name":"Идентификатор","fieldTypeId":"5","visualLength":"NULL","fieldOrderInSort":"1"}*/
  id INT NOT NULL,
 /**{"name":"Наименование","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"2"}*/
  name TEXT,
  CONSTRAINT pk_education_level PRIMARY KEY (id)
);
--
/**{"name":"Адреса прохождения циклов","folderId":"1","dirTypeId":"1","isHierarchical":"false"}*/
CREATE TABLE cycle_address(
 /**{"name":"Ид цикла","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"1"}*/
  cycle_id VARCHAR(36) NOT NULL,
 /**{"name":"Регион, ФИАС","fieldTypeId":"7","refTable":"vw_fias_addrobj","refTableColumn":"offname","visualLength":"NULL","fieldOrderInSort":"2"}*/
  aoid VARCHAR(36),
 /**{"name":"Houde ID","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"3"}*/
  house_id VARCHAR(36),
 /**{"name":"Область, ФИАС","fieldTypeId":"7","refTable":"vw_fias_region","refTableColumn":"offname","visualLength":"NULL","fieldOrderInSort":"4"}*/
  region_id VARCHAR(36),
 /**{"name":"Наименование области","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"5"}*/
  region_name VARCHAR(250),
 /**{"name":"Город, ФИАС","fieldTypeId":"7","refTable":"vw_fias_city","refTableColumn":"offname","visualLength":"NULL","fieldOrderInSort":"6"}*/
  city_id VARCHAR(36),
 /**{"name":"Наименование города","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"7"}*/
  city_name VARCHAR(250),
 /**{"name":"Улица, ФИАС","fieldTypeId":"7","refTable":"vw_fias_street","refTableColumn":"offname","visualLength":"NULL","fieldOrderInSort":"8"}*/
  street_id VARCHAR(36),
 /**{"name":"Наименование улицы","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"9"}*/
  street_name VARCHAR(250),
 /**{"name":"Адрес","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"10"}*/
  address VARCHAR(1000),
 /**{"name":"Адрес, дополнительно","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"11"}*/
  address_additional VARCHAR(100),
 /**{"name":"Почтовый индекс","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"12"}*/
  postcode VARCHAR(6),
  CONSTRAINT pk_cycle_address PRIMARY KEY (cycle_id)
);
--
/**{"name":"Физлица","folderId":"1","dirTypeId":"1","isHierarchical":"false"}*/
CREATE TABLE person(
 /**{"name":"ИД","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"1"}*/
  uid VARCHAR(255) NOT NULL,
 /**{"name":"СНИЛС","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"2"}*/
  snils VARCHAR(255),
 /**{"name":"Фамилия","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"3"}*/
  surname VARCHAR(255),
 /**{"name":"Имя","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"4"}*/
  name VARCHAR(255) NOT NULL,
 /**{"name":"Отчество","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"5"}*/
  patronymic VARCHAR(255),
 /**{"name":"Слитое ФИО","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"6"}*/
  fullname TEXT NOT NULL,
  /**{"name":"Дата рождения","fieldTypeId":"2","visualLength":"NULL","fieldOrderInSort":"7"}*/
  birthDate DATETIME,
 /**{"name":"Эл. почта","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"8"}*/
  email VARCHAR(255),
 /**{"name":"Телефон","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"9"}*/
  phone VARCHAR(250),
 /**{"name":"Дополнительный телефон","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"10"}*/
  phoneExtra VARCHAR(250),
  /**{"name":"Согласие на обработку персональных данных", "fieldTypeId":"1", "visualLength":"NULL", "fieldOrderInSort":"11"}*/
  personal_data_agree BIT DEFAULT 'FALSE',
 /**{"name":"Пол","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"12"}*/
  sex VARCHAR(36),
  /**{"name":"Есть в ФРМР", "fieldTypeId":"1", "visualLength":"NULL", "fieldOrderInSort":"13"}*/
  in_frmr BIT DEFAULT 'FALSE',
  CONSTRAINT pk_person PRIMARY KEY (uid)
);
--
/**{"name":"Связь Физлица - Образовательные организации","folderId":"1","dirTypeId":"1","isHierarchical":"false"}*/
CREATE TABLE person_edu_organization(
 /**{"name":"ИД","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"1"}*/
  uid VARCHAR(36) NOT NULL,
 /**{"name":"Физ.лицо","fieldTypeId":"7","refTable":"person","refTableColumn":"fullname","visualLength":"NULL","fieldOrderInSort":"2"}*/
  person_id VARCHAR(255) NOT NULL,
 /**{"name":"Физ.лицо","fieldTypeId":"7","refTable":"edu_organization","refTableColumn":"name","visualLength":"NULL","fieldOrderInSort":"3"}*/
  edu_organization_id VARCHAR(36) NOT NULL,
 -- "staff": "Сотрудник организации", "supervised_student": "Курируемый слушатель"
 /**{"name":"Роль","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"4"}*/
  role VARCHAR(255),
  CONSTRAINT pk_person_edu_organization PRIMARY KEY (uid)
);
--
/**{"name":"Область","folderId":"1","dirTypeId":"1","isHierarchical":"false"}*/
CREATE TABLE staff_area(
/**{"name":"Идентификатор","fieldTypeId":"5","visualLength":"NULL","fieldOrderInSort":"0"}*/
  id INT NOT NULL IDENTITY,
 /**{"name":"Наименование","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"1"}*/
  name TEXT,
  CONSTRAINT pk_staff_area PRIMARY KEY (id)
);
--
/**{"name":"Градация персонала","folderId":"1","dirTypeId":"1","isHierarchical":"false"}*/
CREATE TABLE staff_scale(
/**{"name":"Идентификатор","fieldTypeId":"5","visualLength":"NULL","fieldOrderInSort":"0"}*/
  id INT NOT NULL IDENTITY,
 /**{"name":"Наименование","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"1"}*/
  name TEXT,
  CONSTRAINT pk_staff_scale PRIMARY KEY (id)
);
--
/**{"name":"Статусы","folderId":"1","dirTypeId":"1","isHierarchical":"false"}*/
CREATE TABLE status_list(
/**{"name":"Идентификатор","fieldTypeId":"5","visualLength":"NULL","fieldOrderInSort":"1"}*/
  id INT NOT NULL,
 /**{"name":"Наименование","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"2"}*/
  name VARCHAR(255) NOT NULL,
  CONSTRAINT pk_status_list PRIMARY KEY (id)
);
--
/**{"name":"Статусные модели","folderId":"1","dirTypeId":"1","isHierarchical":"false"}*/
CREATE TABLE status_model(
/**{"name":"Идентификатор","fieldTypeId":"5","visualLength":"NULL","fieldOrderInSort":"1"}*/
  id INT NOT NULL,
 /**{"name":"Наименование","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"2"}*/
  name VARCHAR(255) NOT NULL,
  CONSTRAINT pk_status_model PRIMARY KEY (id)
);
--
/**{"name":"Статусная модель - статусы","folderId":"1","dirTypeId":"1","isHierarchical":"false"}*/
CREATE TABLE status_model_status(
/**{"name":"Идентификатор","fieldTypeId":"5","visualLength":"NULL","fieldOrderInSort":"0"}*/
  id INT NOT NULL IDENTITY,
  /**{"name":"Статусная модель","fieldTypeId":"7","refTable":"status_model","refTableColumn":"name","visualLength":"NULL","fieldOrderInSort":"1"}*/
  model_id INT NOT NULL,
  /**{"name":"Статус 1","fieldTypeId":"7","refTable":"status_list","refTableColumn":"name","visualLength":"NULL","fieldOrderInSort":"2"}*/
  status_id1 INT NOT NULL,
  /**{"name":"Статус 2","fieldTypeId":"7","refTable":"status_list","refTableColumn":"name","visualLength":"NULL","fieldOrderInSort":"3"}*/
  status_id2 INT NOT NULL,
 /**{"name":"Наименование","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"4"}*/
  transition_text VARCHAR(250),
  CONSTRAINT pk_status_model_status PRIMARY KEY (id)
);

create table global_settings(
  id VARCHAR(36) NOT NULL,
  name VARCHAR(100) NOT NULL,
  value text NOT NULL,
  CONSTRAINT pk_global_settings PRIMARY KEY (id)
);

create table test_matrix (
  tm_id varchar(36) not null,
  tm_name varchar(50) not null,
  speciality_id varchar(36) not null,
  question_count int not null,
  params text,
  constraint pk_test_matrix primary key (tm_id)
);
CREATE INDEX idx_test_matrix_1 ON test_matrix(speciality_id);

create table test_matrix_themes (
  id INT NOT NULL IDENTITY,
  tm_id varchar(36) not null,
  package_id varchar(36) not null,
  theme_id varchar(36),
  question_count int not null,
  constraint pk_test_matrix_themes primary key (id)
);
CREATE INDEX idx_test_matrix_themes_1 ON test_matrix_themes(tm_id);
CREATE INDEX idx_test_matrix_themes_2 ON test_matrix_themes(package_id);

-- *** FOREIGN KEYS ***
ALTER TABLE accred_center ADD CONSTRAINT fk_accred_center_address FOREIGN KEY (address_id) REFERENCES nci.org_address(address_id);
ALTER TABLE edu_organization ADD CONSTRAINT fk_edu_org_address FOREIGN KEY (address_id) REFERENCES nci.org_address(address_id);
ALTER TABLE org_address ADD CONSTRAINT fk_org_address FOREIGN KEY (aoid) REFERENCES fias.addrobj(aoid) ON DELETE CASCADE;
ALTER TABLE org_address ADD CONSTRAINT fk_org_address_region FOREIGN KEY (region_id) REFERENCES fias.addrobj(aoid) ON DELETE SET NULL;
ALTER TABLE org_address ADD CONSTRAINT fk_org_address_city FOREIGN KEY (city_id) REFERENCES fias.addrobj(aoid) ON DELETE SET NULL;
ALTER TABLE org_address ADD CONSTRAINT fk_org_address_street FOREIGN KEY (street_id) REFERENCES fias.addrobj(aoid) ON DELETE SET NULL;
ALTER TABLE person_edu_organization ADD CONSTRAINT fk_nci_person_edu_orgaBD97D284 FOREIGN KEY (person_id) REFERENCES nci.person(uid);
ALTER TABLE person_edu_organization ADD CONSTRAINT fk_nci_person_edu_orgaEBA1CDA4 FOREIGN KEY (edu_organization_id) REFERENCES nci.edu_organization(uid);
ALTER TABLE status_model_status ADD CONSTRAINT fk_nci_status_model_stED508E1A FOREIGN KEY (model_id) REFERENCES nci.status_model(id);
ALTER TABLE status_model_status ADD CONSTRAINT fk_nci_status_model_stD48B5D77 FOREIGN KEY (status_id1) REFERENCES nci.status_list(id);
ALTER TABLE status_model_status ADD CONSTRAINT fk_nci_status_model_stD48B5D78 FOREIGN KEY (status_id2) REFERENCES nci.status_list(id);
ALTER TABLE org_subtype ADD CONSTRAINT fk_subtype_type FOREIGN KEY (main_type) REFERENCES nci.org_type(id);
ALTER TABLE subtype_by_org ADD CONSTRAINT fk_org_subtype FOREIGN KEY (org_id) REFERENCES nci.edu_organization(uid);
ALTER TABLE subtype_by_org ADD CONSTRAINT fk_org_subtype_type FOREIGN KEY (subtype_id) REFERENCES nci.org_subtype(id);
ALTER TABLE activity_kind ADD CONSTRAINT fk_kind_type FOREIGN KEY (type_id) REFERENCES nci.activity_type(id);
ALTER TABLE org_activity_type ADD CONSTRAINT fk_org_activity FOREIGN KEY (org_id) REFERENCES nci.edu_organization(uid);
ALTER TABLE org_activity_type ADD CONSTRAINT fk_org_activity_type FOREIGN KEY (type_id) REFERENCES nci.activity_type(id);
ALTER TABLE org_activity_kind ADD CONSTRAINT fk_org_kind FOREIGN KEY (org_id) REFERENCES nci.edu_organization(uid);
ALTER TABLE org_activity_kind ADD CONSTRAINT fk_org_activity_kind FOREIGN KEY (kind_id) REFERENCES nci.activity_kind(id);

-- *** INDICES ***
CREATE INDEX idx_accred_center ON accred_center(address_id);
CREATE INDEX idx_edu_organization ON edu_organization(address_id);
CREATE INDEX idx_org_address ON org_address(aoid);
CREATE INDEX idx_org_address_0 ON org_address(region_id);
CREATE INDEX idx_org_address_1 ON org_address(city_id);
CREATE INDEX idx_org_address_2 ON org_address(street_id);

-- *** VIEWS ***
create view vw_test_matrix_themes as
select
  tmt.id as id,
  tmt.tm_id as tm_id,
  tmt.package_id as package_id,
  qp.PackageName as package_name,
  tmt.theme_id as theme_id,
  qtt.ThemeName as theme_name,
  tmt.question_count as question_count
from test_matrix_themes tmt
  inner join qt.QtiPackage qp on qp.PackageID=tmt.package_id
  left join qt.QtiTheme qtt on qtt.ThemeID=tmt.theme_id;

create view vw_accred_center as
  select accred.uid as uid, accred.type as type, address.region_id as region_id, 
    address.region_name as region_name, address.city_id as city_id, address.city_name as city_name
    , address.address as address, address.address_additional as address_additional
    , accred.website as website, accred.phone as phone, accred.email as email
  from accred_center as accred
    LEFT join org_address as address on accred.address_id = address.address_id;

create view vw_city as
  select distinct region_id as region_id, city_id as city_id, city_name as city_name
    
  from org_address as org_address;

create view vw_edu_organization as
  select edu.uid as uid, edu.name as name, edu.shortname as shortname, edu.abbreviation as abbreviation
    , address.region_id as region_id, address.region_name as region_name, address.city_id as city_id
    , address.city_name as city_name, address.address as address, address.address_additional as address_additional
    , edu.website as website, edu.phone as phone, edu.email as email, edu.actual as actual, edu.subordinate_ministry as subordinate_ministry, edu.org_type_id as org_type_id
    
  from edu_organization as edu
    LEFT join org_address as address on address.address_id = edu.address_id;

create view vw_region as
  select distinct region_id as region_id, region_name as region_name
  from org_address as org_address;

create view vw_status_model_status as
  select sms.id as id, sms.model_id as model_id, sms.status_id1 as status_id1, sms.status_id2 as status_id2
    , sm.name as model_name, sl1.name as status_name1, sl2.name as status_name2, 
    sms.transition_text as transition_text
  from status_model_status as sms
    INNER join status_list as sl1 on sms.status_id1 = sl1.id
    INNER join status_list as sl2 on sms.status_id2 = sl2.id
    INNER join status_model as sm on sms.model_id = sm.id;

create view vw_supervised_student as
  select distinct peo.uid as peo_uid, p.uid as person_id, p.name as person_name, 
    p.surname as person_surname, p.patronymic as person_patronymic, p.snils as person_snils
    , p.fullname as person_fullname, p.sex as person_sex, p.email as person_email
    , p.personal_data_agree as personal_data_agree, eo.uid as edu_organization_id
    , eo.name as eo_name, eo.shortname as eo_shortname, eo.actual as eo_actual
  from person_edu_organization as peo
    INNER join person as p on peo.person_id = p.uid
    INNER join edu_organization as eo on peo.edu_organization_id = eo.uid
  where peo.role = 'supervised_student';

/**{"name":"ФИАС Регион","folderId":"1","dirTypeId":"2","isHierarchical":"false"}*/
create view vw_fias_addrobj as
select
	/**{"name":"ИД","fieldTypeId":"5","visualLength":"NULL","fieldOrderInSort":"0"}*/
	aoid as id, 
	 /**{"name":"Наименование","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"1"}*/
	offname
from fias.addrobj
where aolevel=1;

/**{"name":"ФИАС Область","folderId":"1","dirTypeId":"2","isHierarchical":"false"}*/
create view vw_fias_region as
select
	/**{"name":"ИД","fieldTypeId":"5","visualLength":"NULL","fieldOrderInSort":"0"}*/
	aoid as id, 
	 /**{"name":"Наименование","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"1"}*/
	offname
from fias.addrobj
where aolevel=3;

/**{"name":"ФИАС Населенные пункты","folderId":"1","dirTypeId":"2","isHierarchical":"false"}*/
create view vw_fias_city as
select
	/**{"name":"ИД","fieldTypeId":"5","visualLength":"NULL","fieldOrderInSort":"0"}*/
	aoid as id, 
	 /**{"name":"Наименование","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"1"}*/
	offname
from fias.addrobj
where aolevel=4 or aolevel=5 or aolevel=6;

/**{"name":"ФИАС Улицы","folderId":"1","dirTypeId":"2","isHierarchical":"false"}*/
create view vw_fias_street as
select
	/**{"name":"ИД","fieldTypeId":"5","visualLength":"NULL","fieldOrderInSort":"0"}*/
	aoid as id, 
	 /**{"name":"Наименование","fieldTypeId":"9","visualLength":"NULL","fieldOrderInSort":"1"}*/
	offname
from fias.addrobj
where aolevel=7;

create table speciality_commissions (
  id INT NOT NULL IDENTITY,
  speciality_id varchar(36) not null,
  report_type varchar(255) not null,
  member_fio varchar(36) null,
  member_position varchar(255) null,
  chairman BIT DEFAULT 'FALSE',
  constraint pk_speciality_commissions primary key (id)
);


CREATE TABLE speciality_commissions_view(
  uid VARCHAR(36) NOT NULL,
  name TEXT,
  shortname TEXT,
  education_level INT,
  sort TEXT,
  gia_report INT,
  cert_report INT,
  CONSTRAINT pk_speciality_commissions_view PRIMARY KEY (uid)
);