CREATE GRAIN edu VERSION '1.07';

/**Курсы (компонент основной части программы КДПП)*/
CREATE TABLE course(
  uid VARCHAR(36) NOT NULL,
  name TEXT NOT NULL,
  edu_organization_id VARCHAR(36) NOT NULL,
  /**Структурное подразделение*/
  str_unit TEXT,
  speciality_id VARCHAR(36) NOT NULL,
  hours REAL NOT NULL,
  /**Интернет ссылка на программу*/
  program_link TEXT,
  /**Сертификационная или нет*/
  isCertification BIT,
  /**Вид обучения*/
  edu_type TEXT,
  /**Стоимость обучения*/
  edu_cost REAL,
  /**Обоснование стоимости*/
  cost_explanation TEXT,
  /**Основа обучения(бюджетная/договорная)*/
  edu_base TEXT,
  /**ОСК*/
  osk BIT,
  /**ОСК (ак часов)*/
  osk_hours REAL,
  /**Описание ОСК*/
  osk_description TEXT,
  /**Стажировка*/
  internship BIT,
  /**Стажировка (ак часов)*/
  internship_hours REAL,
  /**Описание стажировки*/
  internship_description TEXT,
  /**ДОТ и ЭО*/
  dot_and_eo BIT,
  /**Описание ДОТ и ЭО*/
  dot_and_eo_description TEXT,
   /**Id статуса*/
  status_id INT,
  /**Объекм заочной части, а.к часов*/
  extramural_hours REAL,
  /**Актуальность*/
  relevance TEXT,
  /**Получение новой(-ых) компетенции(-й)*/
  new_competence_reception BIT,
  /**Описание новой(-ых) компетенции(-й) */
  new_competence_description TEXT,
  /**Год разработки*/
  dev_year INT,
  /**Примечание*/
  note TEXT,
  
  CONSTRAINT pk_course PRIMARY KEY (uid)
  -- CONSTRAINT attestation_type_key FOREIGN KEY (attestation_type) REFERENCES nci.control_form_type(id)
);

/**Циклы по курсам (компонентам основной части программы КДПП)*/
CREATE TABLE course_cycle(
  uid VARCHAR(36) NOT NULL,
  /**ссылка на курс*/
	course_id VARCHAR(36) NOT NULL,
	start_date DATETIME,
	end_date DATETIME,
  /**id формы обучения*/
	education_form_id INT,
 /**id основы обучения*/
	education_base_id INT,
  /**id статуса*/
	status_id INT NOT NULL,
	/**Наличие ДОТ*/
	dot_included BIT,
	/**Описание ДОТ*/
	dot_description TEXT,
  /**Количество бюджетных мест*/
	budget_places INT,
  /**Количество платных мест*/
	paid_places INT,
  /**Выездной(да/нет)*/
	outside BIT,
  /** Регион*/
	region TEXT,
  /** Город*/
	city TEXT,
  /** Место проведения*/
	place TEXT,
  /** Клиническая база*/
	clinic_base TEXT,
  /** Телефон*/
	phone TEXT,
  /** Эл. почта организации*/
	org_email TEXT,
    /** тип аттестации (зачет, экзамен)*/
	attestation_type_id INT,
	/**ДОТ и ЭО*/
	dot_and_eo BIT,
	dot_and_eo_description TEXT,
	/**Наименование*/
	note TEXT,
	cycle_order int default 1,
  CONSTRAINT pk_course_cycle PRIMARY KEY (uid)
);


/**Таблица сопоставления программы и форм обучения*/
CREATE TABLE course_edu_form(
course_id VARCHAR(36) NOT NULL,
edu_form_id INT NOT NULL,
CONSTRAINT pk_course_edu_form PRIMARY KEY (course_id,edu_form_id)
);

/**Таблица сопоставления программы и основ обучения*/
CREATE TABLE course_edu_base(
course_id VARCHAR(36) NOT NULL,
edu_base_id INT NOT NULL,
CONSTRAINT pk_course_edu_base PRIMARY KEY (course_id,edu_base_id)
);

/**Таблица сопоставления программы и основ обучения*/
CREATE TABLE cycle_edu_base(
cycle_id VARCHAR(36) NOT NULL,
edu_base_id INT NOT NULL,
CONSTRAINT pk_cycle_edu_base PRIMARY KEY (cycle_id,edu_base_id)
);

