-- ENUM for hdb_option.type
CREATE TYPE hdb_option_type_enum AS ENUM (
  'string',
  'number',
  'boolean',
  'date',
  'timestamp',
  'image',
  'file',
  'object',
  'enum',
  'color'
);

-- Common ENUM for status
CREATE TYPE hdb_common_status_enum AS ENUM (
  'draft',
  'active',
  'inactive',
  'published',
  'archived'
);

-- hdb_form_section Table
CREATE TABLE hdb_form_section (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,

  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  deleted_at TIMESTAMPTZ DEFAULT NULL,

  created_by INTEGER,
  updated_by INTEGER,

  CONSTRAINT fk_created_by FOREIGN KEY (created_by)
      REFERENCES public.users (id) ON DELETE SET NULL ON UPDATE CASCADE,

  CONSTRAINT fk_updated_by FOREIGN KEY (updated_by)
      REFERENCES public.users (id) ON DELETE SET NULL ON UPDATE CASCADE
);


INSERT INTO hdb_form_section (name, description, created_by, updated_by)
VALUES
  ('Basic Details', 'Section for basic user details', 8319, 8319),
  ('Invoice', 'Section for invoice information', 8319, 8319),
  ('Travel', 'Section for travel-related details', 8319, 8319);

-- hdb_question Table
CREATE TABLE hdb_question (
    id SERIAL PRIMARY KEY,
    label VARCHAR(1024) NOT NULL,
    config JSONB,
    stats hdb_common_status_enum NOT NULL DEFAULT 'draft', -- Use common status ENUM
    typ VARCHAR(50) NOT NULL,

    form_section_id INTEGER, -- Nullable, links to hdb_form_section

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMPTZ,

    created_by INTEGER,
    updated_by INTEGER,

    CONSTRAINT fk_form_section FOREIGN KEY (form_section_id)
        REFERENCES public.hdb_form_section (id) ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT fk_created_by FOREIGN KEY (created_by)
        REFERENCES public.users (id) ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT fk_updated_by FOREIGN KEY (updated_by)
        REFERENCES public.users (id) ON DELETE SET NULL ON UPDATE CASCADE
);

-- hdb_option_category Table
CREATE TABLE hdb_option_category (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,

  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  deleted_at TIMESTAMPTZ DEFAULT NULL, -- Soft delete column

  created_by INTEGER,
  updated_by INTEGER,

  CONSTRAINT fk_created_by FOREIGN KEY (created_by)
      REFERENCES public.users (id) ON DELETE SET NULL ON UPDATE CASCADE,

  CONSTRAINT fk_updated_by FOREIGN KEY (updated_by)
      REFERENCES public.users (id) ON DELETE SET NULL ON UPDATE CASCADE
);

-- hdb_option Table
CREATE TABLE hdb_option (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  typ hdb_option_type_enum NOT NULL,
  category_id INTEGER,

  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  deleted_at TIMESTAMPTZ, -- Soft delete column

  stats hdb_common_status_enum DEFAULT 'draft', -- Use common status ENUM

  created_by INTEGER,
  updated_by INTEGER,

  CONSTRAINT fk_category FOREIGN KEY (category_id)
      REFERENCES public.hdb_option_category (id) ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT fk_created_by FOREIGN KEY (created_by)
      REFERENCES public.users (id) ON DELETE SET NULL ON UPDATE CASCADE,

  CONSTRAINT fk_updated_by FOREIGN KEY (updated_by)
      REFERENCES public.users (id) ON DELETE SET NULL ON UPDATE CASCADE
);

