USE citetrack;

DELETE FROM stage_templates;

-- Helper: keep sort_order sequential per design
-- A) SYSTEMATIC_REVIEW_META_ANALYSIS
INSERT INTO stage_templates (study_design, code, name, description, sort_order, suggested_duration_days, follow_up_cadence_days, deliverables_json) VALUES
('SYSTEMATIC_REVIEW_META_ANALYSIS','QuestionEligibility','Question & Eligibility','Define PICO and inclusion/exclusion criteria.',1,7,7, JSON_ARRAY('PICO','Eligibility criteria')),
('SYSTEMATIC_REVIEW_META_ANALYSIS','ProtocolAnalysisPlan','Protocol & Analysis Plan','Draft protocol and prespecify analyses.',2,10,7, JSON_ARRAY('Protocol draft','Analysis plan')),
('SYSTEMATIC_REVIEW_META_ANALYSIS','SearchStrategy','Search Strategy','Finalize databases/queries and date limits.',3,7,7, JSON_ARRAY('Search strings','Databases list')),
('SYSTEMATIC_REVIEW_META_ANALYSIS','RunSearchDeduplicate','Run Search & Deduplicate','Run searches, export results, remove duplicates.',4,5,7, JSON_ARRAY('EndNote/Zotero library','Deduped set')),
('SYSTEMATIC_REVIEW_META_ANALYSIS','TitleAbstractScreening','Title/Abstract Screening','Screen titles/abstracts against criteria.',5,14,7, JSON_ARRAY('Screening decisions')),
('SYSTEMATIC_REVIEW_META_ANALYSIS','FullTextEligibility','Full-text Eligibility','Retrieve and assess full texts.',6,14,7, JSON_ARRAY('Full-text log')),
('SYSTEMATIC_REVIEW_META_ANALYSIS','DataExtraction','Data Extraction','Extract data with standardized forms.',7,14,7, JSON_ARRAY('Extraction sheet')),
('SYSTEMATIC_REVIEW_META_ANALYSIS','RiskOfBias','Risk of Bias','Assess ROB with appropriate tool.',8,10,7, JSON_ARRAY('ROB tables')),
('SYSTEMATIC_REVIEW_META_ANALYSIS','MetaAnalysis','Meta-analysis','Run meta-analysis where appropriate.',9,10,7, JSON_ARRAY('Forest plots','Model outputs')),
('SYSTEMATIC_REVIEW_META_ANALYSIS','Interpretation','Interpretation','Interpret findings, certainty, limitations.',10,7,7, JSON_ARRAY('GRADE summary','Key messages')),
('SYSTEMATIC_REVIEW_META_ANALYSIS','Writing_PRISMA','Writing (PRISMA)','Write manuscript following PRISMA.',11,14,7, JSON_ARRAY('Draft manuscript')),
('SYSTEMATIC_REVIEW_META_ANALYSIS','Submission_Revisions','Submission & Revisions','Submit and manage revisions/responses.',12,21,7, JSON_ARRAY('Cover letter','Responses to reviewers'));

-- B) CASE_CONTROL
INSERT INTO stage_templates (study_design, code, name, description, sort_order, suggested_duration_days, follow_up_cadence_days, deliverables_json) VALUES
('CASE_CONTROL','Protocol','Protocol','Study question, variables, and analysis plan.',1,10,7, JSON_ARRAY('Protocol')),
('CASE_CONTROL','DefineCases','Define Cases','Define case criteria and identification.',2,7,7, JSON_ARRAY('Case definition')),
('CASE_CONTROL','DefineControls','Define Controls','Define control selection and matching.',3,7,7, JSON_ARRAY('Control definition')),
('CASE_CONTROL','DefineExposures_Confounders','Exposures & Confounders','Define exposure measurement and confounders.',4,10,7, JSON_ARRAY('Variables list')),
('CASE_CONTROL','Ethics_DataAccess','Ethics & Data Access','IRB/data access approvals.',5,21,14, JSON_ARRAY('IRB approval','Data access')),
('CASE_CONTROL','DataCollection','Data Collection','Collect data from sources.',6,21,7, JSON_ARRAY('Data collection log')),
('CASE_CONTROL','Cleaning_Coding','Cleaning & Coding','Clean dataset, handle missingness.',7,10,7, JSON_ARRAY('Clean dataset')),
('CASE_CONTROL','Analysis','Analysis','Run primary/secondary analyses.',8,10,7, JSON_ARRAY('Stat outputs')),
('CASE_CONTROL','Writing_STROBE','Writing (STROBE)','Write manuscript following STROBE.',9,14,7, JSON_ARRAY('Draft manuscript')),
('CASE_CONTROL','Submission_Revisions','Submission & Revisions','Submit and manage revisions.',10,21,7, JSON_ARRAY('Cover letter','Responses'));

