-- This script creates the program_type_section table.
-- This table stores sections and their configurations for a specific program type.

CREATE TABLE IF NOT EXISTS program_type_section (
    id SERIAL PRIMARY KEY,
    program_type_id INT NOT NULL,
    section_key VARCHAR(255) NOT NULL,
    section_label VARCHAR(1024) NOT NULL,
    display_order INT DEFAULT 0,
    dependency_key VARCHAR(255),
    section_status hdb_common_status_enum NOT NULL DEFAULT 'active',
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMPTZ,
    created_by INT,
    updated_by INT,

    CONSTRAINT fk_pts_program_type
        FOREIGN KEY (program_type_id)
        REFERENCES program_type_v1(id) ON DELETE CASCADE,

    CONSTRAINT fk_pts_created_by
        FOREIGN KEY (created_by)
        REFERENCES users(id) ON DELETE SET NULL,

    CONSTRAINT fk_pts_updated_by
        FOREIGN KEY (updated_by)
        REFERENCES users(id) ON DELETE SET NULL,

    UNIQUE (program_type_id, section_key)
);

-- Add comments for columns for better understanding
COMMENT ON TABLE program_type_section IS 'Stores sections and their configurations for a specific program type.';
COMMENT ON COLUMN program_type_section.id IS 'Unique identifier for each program type section.';
COMMENT ON COLUMN program_type_section.program_type_id IS 'Foreign key to the program_type_v1 table.';
COMMENT ON COLUMN program_type_section.section_key IS 'A machine-readable key for the section, unique per program type.';
COMMENT ON COLUMN program_type_section.section_label IS 'The human-readable label for the section.';
COMMENT ON COLUMN program_type_section.display_order IS 'The order in which the section should be displayed.';
COMMENT ON COLUMN program_type_section.dependency_key IS 'Registration status key, foreign key to lookup_data(key).';
COMMENT ON COLUMN program_type_section.section_status IS 'The status of the section (e.g., draft, active, inactive).';
COMMENT ON COLUMN program_type_section.created_at IS 'Timestamp when the record was created.';
COMMENT ON COLUMN program_type_section.updated_at IS 'Timestamp when the record was last updated.';
COMMENT ON COLUMN program_type_section.deleted_at IS 'Timestamp for soft deletion.';
COMMENT ON COLUMN program_type_section.created_by IS 'Foreign key to the users table for the creator.';
COMMENT ON COLUMN program_type_section.updated_by IS 'Foreign key to the users table for the last updater.';

-- Create indexes for better query performance
CREATE INDEX IF NOT EXISTS idx_pts_program_type_id ON program_type_section(program_type_id, section_key);

-- This script creates the program_type_question table.
-- This table stores questions and their configurations for a specific program type.

CREATE TABLE IF NOT EXISTS program_type_question (
    id SERIAL PRIMARY KEY,
    program_type_id INT NOT NULL,
    question_key VARCHAR(255) NOT NULL,
    question_label VARCHAR(1024) NOT NULL,
    display_order INT DEFAULT 0,
    section_key VARCHAR(255),
    question_type VARCHAR(50) NOT NULL,
    question_config JSONB,
    question_status hdb_common_status_enum NOT NULL DEFAULT 'active',
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMPTZ,
    created_by INT,
    updated_by INT,

    CONSTRAINT fk_program_type
        FOREIGN KEY (program_type_id)
        REFERENCES program_type_v1(id) ON DELETE CASCADE,

    CONSTRAINT fk_created_by
        FOREIGN KEY (created_by)
        REFERENCES users(id) ON DELETE SET NULL,

    CONSTRAINT fk_updated_by
        FOREIGN KEY (updated_by)
        REFERENCES users(id) ON DELETE SET NULL,

    CONSTRAINT fk_section_key
        FOREIGN KEY (program_type_id, section_key)
        REFERENCES program_type_section(program_type_id, section_key) ON DELETE CASCADE,

    UNIQUE (program_type_id, question_key)
);

-- Add comments for columns for better understanding
COMMENT ON TABLE program_type_question IS 'Stores questions and their configurations for a specific program type.';
COMMENT ON COLUMN program_type_question.id IS 'Unique identifier for each program type question.';
COMMENT ON COLUMN program_type_question.program_type_id IS 'Foreign key to the program_type_v1 table.';
COMMENT ON COLUMN program_type_question.question_key IS 'A machine-readable key for the question, unique per program type.';
COMMENT ON COLUMN program_type_question.question_label IS 'The human-readable label for the question.';
COMMENT ON COLUMN program_type_question.display_order IS 'The order in which the question should be displayed.';
COMMENT ON COLUMN program_type_question.section_key IS 'A key to group questions into sections.';
COMMENT ON COLUMN program_type_question.question_type IS 'The type of the question (e.g., text, radio, checkbox).';
COMMENT ON COLUMN program_type_question.question_config IS 'JSONB field to store question-specific configurations (e.g., options for a radio button).';
COMMENT ON COLUMN program_type_question.question_status IS 'The status of the question (e.g., draft, active, inactive).';
COMMENT ON COLUMN program_type_question.created_at IS 'Timestamp when the record was created.';
COMMENT ON COLUMN program_type_question.updated_at IS 'Timestamp when the record was last updated.';
COMMENT ON COLUMN program_type_question.deleted_at IS 'Timestamp for soft deletion.';
COMMENT ON COLUMN program_type_question.created_by IS 'Foreign key to the users table for the creator.';
COMMENT ON COLUMN program_type_question.updated_by IS 'Foreign key to the users table for the last updater.';

-- Create indexes for better query performance
CREATE INDEX IF NOT EXISTS idx_ptq_program_type_id ON program_type_question(program_type_id, question_key);
CREATE INDEX IF NOT EXISTS idx_ptq_section_key ON program_type_question(program_type_id, section_key);

INSERT INTO program_type_section (id, program_type_id, section_key, section_label, display_order, section_status)
VALUES 
(1, 1, 'FS_BASICDETAILS', 'Basic Details', 1, 'active'),
(2, 1, 'FS_MAHATRIAQUESTIONS', 'Mahatria''s Questions', 2, 'active'),
(3, 1, 'FS_PAYMENTINVOICE', 'Payment & Invoice', 3, 'active'),
(4, 1, 'FS_TRAVELPLAN', 'Travel Plan & Goodies', 4, 'active');

INSERT INTO program_type_question (
  program_type_id,
  question_key,
  question_label,
  display_order,
  section_key,
  question_type,
  question_config,
  question_status
)
SELECT
  1                      AS program_type_id,
  q.binding_key          AS question_key,
  q.label                AS question_label,
  pq.display_order       AS display_order,
  fs.key                 AS section_key,
  q.typ                  AS question_type,
  q.config::jsonb        AS question_config,
  'active'::hdb_common_status_enum AS question_status
FROM hdb_program_question pq
JOIN hdb_question       q  ON pq.question_id      = q.id
JOIN hdb_form_section   fs ON pq.form_section_id  = fs.id
WHERE pq.program_id = 567
ORDER BY pq.form_section_id, pq.display_order;
