-- =====================================================
-- Master-Template-Program Question & Section System
-- Schema Migration Script
-- Version: 4.0.0
-- Date: 2026-03-13
-- Order: 00 - Run this first to create schema
-- Note: Default values are handled in TypeScript entities
-- Note: No ON DELETE CASCADE/SET NULL - handle in application
-- =====================================================

-- =====================================================
-- STEP 1: CREATE ENUMS
-- =====================================================

-- Create question_type enum (UI control type)
DO $$ BEGIN
    CREATE TYPE question_type_enum AS ENUM (
        'text',
        'number',
        'radio',
        'checkbox',
        'multiselect',
        'email',
        'tel',
        'textarea',
        'select',
        'date',
        'dateandtime',
        'time',
        'year',
        'yearRange',
        'file',
        'draganddrop',
        'apicall',
        'Address',
        'address',
        'boolean',
        'button',
        'multiQuestion'
    );
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- Create answer_type enum (submitted value data type)
DO $$ BEGIN
    CREATE TYPE answer_type AS ENUM (
        'string',   -- text, email, tel, textarea, radio, select, year, yearRange, file, checkbox, multiselect, Address, draganddrop, apicall (all stored as strings/JSON)
        'number',   -- number
        'boolean',  -- boolean
        'date'      -- date, dateandtime
    );
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- Create template_status enum
DO $$ BEGIN
    CREATE TYPE template_status AS ENUM (
        'DRAFT',
        'PUBLISHED',
        'ARCHIVED'
    );
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- =====================================================
-- STEP 2: CREATE MASTER_FORM_SECTION TABLE
-- =====================================================

CREATE TABLE IF NOT EXISTS master_form_section (
    id BIGSERIAL PRIMARY KEY,
    section_key VARCHAR(100) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    parent_section_id BIGINT NULL,
    conditional_config JSONB NULL,
    is_active BOOLEAN NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE,
    deleted_at TIMESTAMP WITH TIME ZONE NULL,
    created_by INTEGER NULL,
    updated_by INTEGER NULL,
    CONSTRAINT fk_master_section_parent FOREIGN KEY (parent_section_id) 
        REFERENCES master_form_section(id)
);

-- Create indexes for master_form_section
CREATE UNIQUE INDEX IF NOT EXISTS idx_master_section_key 
    ON master_form_section(section_key) 
    WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_master_section_parent 
    ON master_form_section(parent_section_id);

CREATE INDEX IF NOT EXISTS idx_master_section_active 
    ON master_form_section(is_active) 
    WHERE is_active = true;

-- =====================================================
-- STEP 3: CREATE MASTER_QUESTION TABLE
-- =====================================================

-- Create dedicated sequence for question codes
CREATE SEQUENCE IF NOT EXISTS master_question_code_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

CREATE TABLE IF NOT EXISTS master_question (
    id BIGSERIAL PRIMARY KEY,
    question_code VARCHAR(100) NOT NULL,
    binding_key VARCHAR(100) NOT NULL,
    master_form_section_id BIGINT NOT NULL,
    question_text TEXT NOT NULL,
    question_type question_type_enum NOT NULL,
    answer_type answer_type NOT NULL,
    answer_location VARCHAR(1024) NULL,
    option_config JSONB NULL,
    config JSONB NOT NULL,
    is_active BOOLEAN NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE,
    deleted_at TIMESTAMP WITH TIME ZONE NULL,
    created_by INTEGER NULL,
    updated_by INTEGER NULL,
    CONSTRAINT fk_master_question_section FOREIGN KEY (master_form_section_id) 
        REFERENCES master_form_section(id)
);

-- Create indexes for master_question
CREATE UNIQUE INDEX IF NOT EXISTS idx_master_question_code 
    ON master_question(question_code) 
    WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_master_question_section 
    ON master_question(master_form_section_id);

CREATE INDEX IF NOT EXISTS idx_master_question_binding 
    ON master_question(binding_key);

CREATE INDEX IF NOT EXISTS idx_master_question_active 
    ON master_question(is_active) 
    WHERE is_active = true;

