-- MIGRATE DEGREE
============================================================================================================================================
INSERT INTO degree (id,name,description,created_by,created_date,updated_by,updated_date)
SELECT patternID AS id, patternName AS name,patternDesc AS description, 1 AS created_by,UTC_TIMESTAMP() AS created_date,1 AS updated_by,UTC_TIMESTAMP() AS updated_date from course_pattern;

update course_pattern cp 
inner join degree d on d.id = cp.patternID
set cp.degree_id = d.id 
where cp.patternID;


============================================================================================================================================
SELECT * FROM pattern_deptcourses;
SELECT * FROM stream;

select LEFT(MD5(RAND()), 17);
============================================================================================================================================


-- MIGRATE STREAM
============================================================================================================================================
ALTER TABLE `stream` DROP INDEX `name_UNIQUE_stream_idx`;
ALTER TABLE stream ADD UNIQUE `department_stream_unique`(name, departmentId);

INSERT INTO stream (id, name, properties, trashed, departmentId, created_by, created_date, updated_by, updated_date)
SELECT 
patterncourseID AS id,
patterncourseName AS name, 
JSON_OBJECT('code',patterncourseCode,'abbreviation',IFNULL(patternAbbreviation,""),'isActive',isHide) AS properties,
NULL AS trashed, 
deptID AS departmentId, 
1 AS created_by, 
UTC_TIMESTAMP() AS created_date, 
1 AS updated_by, 
UTC_TIMESTAMP() AS updated_date
FROM pattern_deptcourses
WHERE deptID in (select deptID from department) AND patterncourseName <> ""
GROUP BY patterncourseName,deptID;



-- MIGRATE PROGRAM
============================================================================================================================================
INSERT IGNORE INTO program (id, name, course_type_id, degree_id, stream_id, specialization, properties, trashed, created_by, created_date, updated_by, updated_date)
SELECT 
	LEFT(MD5(RAND()), 17) AS id,
    IF(pdc.patterncourseName LIKE CONCAT('%',cp.patternName,'%'),pdc.patterncourseName,concat(cp.patternName,' - ',pdc.patterncourseName)) AS name,
	b.courseTypeID AS course_type_id,
    pdc.patternID AS degree_id,
    JSON_ARRAY(cast(pdc.patterncourseID AS CHAR)) AS stream_id,
    JSON_OBJECT() AS specialization,
    JSON_OBJECT() AS properties,
    NULL AS trashed,
    1 AS created_by,
    UTC_TIMESTAMP() AS created_date,
    1 AS updated_by, 
    UTC_TIMESTAMP() AS updated_date
FROM batches b
INNER JOIN course_type ct ON ct.courseTypeID = b.courseTypeID
INNER JOIN course_pattern cp ON cp.patternID = b.patternID
LEFT JOIN pattern_deptcourses pdc ON pdc.patternID = b.patternID AND b.deptID = pdc.deptID
GROUP BY b.courseTypeID,b.patternID,b.patterncourseID;


ALTER TABLE `stthomas_07`.`program` 
CHANGE COLUMN `stream_id` `stream_id` VARCHAR(600) NULL DEFAULT NULL ;

ALTER TABLE `stthomas_07_test1`.`program` 
ADD UNIQUE INDEX `index6` (`stream_id` ASC, `degree_id` ASC, `course_type_id` ASC, `name` ASC),
DROP INDEX `index5` ;

update pattern_deptcourses pds
left join stream s on pds.patterncourseID = s.id
set pds.stream_ids = json_array(s.id)
where s.id is not null;

-- Checking all course_pattern migrated correctly
select * from course_pattern cp 
left join degree d on d.id = cp.degree_id
where d.id is null;

-- Create Batches
-- insert ignore into groups (id,name,type,properties,trashed,created_by,created_date,updated_by,updated_date)
select 
b.batchID as id,b.batchName as name,'BATCH' as type,
JSON_OBJECT(
	'startYear', if(b.batchStartYear,cast(b.batchStartYear AS CHAR),null),
    'endYear',if(b.batchEndYear,cast(b.batchEndYear AS CHAR),null),
    'description',b.batchDesc,
    'optionName',b.batchOptionName,
    'studentShowingFields',JSON_ARRAY(JSON_object('name',b.studentSortBy,'order',1,'displayName',b.studentShowField)),
    'universityId',b.universityId,
    'totalTerms',b.totalSemester,
    'termType','SEMESTER',
    'currentTermId',at.id,
    'finalTermId',cast(b.final_semester as CHAR),
    'description',b.batchDesc,
    'isPassout',false,
    'tcIssueDate',b.tcIssueDate,
    'yearOfPassing',if(b.batchEndYear,cast(b.batchEndYear AS CHAR),null),
    'campusTypeId',b.campus_typeID,
    'universityId',b.universityId,
    'studentSortBy',b.studentSortBy,
    'departmentId',b.deptID,
    'programId',p.id,
    'curriculumId','',
    'extraSyllabusId',json_array(),
    'createdNow',true,
    'isActive',b.batchHide,
    'batchDisplayOrder',b.batchDisplayOrder
) as properties,1 as trashed,1 as created_by,utc_timestamp() as created_date,1 as updated_by,utc_timestamp() as updated_date
from batches b
inner join semesters sem on sem.semID = b.semID
inner join academic_term at on sem.termId = at.id
inner join course_type ct on ct.courseTypeID = b.campus_typeID
inner join course_pattern cp on cp.patternID = b.patternID
inner join pattern_deptcourses pdc on pdc.patternID = b.patternID and pdc.deptID = b.deptID
INNER join degree d on d.id = cp.degree_id
INNER join stream s on ( (pdc.patterncourseName = s.name and pdc.deptID = s.departmentId ) )
INNER join program p on p.course_type_id = ct.courseTypeID and p.degree_id = d.id and JSON_CONTAINS(p.stream_id,concat('"',s.id,'"'),'$')
-- set g.properties = JSON_SET(g.properties, "$.departmentId", d.deptID)
group by b.batchID;