-- C) COHORT_PROSPECTIVE
INSERT INTO stage_templates (study_design, code, name, description, sort_order, suggested_duration_days, follow_up_cadence_days, deliverables_json) VALUES
('COHORT_PROSPECTIVE','Protocol','Protocol','Define objectives, outcomes, and plan.',1,10,7, JSON_ARRAY('Protocol')),
('COHORT_PROSPECTIVE','Ethics','Ethics','IRB and consent procedures.',2,21,14, JSON_ARRAY('IRB approval','Consent')),
('COHORT_PROSPECTIVE','CRF_Training','CRF & Training','Build CRF and train team.',3,14,7, JSON_ARRAY('CRF','Training record')),
('COHORT_PROSPECTIVE','RecruitmentStartup','Recruitment Startup','Set workflows, pilot recruitment.',4,14,7, JSON_ARRAY('Recruitment SOP')),
('COHORT_PROSPECTIVE','EnrollmentBaseline','Enrollment & Baseline','Enroll participants and collect baseline data.',5,60,14, JSON_ARRAY('Enrollment log')),
('COHORT_PROSPECTIVE','FollowUp','Follow-up','Ongoing follow-ups per protocol.',6,90,14, JSON_ARRAY('Follow-up log')),
('COHORT_PROSPECTIVE','DataQualityMonitoring','Data Quality Monitoring','Audit and monitor data quality.',7,30,14, JSON_ARRAY('DQ reports')),
('COHORT_PROSPECTIVE','DatabaseLock','Database Lock','Finalize and lock dataset.',8,7,7, JSON_ARRAY('Locked dataset')),
('COHORT_PROSPECTIVE','Analysis','Analysis','Run analyses.',9,14,7, JSON_ARRAY('Stat outputs')),
('COHORT_PROSPECTIVE','Writing_STROBE','Writing (STROBE)','Write manuscript following STROBE.',10,21,7, JSON_ARRAY('Draft manuscript')),
('COHORT_PROSPECTIVE','Submission_Revisions','Submission & Revisions','Submit and manage revisions.',11,21,7, JSON_ARRAY('Cover letter','Responses'));

-- D) COHORT_RETROSPECTIVE
INSERT INTO stage_templates (study_design, code, name, description, sort_order, suggested_duration_days, follow_up_cadence_days, deliverables_json) VALUES
('COHORT_RETROSPECTIVE','Protocol_CohortDefinition','Protocol & Cohort Definition','Define cohort, outcomes, timeframe.',1,10,7, JSON_ARRAY('Protocol','Cohort definition')),
('COHORT_RETROSPECTIVE','Ethics_DataAccess','Ethics & Data Access','IRB and dataset approvals.',2,21,14, JSON_ARRAY('IRB approval','Data access')),
('COHORT_RETROSPECTIVE','CodeList_VariableMapping','Code List & Variable Mapping','Map codes to variables and outcomes.',3,14,7, JSON_ARRAY('Code list','Data dictionary')),
('COHORT_RETROSPECTIVE','DataExtraction','Data Extraction','Extract data from sources.',4,14,7, JSON_ARRAY('Raw extract')),
('COHORT_RETROSPECTIVE','Cleaning_Missingness_CohortFlow','Cleaning/Missingness/Cohort Flow','Clean, missingness plan, cohort flow diagram.',5,14,7, JSON_ARRAY('Clean dataset','Flow diagram')),
('COHORT_RETROSPECTIVE','Analysis','Analysis','Run analyses.',6,14,7, JSON_ARRAY('Stat outputs')),
('COHORT_RETROSPECTIVE','Writing_STROBE','Writing (STROBE)','Write manuscript following STROBE.',7,21,7, JSON_ARRAY('Draft manuscript')),
('COHORT_RETROSPECTIVE','Submission_Revisions','Submission & Revisions','Submit and manage revisions.',8,21,7, JSON_ARRAY('Cover letter','Responses'));

-- E) CASE_REPORT
INSERT INTO stage_templates (study_design, code, name, description, sort_order, suggested_duration_days, follow_up_cadence_days, deliverables_json) VALUES
('CASE_REPORT','CaseSelection','Case Selection','Select case and define teaching point.',1,3,7, JSON_ARRAY('Case summary')),
('CASE_REPORT','Consent_DeID','Consent & De-identification','Consent and anonymize data/images.',2,7,7, JSON_ARRAY('Consent','De-ID checklist')),
('CASE_REPORT','BuildTimeline_CARE','Build Timeline (CARE)','Build clinical timeline per CARE.',3,5,7, JSON_ARRAY('Timeline')),
('CASE_REPORT','DataGathering','Data Gathering','Collect labs/imaging/clinical notes.',4,7,7, JSON_ARRAY('Key data bundle')),
('CASE_REPORT','LiteratureMiniReview','Literature Mini-review','Targeted literature review for context.',5,7,7, JSON_ARRAY('Key references')),
('CASE_REPORT','Writing_CARE','Writing (CARE)','Write per CARE guidelines.',6,10,7, JSON_ARRAY('Draft manuscript')),
('CASE_REPORT','Submission_Revisions','Submission & Revisions','Submit and manage revisions.',7,21,7, JSON_ARRAY('Cover letter','Responses'));