-- hdb_question_option_map Table
CREATE TABLE hdb_question_option_map (
    id SERIAL PRIMARY KEY,
    
    question_id INTEGER NOT NULL,
    option_id INTEGER NOT NULL,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMPTZ,

    created_by INTEGER,
    updated_by INTEGER,

    CONSTRAINT fk_question FOREIGN KEY (question_id)
        REFERENCES public.hdb_question (id) ON DELETE CASCADE ON UPDATE CASCADE,

    CONSTRAINT fk_option FOREIGN KEY (option_id)
        REFERENCES public.hdb_option (id) ON DELETE CASCADE ON UPDATE CASCADE,

    CONSTRAINT fk_created_by FOREIGN KEY (created_by)
        REFERENCES public.users (id) ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT fk_updated_by FOREIGN KEY (updated_by)
        REFERENCES public.users (id) ON DELETE SET NULL ON UPDATE CASCADE
);


-- hdb_program_question Table
CREATE TABLE hdb_program_question (
    id SERIAL PRIMARY KEY,
    
    program_id INTEGER NOT NULL,
    question_id INTEGER NOT NULL,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMPTZ,

    created_by INTEGER,
    updated_by INTEGER,

    CONSTRAINT fk_program FOREIGN KEY (program_id)
        REFERENCES public.program_v1 (id) ON DELETE CASCADE ON UPDATE CASCADE,

    CONSTRAINT fk_question FOREIGN KEY (question_id)
        REFERENCES public.hdb_question (id) ON DELETE CASCADE ON UPDATE CASCADE,

    CONSTRAINT fk_created_by FOREIGN KEY (created_by)
        REFERENCES public.users (id) ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT fk_updated_by FOREIGN KEY (updated_by)
        REFERENCES public.users (id) ON DELETE SET NULL ON UPDATE CASCADE
);

-- drop table hdb_question,hdb_option_category,hdb_option,hdb_question_option_map,hdb_program_question

-- Alter commands for program_v1 table
ALTER TABLE program_v1
ADD COLUMN program_fee DECIMAL(10, 2),
ADD COLUMN is_approval_required BOOLEAN DEFAULT FALSE,
ADD COLUMN is_residence_required BOOLEAN DEFAULT FALSE,
ADD COLUMN is_travel_involved BOOLEAN DEFAULT FALSE,
ADD COLUMN cgst DECIMAL(5,2) DEFAULT 0,
ADD COLUMN sgst DECIMAL(5,2) DEFAULT 0,
ADD COLUMN igst DECIMAL(5,2) DEFAULT 0,
ADD COLUMN max_capacity INT,
ADD COLUMN created_by INT,
ADD COLUMN updated_by INT,
ADD COLUMN deleted_at TIMESTAMP,
ADD COLUMN tds_percent DECIMAL(5,2) DEFAULT 0, -- Added
ADD COLUMN gst_number VARCHAR(50),             -- Added
ADD COLUMN checkin_ends_at TIMESTAMPTZ,
ADD COLUMN checkout_ends_at TIMESTAMPTZ;
ADD COLUMN logo_url VARCHAR(1024); -- Added logo URL column

ALTER TABLE program_session
ADD COLUMN created_by INT,
ADD COLUMN updated_by INT,
ADD COLUMN deleted_at TIMESTAMP,
ADD COLUMN cgst DECIMAL(5,2) DEFAULT 0,
ADD COLUMN sgst DECIMAL(5,2) DEFAULT 0,
ADD COLUMN igst DECIMAL(5,2) DEFAULT 0,
ADD COLUMN tds_percent DECIMAL(5,2) DEFAULT 0, -- Added
ADD COLUMN gst_number VARCHAR(50),             -- Added
ADD COLUMN checkin_ends_at TIMESTAMPTZ,
ADD COLUMN checkout_ends_at TIMESTAMPTZ;
ADD COLUMN logo_url VARCHAR(1024);

ALTER TABLE program_type_v1
ADD COLUMN deleted_at TIMESTAMP;