-- =====================================================
-- STEP 4: CREATE PROGRAM_TEMPLATE TABLE
-- =====================================================

CREATE TABLE IF NOT EXISTS program_template (
    id BIGSERIAL PRIMARY KEY,
    program_type_id INTEGER NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    version INTEGER NOT NULL,
    status template_status NOT NULL,
    is_active BOOLEAN NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE,
    deleted_at TIMESTAMP WITH TIME ZONE NULL,
    created_by INTEGER NULL,
    updated_by INTEGER NULL,
    CONSTRAINT fk_program_template_type FOREIGN KEY (program_type_id) 
        REFERENCES program_type_v1(id)
);

-- Create indexes for program_template
CREATE INDEX IF NOT EXISTS idx_program_template_type 
    ON program_template(program_type_id);

CREATE INDEX IF NOT EXISTS idx_program_template_status 
    ON program_template(status);

CREATE INDEX IF NOT EXISTS idx_program_template_active 
    ON program_template(is_active) 
    WHERE is_active = true;

CREATE INDEX IF NOT EXISTS idx_program_template_version 
    ON program_template(program_type_id, version);

-- =====================================================
-- STEP 5: CREATE TEMPLATE_FORM_SECTION TABLE
-- =====================================================

CREATE TABLE IF NOT EXISTS template_form_section (
    id BIGSERIAL PRIMARY KEY,
    program_template_id BIGINT NOT NULL,
    master_form_section_id BIGINT NULL,
    section_key VARCHAR(100) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    parent_section_id BIGINT NULL,
    conditional_config JSONB NULL,
    display_order INTEGER NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE,
    deleted_at TIMESTAMP WITH TIME ZONE NULL,
    created_by INTEGER NULL,
    updated_by INTEGER NULL,
    CONSTRAINT fk_template_section_program_template FOREIGN KEY (program_template_id) 
        REFERENCES program_template(id),
    CONSTRAINT fk_template_section_master FOREIGN KEY (master_form_section_id) 
        REFERENCES master_form_section(id),
    CONSTRAINT fk_template_section_parent FOREIGN KEY (parent_section_id) 
        REFERENCES template_form_section(id)
);

-- Create indexes for template_form_section
CREATE INDEX IF NOT EXISTS idx_template_section_template 
    ON template_form_section(program_template_id);

CREATE INDEX IF NOT EXISTS idx_template_section_master 
    ON template_form_section(master_form_section_id);

CREATE INDEX IF NOT EXISTS idx_template_section_parent 
    ON template_form_section(parent_section_id);

CREATE INDEX IF NOT EXISTS idx_template_section_display_order 
    ON template_form_section(program_template_id, display_order);

-- =====================================================
-- STEP 6: CREATE TEMPLATE_QUESTION TABLE
-- =====================================================

CREATE TABLE IF NOT EXISTS template_question (
    id BIGSERIAL PRIMARY KEY,
    template_form_section_id BIGINT NOT NULL,
    master_question_id BIGINT NULL,
    question_code VARCHAR(100) NOT NULL,
    binding_key VARCHAR(100) NOT NULL,
    question_text TEXT NOT NULL,
    question_type question_type_enum NOT NULL,
    answer_type answer_type NOT NULL,
    answer_location VARCHAR(1024) NULL,
    option_config JSONB NULL,
    config JSONB NOT NULL,
    display_order INTEGER NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE,
    deleted_at TIMESTAMP WITH TIME ZONE NULL,
    created_by INTEGER NULL,
    updated_by INTEGER NULL,
    CONSTRAINT fk_template_question_section FOREIGN KEY (template_form_section_id) 
        REFERENCES template_form_section(id),
    CONSTRAINT fk_template_question_master FOREIGN KEY (master_question_id) 
        REFERENCES master_question(id)
);

-- Create indexes for template_question
CREATE INDEX IF NOT EXISTS idx_template_question_section 
    ON template_question(template_form_section_id);

CREATE INDEX IF NOT EXISTS idx_template_question_master 
    ON template_question(master_question_id);