CREATE TABLE course_contingent(
course_id VARCHAR(36) NOT NULL,
speciality_id VARCHAR(36) NOT NULL,
CONSTRAINT pk_course_contingent PRIMARY KEY (course_id,speciality_id)
);

/**Таблица сопоставления БТЗ циклам по курсам*/
CREATE TABLE course_cycle_qti_package(
course_cycle_id VARCHAR(36) NOT NULL,
qti_package_id VARCHAR(36) NOT NULL,
eor_type int NOT NULL DEFAULT 1,
control_type int default 1,
qti_order int default 1,
start_time_interval int default 0,
CONSTRAINT pk_course_cycle_qti_package PRIMARY KEY (course_cycle_id,qti_package_id)
);

/**Таблица открытых на просмотр курсов*/
CREATE TABLE course_view_open(
person_id varchar(30) not null,
course_id varchar(36) not null,
CONSTRAINT pk_course_view_open PRIMARY KEY (person_id,course_id)
);

create table educational_group (
  group_id INT NOT NULL IDENTITY,
  group_name VARCHAR(100) NOT NULL,
  create_date DATETIME NOT NULL,
  speciality VARCHAR(36) NOT NULL,
  control_date DATETIME NOT NULL,
  params TEXT,
  status INT DEFAULT 0,
  package_id VARCHAR(36), 
  kafedra_name VARCHAR(255),  
  stud_spec_name VARCHAR(255),  
  edu_level_name VARCHAR(100), 
  CONSTRAINT pk_education_group PRIMARY KEY (group_id)
);
CREATE INDEX idx_speciality ON educational_group(speciality);
CREATE INDEX idx_status ON educational_group(status);

create table edu_group_persons (
  group_person_id INT NOT NULL IDENTITY,
  group_id INT NOT NULL,
  person_id VARCHAR(30) NOT NULL,
  variant_id VARCHAR(36),
  CONSTRAINT pk_edu_group_persons PRIMARY KEY (group_person_id)
);
CREATE INDEX idx_group_id ON edu_group_persons(group_id);
CREATE INDEX idx_person_id ON edu_group_persons(person_id);


-- *** FOREIGN KEYS ***
ALTER TABLE course ADD CONSTRAINT fk_nci_course_nci_edu_012B6BC5 FOREIGN KEY (edu_organization_id) REFERENCES nci.edu_organization(uid);
ALTER TABLE course ADD CONSTRAINT fk_nci_course_nci_spec225E2625 FOREIGN KEY (speciality_id) REFERENCES umk.speciality(uid);
ALTER TABLE course_cycle ADD CONSTRAINT fk_nci_course_cycle_ncAA72705E FOREIGN KEY (course_id) REFERENCES edu.course(uid);
ALTER TABLE course_cycle ADD CONSTRAINT fk_nci_course_cycle_nc0531BC31 FOREIGN KEY (status_id) REFERENCES nci.status_list(id);
ALTER TABLE course_cycle ADD CONSTRAINT fk_course_cycle_attest FOREIGN KEY (attestation_type_id) REFERENCES nci.attestation_type(id);
ALTER TABLE course_cycle ADD CONSTRAINT fk_course_cycle_edu_form FOREIGN KEY (education_form_id) REFERENCES nci.education_form(id);


-- *** VIEWS ***
create view vw_edu_group_persons as
select
  gp.group_person_id,
  gp.group_id,
  gp.person_id,
  gp.variant_id,
  p.surname,
  p.name,
  p.patronymic,
  p.phone
from edu_group_persons gp
  inner join nci.person p on p.uid = gp.person_id;

create view vw_course as
  select co.uid as uid, co.name as name, co.speciality_id as speciality_id, sp.name as speciality_name
    , co.edu_organization_id as edu_organization_id, eo.shortname as edu_organization_name
    , co.hours as course_hours, co.program_link as program_link, co.edu_cost as edu_cost, co.cost_explanation as cost_explanation, co.edu_base as edu_base, co.osk as osk, co.osk_hours as osk_hours, co.internship as internship, co.internship_hours as internship_hours, co.internship_description as internship_description, co.dot_and_eo as dot_and_eo, co.dot_and_eo_description, co.extramural_hours as extramural_hours, co.relevance as relevance, co.new_competence_reception as new_competence_reception, co.new_competence_description as new_competence_description,co.note as note, co.str_unit as str_unit, co.osk_description as osk_description, co.edu_type as edu_type, co.dev_year as dev_year, co.isCertification as isCertification, 
    co.status_id as status_id, sl.name as status_name
    
  from course as co
    INNER join umk.speciality as sp on co.speciality_id = sp.uid
    INNER join nci.status_list as sl on co.status_id = sl.id
    INNER join nci.edu_organization as eo on co.edu_organization_id = eo.uid;
	-- LEFT join nci.education_form as ef on co.edu_form_id = ef.id
	-- LEFT join nci.control_form_type as cft on co.attestation_type = cft.id;