-- Create ENUM types
CREATE TYPE gender_enum AS ENUM ('male', 'female', 'other', 'prefer_not_to_say');
CREATE TYPE invoice_type_enum AS ENUM ('online', 'offline');
CREATE TYPE payment_mode_enum AS ENUM ('online', 'offline');
CREATE TYPE id_type_enum AS ENUM ('passport', 'aadhar', 'driving_license', 'voter_id', 'pan_card');
CREATE TYPE tshirt_size_enum AS ENUM ('XS', 'S', 'M', 'L', 'XL', 'XXL', 'XXXL');
CREATE TYPE travel_type_enum AS ENUM ('flight', 'own_transport', 'city_pick_up', 'city_drop');
CREATE TYPE approval_status_enum AS ENUM ('pending', 'approved', 'rejected', 'under_review');
CREATE TYPE registration_status_enum AS ENUM ('draft', 'pending_approval', 'waitlisted', 'completed', 'cancelled');
CREATE TYPE basic_details_status_enum AS ENUM ('draft', 'approved', 'waitlisted');
CREATE TYPE payment_status_enum AS ENUM ('draft', 'online_pending', 'online_completed', 'offline_pending', 'offline_completed');
CREATE TYPE invoice_status_enum AS ENUM ('draft', 'invoice_pending', 'invoice_completed');
CREATE TYPE travel_status_enum AS ENUM ('draft', 'pending', 'completed');

-- hdb_program_registration Table (Main Registration Table)
CREATE TABLE hdb_program_registration (
    id BIGSERIAL PRIMARY KEY,
    program_session_id BIGINT NOT NULL,
    user_id BIGINT NULL,
    program_registration_seq_number INTEGER NULL,
    registration_number VARCHAR(50) NULL,
    waiting_list_seq_number INTEGER NULL,
    registration_status registration_status_enum NOT NULL DEFAULT 'draft',
    basic_details_status basic_details_status_enum NOT NULL DEFAULT 'draft',
    registration_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    cancellation_date TIMESTAMP NULL,
    cancelled_by BIGINT NULL,
    rm_contact BIGINT NULL,
    preferred_room_mate VARCHAR(255) NULL,
    
    -- Basic Details
    full_name VARCHAR(255) NOT NULL,
    gender gender_enum NOT NULL,
    mobile_number VARCHAR(20) NOT NULL,
    email_address VARCHAR(255) NOT NULL,
    dob DATE NULL,
    city VARCHAR(100) NULL,
    notes TEXT NULL,
    terms_accepted BOOLEAN NOT NULL DEFAULT FALSE,
    
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by BIGINT NULL,
    updated_by BIGINT NULL
);

-- hdb_registration_invoice_detail Table (Fixed table name)
CREATE TABLE hdb_registration_invoice_detail (
    id BIGSERIAL PRIMARY KEY,
    registration_id BIGINT NOT NULL,
    invoice_type invoice_type_enum NOT NULL,
    invoice_name VARCHAR(255) NOT NULL,
    invoice_email VARCHAR(255) NOT NULL,
    pan_number VARCHAR(20) NULL,
    is_gst_registered BOOLEAN NOT NULL DEFAULT FALSE,
    invoice_address TEXT NOT NULL,
    tax_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    invoice_sequence_number VARCHAR(50) NULL,
    razorpay_id VARCHAR(100) NULL,
    invoice_issued_date TIMESTAMP NULL,
    invoice_status invoice_status_enum NOT NULL DEFAULT 'draft',
    tds_applicable BOOLEAN NOT NULL DEFAULT FALSE,
    tan_number VARCHAR(20) NULL,
    handover_date TIMESTAMP NULL,
    handover_to VARCHAR(255) NULL,
    
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	created_by BIGINT NULL,
    updated_by BIGINT NULL
);

-- hdb_registration_payment_detail Table (Fixed table name)
CREATE TABLE hdb_registration_payment_detail (
    id BIGSERIAL PRIMARY KEY,
    registration_id BIGINT NOT NULL,
    program_session_id BIGINT NOT NULL,
    payment_mode payment_mode_enum NOT NULL,
    tax_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    tds DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    sub_total DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    razorpay_id VARCHAR(100) NULL,
    offline_meta JSONB NULL,
    original_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    gst_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    payment_status payment_status_enum NOT NULL DEFAULT 'draft',
    payment_date TIMESTAMP NULL,
    
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	created_by BIGINT NULL,
    updated_by BIGINT NULL
);