delete from groups 
-- update batches set 
-- groups_id = null
where id not in (select groups_id from group_members) and id not in (select groups_id from batch_paper_subject);

update batches set groups_id = batchID where batchID in (select id from groups);


-- FOR CREATING CURRICULUM
-- ===============================================
-- insert into cm_curriculum (id, name, description, type, properties, trashed, created_by, created_date, updated_by, updated_date)
select 
	LEFT(MD5(RAND()), 17) AS id,
    concat(p.name,' (',min(b.batchStartYear),'-',max(b.batchEndYear),')-',group_concat(distinct d.deptName),'-',group_concat(distinct ct.typeName)) as name,
    concat(p.name,' (',min(b.batchStartYear),'-',max(b.batchEndYear),')') as description,
    'SINGLE_MAJOR' as type,
    json_object('programId',p.id,'startYear',cast(min(b.batchStartYear) as CHAR),'endYear',cast(max(b.batchEndYear) as CHAR)) as properties,
    null as trashed,
    1 as createdBy,
    1 as updated_by,
    utc_timestamp() as created_date,
    utc_timestamp() as updated_date
from batches b 
inner join groups g on g.type = 'BATCH' and g.id = b.groups_id
inner join sbs_relation sbs on sbs.batchID = b.batchID
inner join program p on p.id = g.properties->'$.programId'
inner join course_type ct on ct.courseTypeID = p.course_type_id
inner join stream s on JSON_CONTAINS(p.stream_id,concat('"',s.id,'"'),'$')
inner join department d on d.deptID = s.departmentId
group by g.properties->'$.programId';
select * from program;
select * from cm_syllabus;

-- FOR CREATING SYLLABUS 
-- ==========================================================
-- insert into cm_syllabus (id, name, description, type, properties, trashed, created_by, created_date, updated_by, updated_date)
select 
	LEFT(MD5(RAND()), 17) AS id,
    concat(p.name,' (',min(b.batchStartYear),'-',max(b.batchEndYear),')-',d.deptName,'-',group_concat(distinct ct.typeName)) as name,
    concat(p.name,' (',min(b.batchStartYear),'-',max(b.batchEndYear),')') as description,
    'MAJOR' as type,
    json_object('departmentId',d.deptID,'curriculumId',cm.id) as properties,
    null as trashed,
    1 as createdBy,
    1 as updated_by,
    utc_timestamp() as created_date,
    utc_timestamp() as updated_date
from batches b 
inner join groups g on g.type = 'BATCH' and g.id = b.groups_id
inner join sbs_relation sbs on sbs.batchID = b.batchID
inner join program p on p.id = g.properties->'$.programId'
inner join course_type ct on ct.courseTypeID = p.course_type_id
inner join stream s on JSON_CONTAINS(p.stream_id,concat('"',s.id,'"'),'$')
inner join department d on d.deptID = s.departmentId
inner join cm_curriculum cm on cm.properties->'$.programId' = p.id
group by g.properties->'$.programId',d.deptID;

-- FOR CREATING CURRICULUM SYLLABUS RELATIONS 
-- ==========================================================
-- insert into cm_curriculum_syllabus_relation (cm_curriculum_id, cm_syllabus_id, properties, trashed, createdBy, updated_by, created_date, updated_date)
select 
	cm.id as cm_curriculum_id, 
    sy.id as cm_syllabus_id, 
    json_object('departmentId',cast(sy.properties->'$.departmentId' as CHAR)) as properties,
    null as trashed,
    1 as createdBy,
    1 as updated_by,
    utc_timestamp() as created_date,
    utc_timestamp() as updated_date
from cm_curriculum cm 
inner join cm_syllabus sy on sy.properties->'$.curriculumId' = cm.id
group by cm.id;