CREATE INDEX IF NOT EXISTS idx_template_question_code 
    ON template_question(question_code);

CREATE INDEX IF NOT EXISTS idx_template_question_binding 
    ON template_question(binding_key);

CREATE INDEX IF NOT EXISTS idx_template_question_display_order 
    ON template_question(template_form_section_id, display_order);

-- =====================================================
-- STEP 7: EXTEND HDB_FORM_SECTION TABLE
-- =====================================================

-- Add template_form_section_id column and foreign key
ALTER TABLE hdb_form_section 
ADD COLUMN IF NOT EXISTS template_form_section_id BIGINT NULL;

DO $$ BEGIN
    ALTER TABLE hdb_form_section 
    ADD CONSTRAINT fk_hdb_section_template 
    FOREIGN KEY (template_form_section_id) 
    REFERENCES template_form_section(id);
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

CREATE INDEX IF NOT EXISTS idx_hdb_section_template 
ON hdb_form_section(template_form_section_id);

-- Add parent_section_id column for nested sections
ALTER TABLE hdb_form_section 
ADD COLUMN IF NOT EXISTS parent_section_id INTEGER NULL;

DO $$ BEGIN
    ALTER TABLE hdb_form_section 
    ADD CONSTRAINT fk_hdb_section_parent 
    FOREIGN KEY (parent_section_id) 
    REFERENCES hdb_form_section(id);
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

CREATE INDEX IF NOT EXISTS idx_hdb_section_parent 
ON hdb_form_section(parent_section_id);

-- Add conditional_config column
ALTER TABLE hdb_form_section 
ADD COLUMN IF NOT EXISTS conditional_config JSONB NULL;

-- =====================================================
-- STEP 8: EXTEND HDB_QUESTION TABLE
-- =====================================================

-- Note: hdb_question already has config and binding_key columns
-- We only need to add new columns for master-template linkage

-- Add template_question_id column and foreign key
ALTER TABLE hdb_question 
ADD COLUMN IF NOT EXISTS template_question_id BIGINT NULL;

DO $$ BEGIN
    ALTER TABLE hdb_question 
    ADD CONSTRAINT fk_hdb_question_template 
    FOREIGN KEY (template_question_id) 
    REFERENCES template_question(id);
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

CREATE INDEX IF NOT EXISTS idx_hdb_question_template 
ON hdb_question(template_question_id);

-- Add question_code column
ALTER TABLE hdb_question 
ADD COLUMN IF NOT EXISTS question_code VARCHAR(100) NULL;

CREATE INDEX IF NOT EXISTS idx_hdb_question_code 
ON hdb_question(question_code);

-- Add option_config column
ALTER TABLE hdb_question 
ADD COLUMN IF NOT EXISTS option_config JSONB NULL;

-- add answer_type column
ALTER TABLE hdb_question 
ADD COLUMN IF NOT EXISTS answer_type answer_type NULL;

-- =====================================================
-- STEP 9: EXTEND PROGRAM_V1 TABLE
-- =====================================================

-- Add program_template_id column to link program to template
ALTER TABLE program_v1 
ADD COLUMN IF NOT EXISTS program_template_id BIGINT NULL;

DO $$ BEGIN
    ALTER TABLE program_v1 
    ADD CONSTRAINT fk_program_template 
    FOREIGN KEY (program_template_id) 
    REFERENCES program_template(id);
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

CREATE INDEX IF NOT EXISTS idx_program_template 
ON program_v1(program_template_id);

-- =====================================================
-- MIGRATION COMPLETE
-- =====================================================

COMMENT ON TABLE master_form_section IS 'Master template for form sections - single source of truth';
COMMENT ON TABLE master_question IS 'Master template for questions - single source of truth';
COMMENT ON TABLE program_template IS 'Program type template metadata - defines template versions for program types (HDB, MSD, etc.)';
COMMENT ON TABLE template_form_section IS 'Program template sections - cloned from master';
COMMENT ON TABLE template_question IS 'Program template questions - cloned from master';

COMMENT ON COLUMN program_v1.program_template_id IS 'Links program instance to its template configuration';