-- hdb_registration_travel_info Table
CREATE TABLE hdb_registration_travel_info (
    id BIGSERIAL PRIMARY KEY,
    registration_id BIGINT NOT NULL,
    id_type id_type_enum NOT NULL,
    id_number VARCHAR(50) NOT NULL,
    id_picture_url VARCHAR(500) NULL,
    user_picture_url VARCHAR(500) NULL,
    tshirt_size tshirt_size_enum NULL,
    travel_info_status travel_status_enum NOT NULL DEFAULT 'draft',
    
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	created_by BIGINT NULL,
    updated_by BIGINT NULL
);

-- hdb_registration_travel_plan Table
CREATE TABLE hdb_registration_travel_plan (
    id BIGSERIAL PRIMARY KEY,
    registration_id BIGINT NOT NULL,
    travel_type travel_type_enum NOT NULL,
    airline_name VARCHAR(100) NULL,
    flight_number VARCHAR(20) NULL,
    arrival_datetime TIMESTAMP NULL,
    departure_location VARCHAR(255) NULL,
    pickup_time TIME NULL,
    pickup_location VARCHAR(255) NULL,
    checkin_at TIME NULL,
    checkin_location VARCHAR(255) NULL,
    travel_plan_status travel_status_enum NOT NULL DEFAULT 'draft',
    
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by BIGINT NULL,
    updated_by BIGINT NULL
);

-- hdb_registration_custom_response Table
CREATE TABLE hdb_registration_custom_response (
    id BIGSERIAL PRIMARY KEY,
    registration_id BIGINT NOT NULL,
    question_id BIGINT NOT NULL,
    response_value JSONB NULL,
    
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	created_by BIGINT NULL,
    updated_by BIGINT NULL
);

-- hdb_registration_question_answer Table
CREATE TABLE hdb_registration_question_answer (
    id BIGSERIAL PRIMARY KEY,
    registration_id BIGINT NOT NULL,
    question_id BIGINT NOT NULL,
    answer_value JSONB NULL,

    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by BIGINT NULL,
    updated_by BIGINT NULL
);

-- hdb_registration_approval Table
CREATE TABLE hdb_registration_approval (
    id BIGSERIAL PRIMARY KEY,
    registration_id BIGINT NOT NULL,
    approval_status approval_status_enum NOT NULL DEFAULT 'pending',
    approval_date TIMESTAMP NULL,
    approved_by BIGINT NULL,
    rejection_reason TEXT NULL,
    reviewer_comments TEXT NULL,
    auto_approved BOOLEAN NOT NULL DEFAULT FALSE,
    
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by BIGINT NULL,
    updated_by BIGINT NULL
);

-- Create Indexes for hdb_program_registration
CREATE INDEX idx_hdb_program_session ON hdb_program_registration(program_session_id);
CREATE INDEX idx_hdb_user ON hdb_program_registration(user_id);
CREATE INDEX idx_hdb_registration_date ON hdb_program_registration(registration_date);
CREATE INDEX idx_hdb_rm_contact ON hdb_program_registration(rm_contact);
CREATE INDEX idx_hdb_cancelled_by ON hdb_program_registration(cancelled_by);
CREATE INDEX idx_hdb_created_by ON hdb_program_registration(created_by);
CREATE INDEX idx_hdb_updated_by ON hdb_program_registration(updated_by);

-- Create Indexes for hdb_registration_invoice_detail
CREATE INDEX idx_hdb_invoice_registration ON hdb_registration_invoice_detail(registration_id);
CREATE INDEX idx_hdb_invoice_type ON hdb_registration_invoice_detail(invoice_type);
CREATE INDEX idx_hdb_invoice_sequence ON hdb_registration_invoice_detail(invoice_sequence_number);
CREATE INDEX idx_hdb_invoice_razorpay_id ON hdb_registration_invoice_detail(razorpay_id);
CREATE INDEX idx_hdb_invoice_issued_date ON hdb_registration_invoice_detail(invoice_issued_date);