-- FOR CREATING ACADEMICS TERM SETTINGS (SYLLABUS TERM RELATIONS)
-- ==========================================================
-- insert into cm_syllabus_academic_term_settings (id, cm_syllabus_id, academic_term_id, properties, trashed, created_by, updated_by, created_date, updated_date)
select 
	UPPER(LEFT(MD5(RAND()), 17)) AS id,
    sy.id as cm_syllabus_id,
    at.id as academic_term_id,
    json_object() as properties,
    null as trashed,
    1 as created_by,
    1 as updated_by,
    utc_timestamp() as created_date,
    utc_timestamp() as updated_date
from batches b 
inner join groups g on g.id = b.groups_id
inner join sbs_relation sbs on sbs.batchID = b.batchID
inner join semesters sem on sem.semID = sbs.semID
inner join academic_term at on sem.termId = at.id
inner join program p on p.id = g.properties->'$.programId'
inner join cm_curriculum cm on cm.properties->'$.programId' = p.id
inner join cm_curriculum_syllabus_relation csr on csr.cm_curriculum_id = cm.id
inner join cm_syllabus sy on sy.id = csr.cm_syllabus_id 
where g.type = 'BATCH'
group by sy.id,sbs.semID;

-- FOR CREATING ACADEMICS PAPER
-- ==========================================================
-- insert into cm_academic_paper (id, name, cm_syllabus_academic_term_settings_id, properties, trashed, created_by, updated_by, created_date, updated_date)
select 
	UPPER(LEFT(MD5(RAND()), 17)) AS id,
    sub.subjectDesc as name, 
    ats.id as cm_syllabus_academic_term_settings_id,
    json_object('subjectTypeId','','noOfSubjectThatAStudentCanChoose',1,'subjectId',sbs.subjectID) as properties, 
    null as trashed,
    1 as created_by,
    1 as updated_by,
    utc_timestamp() as created_date,
    utc_timestamp() as updated_date
from batches b 
inner join groups g on g.id = b.groups_id
inner join sbs_relation sbs on sbs.batchID = b.batchID
inner join subjects sub on sub.subjectID = sbs.subjectID
inner join semesters sem on sem.semID = sbs.semID
inner join academic_term at on sem.termId = at.id
inner join cm_curriculum cm on cm.properties->'$.programId' = g.properties->'$.programId'
inner join cm_curriculum_syllabus_relation csr on csr.cm_curriculum_id = cm.id
inner join cm_syllabus sy on sy.id = csr.cm_syllabus_id 
inner join cm_syllabus_academic_term_settings ats on ats.cm_syllabus_id = csr.cm_syllabus_id and ats.academic_term_id = at.id
where g.type = 'BATCH'
group by ats.id,sbs.subjectID;

-- FOR CREATING ACADEMICS PAPER SUBJECTS
-- ==========================================================
-- insert into cm_academic_paper_subjects (id, cm_academic_paper_id, subject_id, properties, trashed, created_by, updated_by, created_date, updated_date)
select 
	UPPER(LEFT(MD5(RAND()), 17)) AS id,
    ap.id as cm_academic_paper_id, 
	ap.properties->'$.subjectId' as subject_id, 
    json_object('credit','','isActive',true,'isExternal','','isInternal','','gradeSchemeId','', 'subjectTypeId','', 'studentCanChoose',1, 'excludeSubjectFromTotal','', 'noOfSubjectThatAStudentCanChoose',1) as properties, 
    null as trashed,
    1 as created_by,
    1 as updated_by,
    utc_timestamp() as created_date,
    utc_timestamp() as updated_date
from batches b 
inner join groups g on g.id = b.groups_id
inner join sbs_relation sbs on sbs.batchID = b.batchID
inner join subjects sub on sub.subjectID = sbs.subjectID
inner join semesters sem on sem.semID = sbs.semID
inner join academic_term at on sem.termId = at.id
inner join cm_curriculum cm on cm.properties->'$.programId' = g.properties->'$.programId'
inner join cm_curriculum_syllabus_relation csr on csr.cm_curriculum_id = cm.id
inner join cm_syllabus sy on sy.id = csr.cm_syllabus_id 
inner join cm_syllabus_academic_term_settings ats on ats.cm_syllabus_id = csr.cm_syllabus_id and ats.academic_term_id = at.id
inner join cm_academic_paper ap on ap.cm_syllabus_academic_term_settings_id = ats.id and ap.properties->'$.subjectId' = sbs.subjectID
where g.type = 'BATCH'
group by ats.id,sbs.subjectID;

-- SET CURRICULUM TO BATCH
-- =====================================================================
update groups g 
inner join batches b on g.id = b.groups_id
inner join sbs_relation sbs on sbs.batchID = b.batchID
inner join subjects sub on sub.subjectID = sbs.subjectID
inner join semesters sem on sem.semID = sbs.semID
inner join academic_term at on sem.termId = at.id
inner join cm_curriculum cm on cm.properties->'$.programId' = g.properties->'$.programId'
set g.properties = JSON_SET(g.properties, '$.curriculumId', cm.id);