create view vw_course_cycle as
  select cc.uid as uid, cc.start_date as start_date, cc.end_date as end_date, co.uid as course_id
    , co.name as course_name, co.speciality_id as speciality_id, sp.name as speciality_name
    , co.edu_organization_id as edu_organization_id, eo.shortname as edu_organization_name
    , co.hours as course_hours, sl.id as status_id, sl.name as status_name,
    cc.budget_places as budget_places, cc.paid_places as paid_places, cc.region as region, 
    cc.city as city, cc.place as place, cc.dot_included as dot_included, cc.dot_description, 
    cc.clinic_base as clinic_base, cc.phone as phone, cc.org_email as org_email, cc.outside as outside, 
    cc.education_form_id as education_form_id, cc.education_base_id as education_base_id, 
    cc.dot_and_eo as dot_and_eo, cc.note as note, cc.dot_and_eo_description as dot_and_eo_description,
	ef.name as edu_form_name, cc.cycle_order
    
  from course_cycle as cc
    INNER join course as co on co.uid = cc.course_id
    INNER join umk.speciality as sp on co.speciality_id = sp.uid
    INNER join nci.edu_organization as eo on co.edu_organization_id = eo.uid
    INNER join nci.status_list as sl on cc.status_id = sl.id
	left join nci.education_form as ef on cc.education_form_id =  ef.id;
	
create view vw_program_cycle as
SELECT 
	course.uid AS uuid,
--	course.course_id AS course_id,
	course.name AS name, 
	course.edu_organization_id AS edu_organization_id, 
--	education_form_id AS education_form_id,
	course.speciality_id AS speciality_id,
--	start_date AS start_date,
--	end_date AS end_date, 
	course.hours AS hours,
--	course_cycle.status_id AS status_id,
	course.status_id AS status_id,
	sl.name as status_name,
	course.extramural_hours as extramural_hours,
	course.program_link as program_link,
	course.osk as osk,
	course.osk_hours as osk_hours, 
	course.internship as internship,
	course.internship_hours as internship_hours,
	course.edu_cost as price,
	course.str_unit as str_unit,
	course.dot_and_eo as dot_eo,
	spec.name as speciality_name,
--	ef.name as education_form_name,
	eo.name as edu_org_name,
	eo.shortname as edu_org_shortname,
	course.isCertification as is_certification
--FROM edu.course_cycle course_cycle 
FROM  edu.course course
--ON course_cycle.course_id = course.uid
--INNER JOIN nci.status_list as sl on course_cycle.status_id = sl.id
INNER JOIN nci.status_list as sl on course.status_id = sl.id
INNER JOIN umk.speciality as spec on course.speciality_id = spec.uid
--INNER JOIN nci.education_form as ef on ef.id = course_cycle.education_form_id
INNER JOIN nci.edu_organization as eo on eo.uid = course.edu_organization_id;

create view vw_course_cycle_qti_package as
SELECT
  ccqp.course_cycle_id as course_cycle_id,
  ccqp.qti_package_id as qti_package_id,
  qtip.PackageName as PackageName,
  ccqp.eor_type as eor_type,
  ccqp.control_type as control_type
FROM edu.course_cycle_qti_package ccqp
INNER JOIN qt.QtiPackage qtip ON ccqp.qti_package_id = qtip.PackageID;

create view vw_course_cycle_lectures as
SELECT
  ccqp.course_cycle_id,
  ccqp.qti_package_id,
  al.lecture_name,
  al.lecture_ref,
  ccqp.eor_type
FROM edu.course_cycle_qti_package ccqp
INNER JOIN author.eor_lectures al ON ccqp.qti_package_id = al.lecture_id;

create view vw_course_view_open as
select
  cv.person_id as person_id,
  cv.course_id as course_id, 
  co.name as name
from course_view_open cv
  INNER join course co on cv.course_id = co.uid;