-- Create Indexes for hdb_registration_payment_detail
CREATE INDEX idx_hdb_payment_registration ON hdb_registration_payment_detail(registration_id);
CREATE INDEX idx_hdb_payment_program_session ON hdb_registration_payment_detail(program_session_id);
CREATE INDEX idx_hdb_payment_mode ON hdb_registration_payment_detail(payment_mode);
CREATE INDEX idx_hdb_payment_razorpay_id ON hdb_registration_payment_detail(razorpay_id);
CREATE INDEX idx_hdb_payment_date ON hdb_registration_payment_detail(payment_date);

-- Create Indexes for hdb_registration_travel_info
CREATE INDEX idx_hdb_travel_info_registration ON hdb_registration_travel_info(registration_id);
CREATE INDEX idx_hdb_travel_info_id_type ON hdb_registration_travel_info(id_type);

-- Create Indexes for hdb_registration_travel_plan
CREATE INDEX idx_hdb_travel_plan_registration ON hdb_registration_travel_plan(registration_id);
CREATE INDEX idx_hdb_travel_plan_type ON hdb_registration_travel_plan(travel_type);
CREATE INDEX idx_hdb_travel_plan_arrival ON hdb_registration_travel_plan(arrival_datetime);

-- Create Indexes for hdb_registration_custom_response
CREATE INDEX idx_hdb_custom_response_registration ON hdb_registration_custom_response(registration_id);
CREATE INDEX idx_hdb_custom_response_question ON hdb_registration_custom_response(question_id);

-- Create Indexes for hdb_registration_question_answer
CREATE INDEX idx_hdb_question_answer_registration ON hdb_registration_question_answer(registration_id);
CREATE INDEX idx_hdb_question_answer_question ON hdb_registration_question_answer(question_id);

-- Create Indexes for hdb_registration_approval
CREATE INDEX idx_hdb_approval_registration ON hdb_registration_approval(registration_id);
CREATE INDEX idx_hdb_approval_status ON hdb_registration_approval(approval_status);
CREATE INDEX idx_hdb_approval_approved_by ON hdb_registration_approval(approved_by);
CREATE INDEX idx_hdb_approval_date ON hdb_registration_approval(approval_date);

-- Add Foreign Key Constraints for hdb_program_registration
-- Note: Assuming these reference tables exist: program_session, users
ALTER TABLE hdb_program_registration
ADD CONSTRAINT fk_hdb_program_session FOREIGN KEY (program_session_id) REFERENCES program_session(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_hdb_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_hdb_cancelled_by FOREIGN KEY (cancelled_by) REFERENCES users(id) ON DELETE SET NULL,
ADD CONSTRAINT fk_hdb_rm_contact FOREIGN KEY (rm_contact) REFERENCES users(id) ON DELETE SET NULL,
ADD CONSTRAINT fk_hdb_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
ADD CONSTRAINT fk_hdb_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL;

-- Add Foreign Key Constraints for hdb_registration_invoice_detail
ALTER TABLE hdb_registration_invoice_detail
ADD CONSTRAINT fk_hdb_invoice_registration FOREIGN KEY (registration_id) REFERENCES hdb_program_registration(id) ON DELETE CASCADE;

-- Add Foreign Key Constraints for hdb_registration_payment_detail
ALTER TABLE hdb_registration_payment_detail
ADD CONSTRAINT fk_hdb_payment_registration FOREIGN KEY (registration_id) REFERENCES hdb_program_registration(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_hdb_payment_program_session FOREIGN KEY (program_session_id) REFERENCES program_session(id) ON DELETE CASCADE;

-- Add Foreign Key Constraints for hdb_registration_travel_info
ALTER TABLE hdb_registration_travel_info
ADD CONSTRAINT fk_hdb_travel_info_registration FOREIGN KEY (registration_id) REFERENCES hdb_program_registration(id) ON DELETE CASCADE;

-- Add Foreign Key Constraints for hdb_registration_travel_plan
ALTER TABLE hdb_registration_travel_plan
ADD CONSTRAINT fk_hdb_travel_plan_registration FOREIGN KEY (registration_id) REFERENCES hdb_program_registration(id) ON DELETE CASCADE;

-- Add Foreign Key Constraints for hdb_registration_custom_response
-- Note: Assuming hdb_question table exists (referenced as 'question' in entity)
ALTER TABLE hdb_registration_custom_response
ADD CONSTRAINT fk_hdb_custom_response_registration FOREIGN KEY (registration_id) REFERENCES hdb_program_registration(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_hdb_custom_response_question FOREIGN KEY (question_id) REFERENCES hdb_question(id) ON DELETE CASCADE;

-- Add Foreign Key Constraints for hdb_registration_question_answer
ALTER TABLE hdb_registration_question_answer
ADD CONSTRAINT fk_hdb_question_answer_registration FOREIGN KEY (registration_id) REFERENCES hdb_program_registration(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_hdb_question_answer_question FOREIGN KEY (question_id) REFERENCES hdb_question(id) ON DELETE CASCADE;

-- Add Foreign Key Constraints for hdb_registration_approval
ALTER TABLE hdb_registration_approval
ADD CONSTRAINT fk_hdb_approval_registration FOREIGN KEY (registration_id) REFERENCES hdb_program_registration(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_hdb_approval_approved_by FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL;

-- Add Unique Constraints
ALTER TABLE hdb_program_registration
ADD CONSTRAINT unique_hdb_registration_seq UNIQUE (program_session_id, program_registration_seq_number),
ADD CONSTRAINT unique_hdb_waitlist_seq UNIQUE (program_session_id, waiting_list_seq_number);

ALTER TABLE hdb_registration_invoice_detail
ADD CONSTRAINT unique_hdb_invoice_sequence UNIQUE (invoice_sequence_number);

ALTER TABLE hdb_registration_custom_response
ADD CONSTRAINT unique_hdb_registration_question UNIQUE (registration_id, question_id);

ALTER TABLE hdb_registration_question_answer
ADD CONSTRAINT unique_hdb_question_answer UNIQUE (registration_id, question_id);


-- PROGRAM REGISTRATION
-- hdb_program_registration
ALTER TABLE hdb_program_registration
  ADD COLUMN registration_status registration_status_enum DEFAULT 'draft',
  ADD COLUMN basic_details_status basic_details_status_enum DEFAULT 'draft';
-- hdb_registration_invoice_detail
ALTER TABLE hdb_registration_invoice_detail
  ADD COLUMN invoice_status invoice_status_enum DEFAULT 'draft';
-- hdb_registration_payment_detail
ALTER TABLE hdb_registration_payment_detail
  ADD COLUMN payment_status payment_status_enum DEFAULT 'draft';

-- hdb_registration_travel_info
ALTER TABLE hdb_registration_travel_info
  ADD COLUMN travel_info_status travel_status_enum DEFAULT 'draft';
-- hdb_registration_travel_plan
ALTER TABLE hdb_registration_travel_plan
  ADD COLUMN travel_plan_status travel_status_enum DEFAULT 'draft';







CREATE TYPE approval_status_enum AS ENUM ('pending', 'approved', 'rejected', 'under_review');
CREATE TYPE registration_status_enum AS ENUM ('draft', 'pending_approval','pending', 'waitlisted', 'completed', 'cancelled');
CREATE TYPE basic_details_status_enum AS ENUM ('draft', 'pending', 'waitlisted', 'completed', 'rejected');
CREATE TYPE payment_status_enum AS ENUM ('draft', 'online_pending', 'online_completed', 'offline_pending', 'offline_completed');
CREATE TYPE invoice_status_enum AS ENUM ('draft', 'invoice_pending', 'invoice_completed');
CREATE TYPE travel_status_enum AS ENUM ('draft', 'pending', 'completed');



SELECT e.enumlabel AS value
FROM pg_type t
JOIN pg_enum e ON t.oid = e.enumtypid
WHERE t.typname = 'approval_status_enum';


 CREATE TABLE user_role (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE user_role_map (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role_id INTEGER NOT NULL REFERENCES user_role(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO user_role (name) VALUES
  ('viewer'),
  ('admin'),
  ('mahatria'),
  ('rm');

ALTER TYPE program_status_enum ADD VALUE 'published';

ALTER TYPE payment_mode_enum ADD VALUE IF NOT EXISTS 'Cheque';
ALTER TYPE payment_mode_enum ADD VALUE IF NOT EXISTS 'Direct bank transfer (NEFT/RTGS)';
ALTER TYPE payment_mode_enum ADD VALUE IF NOT EXISTS 'Card swipe';
ALTER TYPE payment_mode_enum ADD VALUE IF NOT EXISTS 'Cash';
ALTER TYPE payment_mode_enum ADD VALUE IF NOT EXISTS 'Online (Through Razorpay)';

-- hdb_registration_payment_details_history Table
CREATE TABLE hdb_registration_payment_details_history ( 
    id BIGSERIAL PRIMARY KEY,
    payment_details_id BIGINT NOT NULL REFERENCES hdb_registration_payment_detail(id),
    payment_mode payment_mode_enum,
    razorpay_id VARCHAR(100),
    offline_meta JSONB,
    payment_status payment_status_enum DEFAULT 'draft'::payment_status_enum,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    created_by BIGINT,
    updated_by BIGINT
);

ALTER TABLE hdb_form_section ADD COLUMN display_order INTEGER DEFAULT 0;

-- hdb_preference Table
CREATE TABLE hdb_preference (
    id BIGSERIAL PRIMARY KEY,
    session_id BIGINT NOT NULL,
    program_id INT NOT NULL,
    registration_id BIGINT NOT NULL,
    priority_order INT DEFAULT 0,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMPTZ DEFAULT NULL,

    created_by INT,
    updated_by INT
);

-- Add Foreign Key Constraints for hdb_preference
ALTER TABLE hdb_preference
    ADD CONSTRAINT fk_preference_session FOREIGN KEY (session_id) REFERENCES program_session_v1(id) ON DELETE CASCADE,
    ADD CONSTRAINT fk_preference_program FOREIGN KEY (program_id) REFERENCES program_v2(id) ON DELETE CASCADE,
    ADD CONSTRAINT fk_preference_registration FOREIGN KEY (registration_id) REFERENCES hdb_program_registration(id) ON DELETE CASCADE,
    ADD CONSTRAINT fk_preference_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    ADD CONSTRAINT fk_preference_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL;

-- communication_type_enum and communication_status_enum
create type communication_type_enum AS ENUM ('whatsapp','email');
create type communication_status_enum AS ENUM ('initiated','success','failed');


-- hdb_communication_track Table
CREATE TABLE hdb_communication_track (
    id BIGSERIAL PRIMARY KEY,
    registration_id BIGINT NOT NULL,
    typ communication_type_enum,
    stats communication_status_enum not null default 'initiated',
	failed_reason text,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by BIGINT,
    updated_by BIGINT,

    CONSTRAINT fk_hdb_communication_track
        FOREIGN KEY (registration_id)
        REFERENCES public.hdb_program_registration (id)
        ON UPDATE NO ACTION
        ON DELETE CASCADE,

    CONSTRAINT fk_hdb_communication_track_created_by 
        FOREIGN KEY (created_by)
        REFERENCES public.users (id)
        ON UPDATE NO ACTION
        ON DELETE SET NULL,

    CONSTRAINT fk_hdb_communication_track_updated_by 
        FOREIGN KEY (updated_by)
        REFERENCES public.users (id)
        ON UPDATE NO ACTION
        ON DELETE SET NULL
);