-- Migration script for Infinitheism Dev to Prod

CREATE TABLE public.feature_flag (
    id integer NOT NULL,
    feature_key character varying(255) NOT NULL,
    label character varying(255) NOT NULL,
    description character varying(512),
    flag boolean DEFAULT false NOT NULL,
    status character varying(50) DEFAULT 'active'::character varying NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    deleted_at timestamp with time zone,
    created_by integer,
    updated_by integer
);


CREATE SEQUENCE public.feature_flag_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.feature_flag_id_seq OWNED BY public.feature_flag.id;

ALTER TABLE ONLY public.feature_flag ALTER COLUMN id SET DEFAULT nextval('public.feature_flag_id_seq'::regclass);

ALTER TABLE ONLY public.feature_flag
    ADD CONSTRAINT feature_flag_feature_key_key UNIQUE (feature_key);

ALTER TABLE ONLY public.feature_flag
    ADD CONSTRAINT feature_flag_pkey PRIMARY KEY (id);




CREATE TABLE public.hdb_address (
    address_id integer NOT NULL,
    location_id integer,
    addr_1 character varying(255),
    addr_2 character varying(255),
    landmark character varying(255),
    city character varying(100),
    state character varying(100),
    country character varying(100),
    pincode character varying(20),
    lat numeric(9,6),
    long numeric(9,6),
    type public.address_type_enum,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    created_by character varying(255),
    updated_by character varying(255)
);

CREATE SEQUENCE public.hdb_address_address_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE public.hdb_address_address_id_seq OWNED BY public.hdb_address.address_id;

ALTER TABLE ONLY public.hdb_address ALTER COLUMN address_id SET DEFAULT nextval('public.hdb_address_address_id_seq'::regclass);


ALTER TABLE ONLY public.hdb_address
    ADD CONSTRAINT hdb_address_pkey PRIMARY KEY (address_id);


CREATE TABLE public.hdb_communication_track (
    id bigint NOT NULL,
    registration_id bigint NOT NULL,
    typ public.communication_type_enum,
    stats public.communication_status_enum DEFAULT 'initiated'::public.communication_status_enum NOT NULL,
    failed_reason text,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    created_by bigint,
    updated_by bigint
);


CREATE SEQUENCE public.hdb_communication_track_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.hdb_communication_track_id_seq OWNED BY public.hdb_communication_track.id;

ALTER TABLE ONLY public.hdb_communication_track ALTER COLUMN id SET DEFAULT nextval('public.hdb_communication_track_id_seq'::regclass);

ALTER TABLE ONLY public.hdb_communication_track
    ADD CONSTRAINT hdb_communication_track_pkey PRIMARY KEY (id);


CREATE TABLE public.hdb_form_section (
    id integer NOT NULL,
    name character varying(255) NOT NULL,
    description text,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamp with time zone,
    created_by integer,
    updated_by integer,
    display_order integer DEFAULT 0,
    key character varying(255) DEFAULT NULL::character varying
);


CREATE SEQUENCE public.hdb_form_section_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.hdb_form_section_id_seq OWNED BY public.hdb_form_section.id;


CREATE TABLE public.hdb_option (
    id integer NOT NULL,
    typ public.hdb_option_type_enum NOT NULL,
    category_id integer,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamp with time zone,
    stats public.hdb_common_status_enum DEFAULT 'draft'::public.hdb_common_status_enum,
    created_by integer,
    updated_by integer,
    name character varying(255)
);


CREATE TABLE public.hdb_option_category (
    id integer NOT NULL,
    name character varying(255) NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamp with time zone,
    created_by integer,
    updated_by integer
);


CREATE SEQUENCE public.hdb_option_category_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE public.hdb_option_category_id_seq OWNED BY public.hdb_option_category.id;


CREATE SEQUENCE public.hdb_option_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.hdb_option_id_seq OWNED BY public.hdb_option.id;


CREATE TABLE public.hdb_preference (
    id bigint NOT NULL,
    session_id bigint,
    program_id integer NOT NULL,
    registration_id bigint NOT NULL,
    priority_order integer DEFAULT 0,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamp with time zone,
    created_by integer,
    updated_by integer,
    preferred_session_id bigint,
    preferred_program_id integer
);


CREATE SEQUENCE public.hdb_preference_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.hdb_preference_id_seq OWNED BY public.hdb_preference.id;


CREATE TABLE public.hdb_program_question (
    id integer NOT NULL,
    program_id integer NOT NULL,
    question_id integer NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamp with time zone,
    program_session_id integer,
    registration_level public.registration_level_enum DEFAULT 'program'::public.registration_level_enum,
    created_by integer,
    updated_by integer,
    display_order integer DEFAULT 0,
    form_section_id integer
);

CREATE SEQUENCE public.hdb_program_question_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE public.hdb_program_question_id_seq OWNED BY public.hdb_program_question.id;

CREATE TABLE public.hdb_program_registration (
    id bigint NOT NULL,
    program_session_id bigint,
    user_id bigint,
    program_registration_seq_number integer,
    waiting_list_seq_number integer,
    registration_date timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    cancellation_date timestamp with time zone,
    cancelled_by bigint,
    rm_contact bigint,
    preferred_room_mate character varying(255),
    full_name character varying(255),
    gender public.gender_enum,
    mobile_number character varying(20),
    email_address character varying(255),
    dob date,
    city character varying(100),
    notes text,
    terms_accepted boolean DEFAULT false,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    created_by bigint,
    updated_by bigint,
    deleted_at timestamp with time zone,
    program_id bigint,
    registration_status public.registration_status_enum DEFAULT 'draft'::public.registration_status_enum,
    basic_details_status public.basic_details_status_enum DEFAULT 'draft'::public.basic_details_status_enum,
    allocated_program_id bigint,
    allocated_session_id bigint,
    rm_review text,
    registration_seq_number character varying(50),
    wait_list_registration_seq_number character varying(50),
    user_profile_url text,
    no_of_hdbs integer DEFAULT 0,
    user_video_url text,
    pro_forma_invoice_name character varying(255),
    pro_forma_invoice_address text,
    pro_forma_invoice_pdf_url text,
    proforma_invoice_seq_number character varying(255)
);

CREATE SEQUENCE public.hdb_program_registration_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE public.hdb_program_registration_id_seq OWNED BY public.hdb_program_registration.id;

CREATE TABLE public.hdb_program_registration_rm_rating (
    id integer NOT NULL,
    program_registration_id bigint,
    rm_id bigint NOT NULL,
    rating_key character varying(255) NOT NULL,
    rating numeric DEFAULT 0,
    created_at timestamp with time zone,
    updated_at timestamp with time zone,
    deleted_at timestamp with time zone,
    created_by bigint,
    updated_by bigint
);


CREATE SEQUENCE public.hdb_program_registration_rm_rating_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE public.hdb_program_registration_rm_rating_id_seq OWNED BY public.hdb_program_registration_rm_rating.id;

CREATE TABLE public.hdb_program_registration_rm_rating_type (
    id bigint NOT NULL,
    rating_key character varying(255) NOT NULL,
    name character varying(255) NOT NULL,
    created_at timestamp with time zone,
    updated_at timestamp with time zone,
    created_by bigint,
    updated_by bigint
);


CREATE TABLE public.hdb_program_registration_swap (
    id integer NOT NULL,
    program_registration_id bigint,
    type character varying(255),
    status character varying(255),
    created_at timestamp with time zone,
    updated_at timestamp with time zone,
    created_by bigint,
    updated_by bigint,
    comment text,
    target_program_id bigint,
    target_programs integer[],
    allocated_program_id bigint,
    current_program_id bigint
);


CREATE SEQUENCE public.hdb_program_registration_swap_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.hdb_program_registration_swap_id_seq OWNED BY public.hdb_program_registration_swap.id;


CREATE TABLE public.hdb_question (
    id integer NOT NULL,
    label character varying(1024) NOT NULL,
    config jsonb,
    stats public.hdb_common_status_enum DEFAULT 'draft'::public.hdb_common_status_enum NOT NULL,
    typ character varying(50) NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamp with time zone,
    created_by integer,
    updated_by integer,
    form_section_id integer,
    answer_location character varying(1024),
    binding_key character varying(255)
);


CREATE SEQUENCE public.hdb_question_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.hdb_question_id_seq OWNED BY public.hdb_question.id;

CREATE TABLE public.hdb_question_option_map (
    id integer NOT NULL,
    question_id integer NOT NULL,
    option_id integer NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamp with time zone,
    created_by integer,
    updated_by integer
);

CREATE SEQUENCE public.hdb_question_option_map_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE public.hdb_question_option_map_id_seq OWNED BY public.hdb_question_option_map.id;

CREATE TABLE public.hdb_registration_approval (
    id bigint NOT NULL,
    registration_id bigint NOT NULL,
    approval_status public.approval_status_enum DEFAULT 'pending'::public.approval_status_enum NOT NULL,
    approval_date timestamp with time zone,
    approved_by bigint,
    rejection_reason text,
    reviewer_comments text,
    auto_approved boolean DEFAULT false NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    created_by bigint,
    updated_by bigint,
    deleted_at timestamp with time zone
);


CREATE SEQUENCE public.hdb_registration_approval_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.hdb_registration_approval_id_seq OWNED BY public.hdb_registration_approval.id;


CREATE TABLE public.hdb_registration_approval_track (
    id bigint NOT NULL,
    approval_id bigint NOT NULL,
    registration_id bigint NOT NULL,
    approval_status public.approval_status_enum DEFAULT 'pending'::public.approval_status_enum NOT NULL,
    approval_date timestamp with time zone,
    approved_by bigint,
    rejection_reason text,
    reviewer_comments text,
    auto_approved boolean DEFAULT false NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    created_by bigint,
    updated_by bigint,
    type character varying(255) DEFAULT 'registration'::character varying,
    swap_request_id bigint
);


CREATE SEQUENCE public.hdb_registration_approval_track_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.hdb_registration_approval_track_id_seq OWNED BY public.hdb_registration_approval_track.id;


CREATE TABLE public.hdb_registration_custom_response (
    id bigint NOT NULL,
    registration_id bigint NOT NULL,
    question_id bigint NOT NULL,
    response_value jsonb,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    created_by bigint,
    updated_by bigint,
    deleted_at timestamp without time zone
);


CREATE SEQUENCE public.hdb_registration_custom_response_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.hdb_registration_custom_response_id_seq OWNED BY public.hdb_registration_custom_response.id;


CREATE TABLE public.hdb_registration_invoice_detail (
    id bigint NOT NULL,
    registration_id bigint NOT NULL,
    invoice_type public.invoice_type_enum,
    invoice_name character varying(255),
    invoice_email character varying(255),
    pan_number character varying(20),
    is_gst_registered boolean DEFAULT false,
    invoice_address text,
    tax_amount numeric(10,2) DEFAULT 0.00,
    invoice_sequence_number character varying(50),
    razorpay_id character varying(100),
    invoice_issued_date timestamp with time zone,
    tds_applicable boolean DEFAULT false,
    tan_number character varying(20),
    handover_date timestamp with time zone,
    handover_to character varying(255),
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    created_by bigint,
    updated_by bigint,
    gst_number character varying(50),
    zip character varying(10),
    deleted_at timestamp without time zone,
    invoice_status public.invoice_status_enum DEFAULT 'draft'::public.invoice_status_enum,
    tds_amount numeric(10,2),
    einvoice_qr_link character varying(1024),
    einvoice_ack_date character varying(500),
    einvoice_inv_ref_num character varying(1024),
    einvoice_is_cancellable boolean,
    einvoice_status_formatted character varying(1024),
    einvoice_formatted_status character varying(500),
    einvoice_ack_number character varying(500),
    einvoice_status character varying(500),
    invoice_pdf_url character varying(2048)
);


CREATE SEQUENCE public.hdb_registration_invoice_detail_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;



ALTER SEQUENCE public.hdb_registration_invoice_detail_id_seq OWNED BY public.hdb_registration_invoice_detail.id;


CREATE TABLE public.hdb_registration_payment_detail (
    id bigint NOT NULL,
    registration_id bigint NOT NULL,
    program_session_id bigint,
    payment_mode public.payment_mode_enum,
    tax_amount numeric(10,2) DEFAULT 0.00,
    tds numeric(10,2) DEFAULT 0.00,
    sub_total numeric(10,2) DEFAULT 0.00,
    razorpay_id character varying(100),
    offline_meta jsonb,
    original_amount numeric(10,2) DEFAULT 0.00,
    gst_amount numeric(10,2) DEFAULT 0.00,
    payment_date timestamp with time zone,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    created_by bigint,
    updated_by bigint,
    deleted_at timestamp without time zone,
    payment_status public.payment_status_enum DEFAULT 'draft'::public.payment_status_enum,
    mark_as_received_date timestamp with time zone
);


CREATE SEQUENCE public.hdb_registration_payment_detail_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.hdb_registration_payment_detail_id_seq OWNED BY public.hdb_registration_payment_detail.id;


CREATE TABLE public.hdb_registration_payment_details_history (
    id bigint NOT NULL,
    payment_details_id bigint NOT NULL,
    razorpay_id character varying(100),
    offline_meta jsonb,
    payment_status public.payment_status_enum DEFAULT 'draft'::public.payment_status_enum,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    created_by bigint,
    updated_by bigint,
    payment_mode public.payment_mode_enum
);



CREATE SEQUENCE public.hdb_registration_payment_details_history_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;



ALTER SEQUENCE public.hdb_registration_payment_details_history_id_seq OWNED BY public.hdb_registration_payment_details_history.id;


CREATE TABLE public.hdb_registration_question_answer (
    id bigint NOT NULL,
    registration_id bigint NOT NULL,
    question_id bigint NOT NULL,
    answer_value jsonb,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    created_by bigint,
    updated_by bigint
);


CREATE SEQUENCE public.hdb_registration_question_answer_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;



ALTER SEQUENCE public.hdb_registration_question_answer_id_seq OWNED BY public.hdb_registration_question_answer.id;

CREATE TABLE public.hdb_registration_travel_info (
    id bigint NOT NULL,
    registration_id bigint NOT NULL,
    id_type public.id_type_enum,
    id_number character varying(50),
    id_picture_url character varying(500),
    user_picture_url character varying(500),
    tshirt_size public.tshirt_size_enum,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    created_by bigint,
    updated_by bigint,
    deleted_at timestamp without time zone,
    travel_info_status public.travel_status_enum DEFAULT 'pending'::public.travel_status_enum
);



CREATE SEQUENCE public.hdb_registration_travel_info_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.hdb_registration_travel_info_id_seq OWNED BY public.hdb_registration_travel_info.id;


CREATE TABLE public.hdb_registration_travel_plan (
    id bigint NOT NULL,
    registration_id bigint NOT NULL,
    travel_type public.travel_type_enum,
    airline_name character varying(100),
    flight_number character varying(20),
    arrival_datetime timestamp with time zone,
    departure_location character varying(255),
    pickup_time time without time zone,
    pickup_location character varying(255),
    checkin_time time without time zone,
    checkin_location character varying(255),
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    created_by bigint,
    updated_by bigint,
    deleted_at timestamp with time zone,
    travel_plan_status public.travel_status_enum DEFAULT 'pending'::public.travel_status_enum,
    checkin_at timestamp with time zone,
    return_travel_type public.travel_type_enum,
    departure_datetime timestamp with time zone,
    departure_flight_number character varying(20),
    departure_airline_name character varying(100),
    arrival_from character varying(255),
    departure_to character varying(255)
);


CREATE SEQUENCE public.hdb_registration_travel_plan_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.hdb_registration_travel_plan_id_seq OWNED BY public.hdb_registration_travel_plan.id;


CREATE TABLE public.hdb_swap_requested_program (
    id integer NOT NULL,
    swap_request_id bigint,
    program_id bigint,
    created_at timestamp without time zone,
    updated_at timestamp without time zone,
    created_by bigint,
    updated_by bigint
);


CREATE SEQUENCE public.hdb_swap_requested_program_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.hdb_swap_requested_program_id_seq OWNED BY public.hdb_swap_requested_program.id;


CREATE TABLE public.lookup_data (
    id integer NOT NULL,
    lookup_category character varying(255) NOT NULL,
    lookup_category_name character varying(255),
    lookup_key character varying(255) NOT NULL,
    lookup_label character varying(255) NOT NULL,
    lookup_status public.hdb_common_status_enum DEFAULT 'active'::public.hdb_common_status_enum NOT NULL
);



CREATE SEQUENCE public.lookup_data_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.lookup_data_id_seq OWNED BY public.lookup_data.id;


CREATE TABLE public.program_message (
    id integer NOT NULL,
    program_id integer NOT NULL,
    seeker_id integer NOT NULL,
    sender_id integer NOT NULL,
    receiver_id integer,
    receiver_role_flag boolean DEFAULT false,
    cc_role_id integer,
    content text NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    created_by integer
);


CREATE SEQUENCE public.program_message_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.program_message_id_seq OWNED BY public.program_message.id;


CREATE TABLE public.program_registration_failures (
    id bigint NOT NULL,
    program_id bigint NOT NULL,
    program_session_id bigint,
    payload jsonb NOT NULL,
    failure_reason character varying(255) NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    deleted_at timestamp with time zone,
    created_by bigint,
    updated_by bigint
);


CREATE SEQUENCE public.program_registration_failures_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.program_registration_failures_id_seq OWNED BY public.program_registration_failures.id;


CREATE TABLE public.user_role (
    id integer NOT NULL,
    name character varying(100) NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    role_key character varying(255)
);


CREATE SEQUENCE public.user_role_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.user_role_id_seq OWNED BY public.user_role.id;


CREATE TABLE public.user_role_map (
    id integer NOT NULL,
    user_id integer NOT NULL,
    role_id integer NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP
);


CREATE SEQUENCE public.user_role_map_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.user_role_map_id_seq OWNED BY public.user_role_map.id;


CREATE TABLE public.user_session (
    id bigint NOT NULL,
    user_id bigint,
    phone_number character varying(255),
    country_code character varying(5),
    email character varying(320),
    login_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    otp character varying(6),
    attempt_count smallint DEFAULT 0,
    jwt_token text,
    session_status public.user_session_status_enum DEFAULT 'initiated'::public.user_session_status_enum NOT NULL,
    user_agent character varying(1024),
    ip_address character varying(45),
    expires_at timestamp with time zone,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP
);


COMMENT ON TABLE public.user_session IS 'Stores user session information, including JWTs and OTPs.';


COMMENT ON COLUMN public.user_session.id IS 'Unique identifier for the user session.';

COMMENT ON COLUMN public.user_session.user_id IS 'Foreign key to the users table, identifying the user.';


COMMENT ON COLUMN public.user_session.phone_number IS 'The phone number used to initiate this session, if applicable.';


COMMENT ON COLUMN public.user_session.country_code IS 'The country code for the phone number used, if applicable.';

COMMENT ON COLUMN public.user_session.email IS 'The email address used to initiate this session, if applicable.';


COMMENT ON COLUMN public.user_session.login_at IS 'Timestamp of when the user logged in and the session was created.';

COMMENT ON COLUMN public.user_session.otp IS 'The one-time password sent for login, if applicable (6-digit alphanumeric).';



COMMENT ON COLUMN public.user_session.attempt_count IS 'Number of OTP validation attempts for this session.';


COMMENT ON COLUMN public.user_session.jwt_token IS 'The JWT bearer token for this session. Populated after successful validation.';


COMMENT ON COLUMN public.user_session.session_status IS 'The current status of the session (e.g., initiated, validated, logged_out, expired, failed).';


COMMENT ON COLUMN public.user_session.user_agent IS 'Information about the user''s device, such as User-Agent, OS, etc.';


COMMENT ON COLUMN public.user_session.ip_address IS 'The IP address from which the session was initiated.';


COMMENT ON COLUMN public.user_session.expires_at IS 'Timestamp when the session is set to expire.';


CREATE SEQUENCE public.user_session_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.user_session_id_seq OWNED BY public.user_session.id;

-- program_type_question , program_type_section these are not used they are for refactoring purpose
-- infini_message_user_group_mapping


ALTER TABLE ONLY public.hdb_form_section ALTER COLUMN id SET DEFAULT nextval('public.hdb_form_section_id_seq'::regclass);


ALTER TABLE ONLY public.hdb_option ALTER COLUMN id SET DEFAULT nextval('public.hdb_option_id_seq'::regclass);


ALTER TABLE ONLY public.hdb_option_category ALTER COLUMN id SET DEFAULT nextval('public.hdb_option_category_id_seq'::regclass);

ALTER TABLE ONLY public.hdb_preference ALTER COLUMN id SET DEFAULT nextval('public.hdb_preference_id_seq'::regclass);

ALTER TABLE ONLY public.hdb_program_question ALTER COLUMN id SET DEFAULT nextval('public.hdb_program_question_id_seq'::regclass);


ALTER TABLE ONLY public.hdb_program_registration ALTER COLUMN id SET DEFAULT nextval('public.hdb_program_registration_id_seq'::regclass);

ALTER TABLE ONLY public.hdb_program_registration_rm_rating ALTER COLUMN id SET DEFAULT nextval('public.hdb_program_registration_rm_rating_id_seq'::regclass);


ALTER TABLE ONLY public.hdb_program_registration_swap ALTER COLUMN id SET DEFAULT nextval('public.hdb_program_registration_swap_id_seq'::regclass);

ALTER TABLE ONLY public.hdb_question ALTER COLUMN id SET DEFAULT nextval('public.hdb_question_id_seq'::regclass);

ALTER TABLE ONLY public.hdb_question_option_map ALTER COLUMN id SET DEFAULT nextval('public.hdb_question_option_map_id_seq'::regclass);


ALTER TABLE ONLY public.hdb_registration_approval ALTER COLUMN id SET DEFAULT nextval('public.hdb_registration_approval_id_seq'::regclass);


ALTER TABLE ONLY public.hdb_registration_approval_track ALTER COLUMN id SET DEFAULT nextval('public.hdb_registration_approval_track_id_seq'::regclass);


ALTER TABLE ONLY public.hdb_registration_custom_response ALTER COLUMN id SET DEFAULT nextval('public.hdb_registration_custom_response_id_seq'::regclass);


ALTER TABLE ONLY public.hdb_registration_invoice_detail ALTER COLUMN id SET DEFAULT nextval('public.hdb_registration_invoice_detail_id_seq'::regclass);


ALTER TABLE ONLY public.hdb_registration_payment_detail ALTER COLUMN id SET DEFAULT nextval('public.hdb_registration_payment_detail_id_seq'::regclass);


ALTER TABLE ONLY public.hdb_registration_payment_details_history ALTER COLUMN id SET DEFAULT nextval('public.hdb_registration_payment_details_history_id_seq'::regclass);


ALTER TABLE ONLY public.hdb_registration_question_answer ALTER COLUMN id SET DEFAULT nextval('public.hdb_registration_question_answer_id_seq'::regclass);


ALTER TABLE ONLY public.hdb_registration_travel_info ALTER COLUMN id SET DEFAULT nextval('public.hdb_registration_travel_info_id_seq'::regclass);


ALTER TABLE ONLY public.hdb_registration_travel_plan ALTER COLUMN id SET DEFAULT nextval('public.hdb_registration_travel_plan_id_seq'::regclass);


ALTER TABLE ONLY public.hdb_swap_requested_program ALTER COLUMN id SET DEFAULT nextval('public.hdb_swap_requested_program_id_seq'::regclass);

ALTER TABLE public.program_type
ADD COLUMN deleted_at timestamp without time zone;

ALTER TABLE public.program_type_v1
ALTER COLUMN program_type DROP NOT NULL;

ALTER TABLE public.program_v1
ALTER COLUMN program DROP NOT NULL;

ALTER TABLE public.users
ALTER COLUMN firebase_id_ext TYPE character varying(255);

ALTER TABLE public.users
ALTER COLUMN alternate_country_code TYPE character varying(255);

ALTER TABLE public.users
ALTER COLUMN alternate_phone_number TYPE character varying(255);

ALTER TABLE public.users
ALTER COLUMN alternate_email TYPE character varying(255);


-- Columns with default but nullable (initially)
ALTER TABLE public.program_type_v1
ADD COLUMN mode_of_operation public.mode_of_operation_enum,
ADD COLUMN online_type public.online_type_enum DEFAULT 'NA',
ADD COLUMN max_session_duration_days integer DEFAULT 8,
ADD COLUMN has_multiple_sessions boolean DEFAULT true,
ADD COLUMN frequency public.frequency_enum DEFAULT 'yearly',
ADD COLUMN default_start_time time without time zone,
ADD COLUMN default_end_time time without time zone,
ADD COLUMN default_duration character varying(50),
ADD COLUMN requires_residence boolean DEFAULT false,
ADD COLUMN involves_travel boolean DEFAULT false,
ADD COLUMN has_checkin_checkout boolean DEFAULT false,
ADD COLUMN requires_payment boolean DEFAULT true,
ADD COLUMN requires_attendance_all_sessions boolean DEFAULT true,
ADD COLUMN allows_minors boolean DEFAULT false,
ADD COLUMN allows_proxy_registration boolean DEFAULT false,
ADD COLUMN requires_approval boolean DEFAULT false,
ADD COLUMN registration_level public.registration_level_enum DEFAULT 'session',
ADD COLUMN is_active boolean DEFAULT true,
ADD COLUMN created_by integer,
ADD COLUMN updated_by integer,
ADD COLUMN no_of_session integer DEFAULT 0,
ADD COLUMN waitlist_applicable boolean DEFAULT false,
ADD COLUMN max_capacity integer DEFAULT 0,
ADD COLUMN meta json DEFAULT '{}'::json,
ADD COLUMN venue text,
ADD COLUMN limited_seats boolean DEFAULT false,
ADD COLUMN is_grouped_program boolean DEFAULT false,
ADD COLUMN logo_url character varying(1024),
ADD COLUMN banner_url character varying(1024),
ADD COLUMN key character varying(255),
ADD COLUMN igst numeric(5,2) DEFAULT 0,
ADD COLUMN cgst numeric(5,2) DEFAULT 0,
ADD COLUMN sgst numeric(5,2) DEFAULT 0,
ADD COLUMN gst_percentage numeric(5,2) DEFAULT 18.00,
ADD COLUMN tds_percent numeric(5,2) DEFAULT 0,
ADD COLUMN gst_number character varying(50),
ADD COLUMN tds_applicability public.tds_applicability_enum,
ADD COLUMN invoice_sender_name character varying(255),
ADD COLUMN invoice_sender_pan character varying(50),
ADD COLUMN invoice_sender_cin character varying(50),
ADD COLUMN invoice_sender_address text,
ADD COLUMN helpline_number text,
ADD COLUMN email_sender_name text,
ADD COLUMN venue_name_in_emails text,
ADD COLUMN is_special_program boolean DEFAULT false,
ADD COLUMN deleted_at timestamp with time zone;


-- select * from program_type_v1; -- initially only there are 1	"Infinipath"	"Infinipath"	"A personality development program"	"2024-12-19 01:11:13.427294+05:30"	"2024-12-19 01:11:13.427294+05:30"

UPDATE public.program_type_v1
SET mode_of_operation = 'online'
WHERE mode_of_operation IS NULL;

UPDATE public.program_type_v1
SET key = 'PT_INFINIPATH'
WHERE key IS NULL;

UPDATE 
    public.program_type_v1
SET 
    no_of_session = 0
WHERE 
    no_of_session IS NULL;


ALTER TABLE public.program_type_v1
    ALTER COLUMN mode_of_operation SET NOT NULL,
    ALTER COLUMN key SET NOT NULL,
    ALTER COLUMN no_of_session SET NOT NULL;

ALTER TABLE public.program_type_v1
ALTER COLUMN logo_url SET DEFAULT NULL,
ALTER COLUMN banner_url SET DEFAULT NULL;


-- existing program_v1 "[{""id"":1,""program"":""P001"",""program_type_id"":1,""name"":""Infinipath Program"",""description"":""An introductory program for personal development"",""start_date"":""2024-12-20"",""start_time"":""09:00:00+00"",""end_date"":""2024-12-21"",""end_time"":""17:00:00+00"",""no_of_session"":2,""registration_start_date"":""2024-12-10"",""registration_start_time"":""08:00:00+00"",""registration_end_date"":""2024-12-19"",""registration_end_time"":""20:00:00+00"",""meta"":{""difficulty"": ""beginner""},""created_at"":""2024-12-19T01:11:22.198819+05:30"",""updated_at"":""2024-12-19T01:11:22.198819+05:30""}]"
-- SELECT 
--     json_agg(program_v1)
-- FROM 
--     program_v1;


ALTER TABLE public.program_v1
ADD COLUMN mode_of_operation public.mode_of_operation_enum,
ADD COLUMN online_type public.online_type_enum DEFAULT 'NA',
ADD COLUMN max_session_duration_days integer DEFAULT 8,
ADD COLUMN has_multiple_sessions boolean DEFAULT true,
ADD COLUMN frequency public.frequency_enum DEFAULT 'yearly',
ADD COLUMN default_start_time time without time zone,
ADD COLUMN default_end_time time without time zone,
ADD COLUMN duration character varying(50),
ADD COLUMN requires_residence boolean DEFAULT false,
ADD COLUMN involves_travel boolean DEFAULT false,
ADD COLUMN has_checkin_checkout boolean DEFAULT false,
ADD COLUMN requires_payment boolean DEFAULT true,
ADD COLUMN requires_attendance_all_sessions boolean DEFAULT true,
ADD COLUMN allows_minors boolean DEFAULT false,
ADD COLUMN allows_proxy_registration boolean DEFAULT false,
ADD COLUMN requires_approval boolean DEFAULT false,
ADD COLUMN registration_level public.registration_level_enum DEFAULT 'session',
ADD COLUMN waitlist_applicable boolean DEFAULT false,
ADD COLUMN limited_seats boolean DEFAULT false,
ADD COLUMN is_grouped_program boolean DEFAULT false,
ADD COLUMN max_capacity integer,
ADD COLUMN total_seats integer DEFAULT 0,
ADD COLUMN waitlist_trigger_count integer DEFAULT 0,
ADD COLUMN available_seats integer DEFAULT 0,
ADD COLUMN filled_seats integer DEFAULT 0,
ADD COLUMN program_fee numeric(10,2),
ADD COLUMN base_price numeric(10,2) DEFAULT 0.00,
ADD COLUMN gst_percentage numeric(5,2) DEFAULT 18.00,
ADD COLUMN currency character varying(3) DEFAULT 'INR',
ADD COLUMN venue text,
ADD COLUMN status public.program_status_enum DEFAULT 'draft',
ADD COLUMN is_active boolean DEFAULT true,
ADD COLUMN code character varying(50),
ADD COLUMN banner_image_url character varying(1024) DEFAULT NULL,
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 group_id uuid,
ADD COLUMN is_primary_program boolean DEFAULT false,
ADD COLUMN primary_program_id integer,
ADD COLUMN group_display_order integer DEFAULT 1,
ADD COLUMN cgst numeric(5,2) DEFAULT 0,
ADD COLUMN sgst numeric(5,2) DEFAULT 0,
ADD COLUMN igst numeric(5,2) DEFAULT 0,
ADD COLUMN tan_percent numeric(5,2) DEFAULT 0,
ADD COLUMN checkin_at timestamp with time zone,
ADD COLUMN checkout_at timestamp with time zone,
ADD COLUMN starts_at timestamp with time zone,
ADD COLUMN ends_at timestamp with time zone,
ADD COLUMN tds_percent numeric(5,2) DEFAULT 0,
ADD COLUMN gst_number character varying(50),
ADD COLUMN tds_applicability public.tds_applicability_enum,
ADD COLUMN invoice_sender_name character varying(255),
ADD COLUMN invoice_sender_pan character varying(50),
ADD COLUMN invoice_sender_cin character varying(50),
ADD COLUMN venue_address_id integer,
ADD COLUMN invoice_sender_address text,
ADD COLUMN helpline_number text,
ADD COLUMN email_sender_name text,
ADD COLUMN venue_name_in_emails text,
ADD COLUMN launch_date timestamp with time zone,
ADD COLUMN logo_url character varying(1024) DEFAULT NULL,
ADD COLUMN checkin_ends_at timestamp with time zone,
ADD COLUMN checkout_ends_at timestamp with time zone;


ALTER TABLE public.program_v1
ADD COLUMN IF NOT EXISTS created_by integer,
ADD COLUMN IF NOT EXISTS updated_by integer,
ADD COLUMN IF NOT EXISTS deleted_at timestamp without time zone;

-- Optional: Set default on existing 'meta' column (if needed)
ALTER TABLE public.program_v1
ALTER COLUMN meta SET DEFAULT '{}'::json;

ALTER TABLE public.program_v1 
ADD COLUMN IF NOT EXISTS registration_starts_at timestamp with time zone;

ALTER TABLE public.program_v1 
ADD COLUMN IF NOT EXISTS registration_ends_at timestamp with time zone;

UPDATE public.program_v1 
SET mode_of_operation = COALESCE(mode_of_operation, 'online'::public.mode_of_operation_enum)
WHERE mode_of_operation IS NULL;

ALTER TABLE public.program_v1 
ALTER COLUMN mode_of_operation SET NOT NULL;

-- select * from program_v1

ALTER TABLE public.users ADD COLUMN IF NOT EXISTS "updatedAt" timestamp with time zone DEFAULT CURRENT_TIMESTAMP; -- dev default
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS is_infiminute boolean;
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS is_infimessages boolean;
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS user_approval_status character varying(20);
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS first_name character varying(255);
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS last_name character varying(255);
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS status_updated_at timestamp with time zone;
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS is_infipath boolean;
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS email character varying(319);
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS is_infimessage_user_active boolean;
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS is_infipath_user_active boolean;
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS name_audio character varying;
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS is_infiminute_user_active boolean;
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS profile_url character varying;
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS address text;
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS country_code character varying;
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS associated_since date;
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS gender character varying(10);
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS dob date;
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS other_address text;
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS firebase_id_ext character varying(255);
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS acknowledgement_date timestamp with time zone;
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS terms_and_conditions boolean DEFAULT false;
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS alternate_country_code character varying(255);
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS alternate_phone_number character varying(255);
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS alternate_email character varying(255);
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS encrypted_phone_number character varying(1024);
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS masked_phone_number character varying(1024);
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS encrypted_email character varying(1024);
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS is_system_user boolean DEFAULT false;
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS is_ai_user boolean DEFAULT false;
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS user_type public.user_type_enum DEFAULT 'Seeker'::public.user_type_enum;
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS legal_full_name character varying(255);


ALTER TABLE public.users
    ALTER COLUMN terms_and_conditions SET DEFAULT false;

-- select * from program_session

-- Add missing columns
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS mode_of_operation public.mode_of_operation_enum;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS online_type public.online_type_enum DEFAULT 'NA'::public.online_type_enum;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS max_session_duration_days integer DEFAULT 8;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS has_multiple_sessions boolean DEFAULT true;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS frequency public.frequency_enum DEFAULT 'yearly'::public.frequency_enum;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS default_start_time time without time zone;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS default_end_time time without time zone;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS duration character varying(50);
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS requires_residence boolean DEFAULT false;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS involves_travel boolean DEFAULT false;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS has_checkin_checkout boolean DEFAULT false;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS requires_payment boolean DEFAULT true;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS requires_attendance_all_sessions boolean DEFAULT true;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS allows_minors boolean DEFAULT false;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS allows_proxy_registration boolean DEFAULT false;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS requires_approval boolean DEFAULT false;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS registration_level public.registration_level_enum DEFAULT 'session'::public.registration_level_enum;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS waitlist_applicable boolean DEFAULT false;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS limited_seats boolean DEFAULT false;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS is_grouped_program boolean DEFAULT false;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS total_seats integer;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS waitlist_trigger_count integer;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS available_seats integer;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS reserved_seats integer DEFAULT 0;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS filled_seats integer DEFAULT 0;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS max_capacity integer;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS session_fee numeric(10,2);
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS base_price numeric(10,2) DEFAULT 0.00;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS gst_percentage numeric(5,2) DEFAULT 18.00;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS currency character varying(3) DEFAULT 'INR'::character varying;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS venue text;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS status public.session_status_enum DEFAULT 'scheduled'::public.session_status_enum;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS is_active boolean DEFAULT true;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS code character varying(50);
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS display_order integer DEFAULT 1;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS banner_image_url character varying(1024) DEFAULT NULL::character varying;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS meeting_link character varying(500);
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS meeting_id character varying(100);
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS meeting_password character varying(100);

-- JSON column default
ALTER TABLE public.program_session ALTER COLUMN meta SET DEFAULT '{}'::json;

-- Additional columns
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS deleted_at timestamp without time zone;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS created_by integer;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS updated_by integer;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS is_residence_required boolean DEFAULT false;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS is_travel_required boolean DEFAULT false;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS cgst numeric(5,2) DEFAULT 0;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS sgst numeric(5,2) DEFAULT 0;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS igst numeric(5,2) DEFAULT 0;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS tan_percent numeric(5,2) DEFAULT 0;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS registration_starts_at timestamp with time zone;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS registration_ends_at timestamp with time zone;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS checkin_at timestamp with time zone;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS checkout_at timestamp with time zone;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS starts_at timestamp with time zone;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS ends_at timestamp with time zone;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS tds_percent numeric(5,2) DEFAULT 0;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS gst_number character varying(50);
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS tds_applicability public.tds_applicability_enum;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS invoice_sender_name character varying(255);
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS invoice_sender_pan character varying(50);
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS invoice_sender_cin character varying(50);
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS venue_address_id integer;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS invoice_sender_address text;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS helpline_number text;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS email_sender_name text;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS venue_name_in_emails text;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS launch_date timestamp with time zone;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS logo_url character varying(1024) DEFAULT NULL::character varying;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS checkin_ends_at timestamp with time zone;
ALTER TABLE public.program_session ADD COLUMN IF NOT EXISTS checkout_ends_at timestamp with time zone;

UPDATE program_session
SET mode_of_operation = 'online'  -- or 'offline', or whatever default makes sense
WHERE mode_of_operation IS NULL;

ALTER TABLE program_session
ALTER COLUMN mode_of_operation SET NOT NULL;


ALTER TABLE ONLY public.lookup_data ALTER COLUMN id SET DEFAULT nextval('public.lookup_data_id_seq'::regclass);



ALTER TABLE ONLY public.program_message ALTER COLUMN id SET DEFAULT nextval('public.program_message_id_seq'::regclass);



ALTER TABLE ONLY public.program_registration_failures ALTER COLUMN id SET DEFAULT nextval('public.program_registration_failures_id_seq'::regclass);



ALTER TABLE ONLY public.user_role ALTER COLUMN id SET DEFAULT nextval('public.user_role_id_seq'::regclass);



ALTER TABLE ONLY public.user_role_map ALTER COLUMN id SET DEFAULT nextval('public.user_role_map_id_seq'::regclass);


ALTER TABLE ONLY public.user_session ALTER COLUMN id SET DEFAULT nextval('public.user_session_id_seq'::regclass);



ALTER TABLE ONLY public.hdb_form_section
    ADD CONSTRAINT hdb_form_section_pkey PRIMARY KEY (id);


ALTER TABLE ONLY public.hdb_option_category
    ADD CONSTRAINT hdb_option_category_pkey PRIMARY KEY (id);


ALTER TABLE ONLY public.hdb_option
    ADD CONSTRAINT hdb_option_pkey PRIMARY KEY (id);


ALTER TABLE ONLY public.hdb_preference
    ADD CONSTRAINT hdb_preference_pkey PRIMARY KEY (id);


ALTER TABLE ONLY public.hdb_program_question
    ADD CONSTRAINT hdb_program_question_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.hdb_program_registration
    ADD CONSTRAINT hdb_program_registration_pkey PRIMARY KEY (id);


ALTER TABLE ONLY public.hdb_program_registration_rm_rating
    ADD CONSTRAINT hdb_program_registration_rm_rating_pkey PRIMARY KEY (id);


ALTER TABLE ONLY public.hdb_program_registration_rm_rating_type
    ADD CONSTRAINT hdb_program_registration_rm_rating_type_pkey PRIMARY KEY (id);


ALTER TABLE ONLY public.hdb_program_registration_rm_rating_type
    ADD CONSTRAINT hdb_program_registration_rm_rating_type_rating_key_key UNIQUE (rating_key);


ALTER TABLE ONLY public.hdb_program_registration_swap
    ADD CONSTRAINT hdb_program_registration_swap_pkey PRIMARY KEY (id);


ALTER TABLE ONLY public.hdb_question_option_map
    ADD CONSTRAINT hdb_question_option_map_pkey PRIMARY KEY (id);


ALTER TABLE ONLY public.hdb_question
    ADD CONSTRAINT hdb_question_pkey PRIMARY KEY (id);


ALTER TABLE ONLY public.hdb_registration_approval
    ADD CONSTRAINT hdb_registration_approval_pkey PRIMARY KEY (id);


ALTER TABLE ONLY public.hdb_registration_approval_track
    ADD CONSTRAINT hdb_registration_approval_track_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.hdb_registration_custom_response
    ADD CONSTRAINT hdb_registration_custom_response_pkey PRIMARY KEY (id);


ALTER TABLE ONLY public.hdb_registration_invoice_detail
    ADD CONSTRAINT hdb_registration_invoice_detail_pkey PRIMARY KEY (id);


ALTER TABLE ONLY public.hdb_registration_payment_detail
    ADD CONSTRAINT hdb_registration_payment_detail_pkey PRIMARY KEY (id);


ALTER TABLE ONLY public.hdb_registration_payment_details_history
    ADD CONSTRAINT hdb_registration_payment_details_history_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.hdb_registration_question_answer
    ADD CONSTRAINT hdb_registration_question_answer_pkey PRIMARY KEY (id);


ALTER TABLE ONLY public.hdb_registration_travel_info
    ADD CONSTRAINT hdb_registration_travel_info_pkey PRIMARY KEY (id);


ALTER TABLE ONLY public.hdb_registration_travel_plan
    ADD CONSTRAINT hdb_registration_travel_plan_pkey PRIMARY KEY (id);


ALTER TABLE ONLY public.hdb_swap_requested_program
    ADD CONSTRAINT hdb_swap_requested_program_pkey PRIMARY KEY (id);


ALTER TABLE ONLY public.lookup_data
    ADD CONSTRAINT lookup_data_lookup_category_lookup_key_key UNIQUE (lookup_category, lookup_key);


ALTER TABLE ONLY public.lookup_data
    ADD CONSTRAINT lookup_data_pkey PRIMARY KEY (id);


ALTER TABLE ONLY public.program_message
    ADD CONSTRAINT program_message_pkey PRIMARY KEY (id);


ALTER TABLE ONLY public.program_registration_failures
    ADD CONSTRAINT program_registration_failures_pkey PRIMARY KEY (id);


ALTER TABLE ONLY public.hdb_registration_invoice_detail
    ADD CONSTRAINT unique_hdb_invoice_sequence UNIQUE (invoice_sequence_number);


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


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


ALTER TABLE ONLY public.hdb_program_registration
    ADD CONSTRAINT unique_hdb_registration_seq UNIQUE (program_session_id, program_registration_seq_number);


ALTER TABLE ONLY public.hdb_program_registration
    ADD CONSTRAINT unique_hdb_waitlist_seq UNIQUE (program_session_id, waiting_list_seq_number);

ALTER TABLE ONLY public.user_role_map
    ADD CONSTRAINT user_role_map_pkey PRIMARY KEY (id);


ALTER TABLE ONLY public.user_role
    ADD CONSTRAINT user_role_name_key UNIQUE (name);

ALTER TABLE ONLY public.user_role
    ADD CONSTRAINT user_role_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.user_session
    ADD CONSTRAINT user_session_pkey PRIMARY KEY (id);

CREATE INDEX idx_allocated_program_id ON public.hdb_program_registration USING btree (allocated_program_id);


--
-- Name: idx_allocated_session_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_allocated_session_id ON public.hdb_program_registration USING btree (allocated_session_id);


--
-- Name: idx_hdb_approval_approved_by; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_hdb_approval_approved_by ON public.hdb_registration_approval USING btree (approved_by);


--
-- Name: idx_hdb_approval_date; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_hdb_approval_date ON public.hdb_registration_approval USING btree (approval_date);


--
-- Name: idx_hdb_approval_registration; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_hdb_approval_registration ON public.hdb_registration_approval USING btree (registration_id);


--
-- Name: idx_hdb_approval_status; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_hdb_approval_status ON public.hdb_registration_approval USING btree (approval_status);


--
-- Name: idx_hdb_cancelled_by; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_hdb_cancelled_by ON public.hdb_program_registration USING btree (cancelled_by);


--
-- Name: idx_hdb_created_by; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_hdb_created_by ON public.hdb_program_registration USING btree (created_by);


--
-- Name: idx_hdb_custom_response_question; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_hdb_custom_response_question ON public.hdb_registration_custom_response USING btree (question_id);


--
-- Name: idx_hdb_custom_response_registration; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_hdb_custom_response_registration ON public.hdb_registration_custom_response USING btree (registration_id);


--
-- Name: idx_hdb_invoice_issued_date; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_hdb_invoice_issued_date ON public.hdb_registration_invoice_detail USING btree (invoice_issued_date);


--
-- Name: idx_hdb_invoice_razorpay_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_hdb_invoice_razorpay_id ON public.hdb_registration_invoice_detail USING btree (razorpay_id);


--
-- Name: idx_hdb_invoice_registration; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_hdb_invoice_registration ON public.hdb_registration_invoice_detail USING btree (registration_id);


--
-- Name: idx_hdb_invoice_sequence; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_hdb_invoice_sequence ON public.hdb_registration_invoice_detail USING btree (invoice_sequence_number);

CREATE INDEX idx_hdb_invoice_type ON public.hdb_registration_invoice_detail USING btree (invoice_type);


CREATE INDEX idx_hdb_payment_date ON public.hdb_registration_payment_detail USING btree (payment_date);


CREATE INDEX idx_hdb_payment_mode ON public.hdb_registration_payment_detail USING btree (payment_mode);


CREATE INDEX idx_hdb_payment_program_session ON public.hdb_registration_payment_detail USING btree (program_session_id);

CREATE INDEX idx_hdb_payment_razorpay_id ON public.hdb_registration_payment_detail USING btree (razorpay_id);

CREATE INDEX idx_hdb_payment_registration ON public.hdb_registration_payment_detail USING btree (registration_id);

CREATE INDEX idx_hdb_program_session ON public.hdb_program_registration USING btree (program_session_id);

CREATE INDEX idx_hdb_registration_date ON public.hdb_program_registration USING btree (registration_date);

CREATE INDEX idx_hdb_rm_contact ON public.hdb_program_registration USING btree (rm_contact);

CREATE INDEX idx_hdb_travel_info_id_type ON public.hdb_registration_travel_info USING btree (id_type);

CREATE INDEX idx_hdb_travel_info_registration ON public.hdb_registration_travel_info USING btree (registration_id);

CREATE INDEX idx_hdb_travel_plan_arrival ON public.hdb_registration_travel_plan USING btree (arrival_datetime);


CREATE INDEX idx_hdb_travel_plan_registration ON public.hdb_registration_travel_plan USING btree (registration_id);


CREATE INDEX idx_hdb_travel_plan_type ON public.hdb_registration_travel_plan USING btree (travel_type);


CREATE INDEX idx_hdb_updated_by ON public.hdb_program_registration USING btree (updated_by);

CREATE INDEX idx_hdb_user ON public.hdb_program_registration USING btree (user_id);


CREATE INDEX idx_lookup_category_key ON public.lookup_data USING btree (lookup_category, lookup_key);


CREATE INDEX idx_program_session_created_by ON public.program_session USING btree (created_by);

CREATE INDEX idx_program_session_program_id ON public.program_session USING btree (program_id);


CREATE INDEX idx_program_session_updated_by ON public.program_session USING btree (updated_by);


CREATE INDEX idx_program_type_v1_created_by ON public.program_type_v1 USING btree (created_by);


CREATE INDEX idx_program_type_v1_updated_by ON public.program_type_v1 USING btree (updated_by);


CREATE INDEX idx_program_v1_active ON public.program_v1 USING btree (is_active, deleted_at) WHERE ((is_active = true) AND (deleted_at IS NULL));


CREATE INDEX idx_program_v1_created_by ON public.program_v1 USING btree (created_by);


CREATE INDEX idx_program_v1_group_id ON public.program_v1 USING btree (group_id);


CREATE INDEX idx_program_v1_group_primary ON public.program_v1 USING btree (group_id, is_primary_program) WHERE (group_id IS NOT NULL);

CREATE INDEX idx_program_v1_primary_program_id ON public.program_v1 USING btree (primary_program_id);

CREATE INDEX idx_program_v1_primary_programs ON public.program_v1 USING btree (is_primary_program) WHERE (is_primary_program = true);


CREATE INDEX idx_program_v1_updated_by ON public.program_v1 USING btree (updated_by);


CREATE INDEX idx_user_role_map_user_id ON public.user_role_map USING btree (user_id) WITH (deduplicate_items='true');



CREATE INDEX idx_user_session_email ON public.user_session USING btree (email) WHERE (email IS NOT NULL);


CREATE UNIQUE INDEX idx_user_session_jwt_token ON public.user_session USING btree (jwt_token) WHERE (jwt_token IS NOT NULL);

CREATE INDEX idx_user_session_phone_number ON public.user_session USING btree (country_code, phone_number) WHERE ((phone_number IS NOT NULL) AND (country_code IS NOT NULL));

CREATE INDEX idx_user_session_user_id ON public.user_session USING btree (user_id) WHERE (user_id IS NOT NULL);


ALTER TABLE ONLY public.hdb_program_registration
    ADD CONSTRAINT fk_allocated_program FOREIGN KEY (allocated_program_id) REFERENCES public.program_v1(id) ON DELETE SET NULL;


ALTER TABLE ONLY public.hdb_program_registration_swap
    ADD CONSTRAINT fk_allocated_program FOREIGN KEY (allocated_program_id) REFERENCES public.program_v1(id);

ALTER TABLE ONLY public.hdb_program_registration
    ADD CONSTRAINT fk_allocated_session FOREIGN KEY (allocated_session_id) REFERENCES public.program_session(id) ON DELETE SET NULL;


ALTER TABLE ONLY public.hdb_option
    ADD CONSTRAINT fk_category FOREIGN KEY (category_id) REFERENCES public.hdb_option_category(id) ON UPDATE CASCADE ON DELETE CASCADE;


ALTER TABLE ONLY public.hdb_question
    ADD CONSTRAINT fk_created_by FOREIGN KEY (created_by) REFERENCES public.users(id) ON UPDATE CASCADE ON DELETE SET NULL;



ALTER TABLE ONLY public.hdb_option_category
    ADD CONSTRAINT fk_created_by FOREIGN KEY (created_by) REFERENCES public.users(id) ON UPDATE CASCADE ON DELETE SET NULL;


ALTER TABLE ONLY public.hdb_option
    ADD CONSTRAINT fk_created_by FOREIGN KEY (created_by) REFERENCES public.users(id) ON UPDATE CASCADE ON DELETE SET NULL;



ALTER TABLE ONLY public.hdb_question_option_map
    ADD CONSTRAINT fk_created_by FOREIGN KEY (created_by) REFERENCES public.users(id) ON UPDATE CASCADE ON DELETE SET NULL;



ALTER TABLE ONLY public.hdb_form_section
    ADD CONSTRAINT fk_created_by FOREIGN KEY (created_by) REFERENCES public.users(id) ON UPDATE CASCADE ON DELETE SET NULL;

ALTER TABLE ONLY public.hdb_program_question
    ADD CONSTRAINT fk_created_by FOREIGN KEY (created_by) REFERENCES public.users(id) ON DELETE SET NULL;


ALTER TABLE ONLY public.program_registration_failures
    ADD CONSTRAINT fk_created_by FOREIGN KEY (created_by) REFERENCES public.users(id) ON DELETE SET NULL;


ALTER TABLE ONLY public.hdb_program_registration_swap
    ADD CONSTRAINT fk_current_program FOREIGN KEY (current_program_id) REFERENCES public.program_v1(id);


ALTER TABLE ONLY public.hdb_question
    ADD CONSTRAINT fk_form_section FOREIGN KEY (form_section_id) REFERENCES public.hdb_form_section(id) ON UPDATE CASCADE ON DELETE SET NULL;


ALTER TABLE ONLY public.hdb_registration_approval
    ADD CONSTRAINT fk_hdb_approval_approved_by FOREIGN KEY (approved_by) REFERENCES public.users(id) ON DELETE SET NULL;



ALTER TABLE ONLY public.hdb_registration_approval
    ADD CONSTRAINT fk_hdb_approval_registration FOREIGN KEY (registration_id) REFERENCES public.hdb_program_registration(id) ON DELETE CASCADE;


ALTER TABLE ONLY public.hdb_program_registration
    ADD CONSTRAINT fk_hdb_cancelled_by FOREIGN KEY (cancelled_by) REFERENCES public.users(id) ON DELETE SET NULL;


ALTER TABLE ONLY public.hdb_communication_track
    ADD CONSTRAINT fk_hdb_communication_track FOREIGN KEY (registration_id) REFERENCES public.hdb_program_registration(id) ON DELETE CASCADE;



ALTER TABLE ONLY public.hdb_communication_track
    ADD CONSTRAINT fk_hdb_communication_track_created_by FOREIGN KEY (created_by) REFERENCES public.users(id) ON DELETE SET NULL;


ALTER TABLE ONLY public.hdb_communication_track
    ADD CONSTRAINT fk_hdb_communication_track_updated_by FOREIGN KEY (updated_by) REFERENCES public.users(id) ON DELETE SET NULL;



ALTER TABLE ONLY public.hdb_program_registration
    ADD CONSTRAINT fk_hdb_created_by FOREIGN KEY (created_by) REFERENCES public.users(id) ON DELETE SET NULL;


ALTER TABLE ONLY public.hdb_registration_custom_response
    ADD CONSTRAINT fk_hdb_custom_response_question FOREIGN KEY (question_id) REFERENCES public.hdb_question(id) ON DELETE CASCADE;


ALTER TABLE ONLY public.hdb_registration_custom_response
    ADD CONSTRAINT fk_hdb_custom_response_registration FOREIGN KEY (registration_id) REFERENCES public.hdb_program_registration(id) ON DELETE CASCADE;


ALTER TABLE ONLY public.hdb_registration_invoice_detail
    ADD CONSTRAINT fk_hdb_invoice_registration FOREIGN KEY (registration_id) REFERENCES public.hdb_program_registration(id) ON DELETE CASCADE;


ALTER TABLE ONLY public.hdb_registration_payment_detail
    ADD CONSTRAINT fk_hdb_payment_registration FOREIGN KEY (registration_id) REFERENCES public.hdb_program_registration(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.hdb_registration_question_answer
    ADD CONSTRAINT fk_hdb_question_answer_question FOREIGN KEY (question_id) REFERENCES public.hdb_question(id) ON DELETE CASCADE;


ALTER TABLE ONLY public.hdb_registration_question_answer
    ADD CONSTRAINT fk_hdb_question_answer_registration FOREIGN KEY (registration_id) REFERENCES public.hdb_program_registration(id) ON DELETE CASCADE;


ALTER TABLE ONLY public.hdb_program_registration
    ADD CONSTRAINT fk_hdb_rm_contact FOREIGN KEY (rm_contact) REFERENCES public.users(id) ON DELETE SET NULL;

ALTER TABLE ONLY public.hdb_registration_travel_info
    ADD CONSTRAINT fk_hdb_travel_info_registration FOREIGN KEY (registration_id) REFERENCES public.hdb_program_registration(id) ON DELETE CASCADE;


ALTER TABLE ONLY public.hdb_registration_travel_plan
    ADD CONSTRAINT fk_hdb_travel_plan_registration FOREIGN KEY (registration_id) REFERENCES public.hdb_program_registration(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.hdb_program_registration
    ADD CONSTRAINT fk_hdb_updated_by FOREIGN KEY (updated_by) REFERENCES public.users(id) ON DELETE SET NULL;

ALTER TABLE ONLY public.hdb_program_registration
    ADD CONSTRAINT fk_hdb_user FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE;


ALTER TABLE ONLY public.hdb_question_option_map
    ADD CONSTRAINT fk_option FOREIGN KEY (option_id) REFERENCES public.hdb_option(id) ON UPDATE CASCADE ON DELETE CASCADE;


ALTER TABLE ONLY public.hdb_preference
    ADD CONSTRAINT fk_preference_created_by FOREIGN KEY (created_by) REFERENCES public.users(id) ON DELETE SET NULL;


ALTER TABLE ONLY public.hdb_preference
    ADD CONSTRAINT fk_preference_preferred_program FOREIGN KEY (preferred_program_id) REFERENCES public.program_v1(id) ON DELETE CASCADE;


ALTER TABLE ONLY public.hdb_preference
    ADD CONSTRAINT fk_preference_preferred_session FOREIGN KEY (preferred_session_id) REFERENCES public.program_session(id) ON DELETE CASCADE;


ALTER TABLE ONLY public.hdb_preference
    ADD CONSTRAINT fk_preference_registration FOREIGN KEY (registration_id) REFERENCES public.hdb_program_registration(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.hdb_preference
    ADD CONSTRAINT fk_preference_updated_by FOREIGN KEY (updated_by) REFERENCES public.users(id) ON DELETE SET NULL;

ALTER TABLE ONLY public.hdb_program_question
    ADD CONSTRAINT fk_program_question_form_section FOREIGN KEY (form_section_id) REFERENCES public.hdb_form_section(id) ON UPDATE CASCADE ON DELETE SET NULL;


ALTER TABLE ONLY public.program_session
    ADD CONSTRAINT fk_program_session_created_by FOREIGN KEY (created_by) REFERENCES public.users(id) ON DELETE SET NULL;

ALTER TABLE ONLY public.program_session
    ADD CONSTRAINT fk_program_session_program FOREIGN KEY (program_id) REFERENCES public.program_v1(id) ON DELETE SET NULL;


ALTER TABLE ONLY public.program_session
    ADD CONSTRAINT fk_program_session_updated_by FOREIGN KEY (updated_by) REFERENCES public.users(id) ON DELETE SET NULL;

ALTER TABLE ONLY public.program_type_v1
    ADD CONSTRAINT fk_program_type_v1_created_by FOREIGN KEY (created_by) REFERENCES public.users(id) ON DELETE SET NULL;

ALTER TABLE ONLY public.program_type_v1
    ADD CONSTRAINT fk_program_type_v1_updated_by FOREIGN KEY (updated_by) REFERENCES public.users(id) ON DELETE SET NULL;

ALTER TABLE ONLY public.program_v1
    ADD CONSTRAINT fk_program_v1_created_by FOREIGN KEY (created_by) REFERENCES public.users(id) ON DELETE SET NULL;


ALTER TABLE ONLY public.program_v1
    ADD CONSTRAINT fk_program_v1_primary_program FOREIGN KEY (primary_program_id) REFERENCES public.program_v1(id) ON DELETE SET NULL;

ALTER TABLE ONLY public.program_v1
    ADD CONSTRAINT fk_program_v1_updated_by FOREIGN KEY (updated_by) REFERENCES public.users(id) ON DELETE SET NULL;

ALTER TABLE ONLY public.program_v1
    ADD CONSTRAINT fk_program_venue_address FOREIGN KEY (venue_address_id) REFERENCES public.hdb_address(address_id);

ALTER TABLE ONLY public.hdb_program_question
    ADD CONSTRAINT fk_question FOREIGN KEY (question_id) REFERENCES public.hdb_question(id) ON UPDATE CASCADE ON DELETE CASCADE;


ALTER TABLE ONLY public.hdb_question_option_map
    ADD CONSTRAINT fk_question FOREIGN KEY (question_id) REFERENCES public.hdb_question(id) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY public.program_session
    ADD CONSTRAINT fk_session_venue_address FOREIGN KEY (venue_address_id) REFERENCES public.hdb_address(address_id);


ALTER TABLE ONLY public.hdb_question
    ADD CONSTRAINT fk_updated_by FOREIGN KEY (updated_by) REFERENCES public.users(id) ON UPDATE CASCADE ON DELETE SET NULL;


ALTER TABLE ONLY public.hdb_option_category
    ADD CONSTRAINT fk_updated_by FOREIGN KEY (updated_by) REFERENCES public.users(id) ON UPDATE CASCADE ON DELETE SET NULL;

ALTER TABLE ONLY public.hdb_option
    ADD CONSTRAINT fk_updated_by FOREIGN KEY (updated_by) REFERENCES public.users(id) ON UPDATE CASCADE ON DELETE SET NULL;

ALTER TABLE ONLY public.hdb_question_option_map
    ADD CONSTRAINT fk_updated_by FOREIGN KEY (updated_by) REFERENCES public.users(id) ON UPDATE CASCADE ON DELETE SET NULL;


ALTER TABLE ONLY public.hdb_form_section
    ADD CONSTRAINT fk_updated_by FOREIGN KEY (updated_by) REFERENCES public.users(id) ON UPDATE CASCADE ON DELETE SET NULL;

ALTER TABLE ONLY public.hdb_program_question
    ADD CONSTRAINT fk_updated_by FOREIGN KEY (updated_by) REFERENCES public.users(id) ON DELETE SET NULL;


ALTER TABLE ONLY public.program_registration_failures
    ADD CONSTRAINT fk_updated_by FOREIGN KEY (updated_by) REFERENCES public.users(id) ON DELETE SET NULL;

ALTER TABLE ONLY public.user_session
    ADD CONSTRAINT fk_user_session_user FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.hdb_program_registration_rm_rating
    ADD CONSTRAINT hdb_program_registration_rm_rating_created_by_fkey FOREIGN KEY (created_by) REFERENCES public.users(id);

ALTER TABLE ONLY public.hdb_program_registration_rm_rating
    ADD CONSTRAINT hdb_program_registration_rm_rating_program_registration_id_fkey FOREIGN KEY (program_registration_id) REFERENCES public.hdb_program_registration(id);

ALTER TABLE ONLY public.hdb_program_registration_rm_rating
    ADD CONSTRAINT hdb_program_registration_rm_rating_rating_key_fkey FOREIGN KEY (rating_key) REFERENCES public.hdb_program_registration_rm_rating_type(rating_key);

ALTER TABLE ONLY public.hdb_program_registration_rm_rating
    ADD CONSTRAINT hdb_program_registration_rm_rating_rm_id_fkey FOREIGN KEY (rm_id) REFERENCES public.users(id);

ALTER TABLE ONLY public.hdb_program_registration_rm_rating_type
    ADD CONSTRAINT hdb_program_registration_rm_rating_type_created_by_fkey FOREIGN KEY (created_by) REFERENCES public.users(id);

ALTER TABLE ONLY public.hdb_program_registration_rm_rating_type
    ADD CONSTRAINT hdb_program_registration_rm_rating_type_updated_by_fkey FOREIGN KEY (updated_by) REFERENCES public.users(id);


ALTER TABLE ONLY public.hdb_program_registration_rm_rating
    ADD CONSTRAINT hdb_program_registration_rm_rating_updated_by_fkey FOREIGN KEY (updated_by) REFERENCES public.users(id);


ALTER TABLE ONLY public.hdb_program_registration_swap
    ADD CONSTRAINT hdb_program_registration_swap_created_by_fkey FOREIGN KEY (created_by) REFERENCES public.users(id);


ALTER TABLE ONLY public.hdb_program_registration_swap
    ADD CONSTRAINT hdb_program_registration_swap_updated_by_fkey FOREIGN KEY (updated_by) REFERENCES public.users(id);


ALTER TABLE ONLY public.hdb_registration_approval_track
    ADD CONSTRAINT hdb_registration_approval_track_approval_id_fkey FOREIGN KEY (approval_id) REFERENCES public.hdb_registration_approval(id);


ALTER TABLE ONLY public.hdb_registration_approval_track
    ADD CONSTRAINT hdb_registration_approval_track_approved_by_fkey FOREIGN KEY (approved_by) REFERENCES public.users(id);

ALTER TABLE ONLY public.hdb_registration_approval_track
    ADD CONSTRAINT hdb_registration_approval_track_created_by_fkey FOREIGN KEY (created_by) REFERENCES public.users(id) ON DELETE SET NULL;


ALTER TABLE ONLY public.hdb_registration_approval_track
    ADD CONSTRAINT hdb_registration_approval_track_registration_id_fkey FOREIGN KEY (registration_id) REFERENCES public.hdb_program_registration(id);

ALTER TABLE ONLY public.hdb_registration_approval_track
    ADD CONSTRAINT hdb_registration_approval_track_updated_by_fkey FOREIGN KEY (updated_by) REFERENCES public.users(id) ON DELETE SET NULL;


ALTER TABLE ONLY public.hdb_registration_payment_details_history
    ADD CONSTRAINT hdb_registration_payment_details_histor_payment_details_id_fkey FOREIGN KEY (payment_details_id) REFERENCES public.hdb_registration_payment_detail(id);


ALTER TABLE ONLY public.hdb_swap_requested_program
    ADD CONSTRAINT hdb_swap_requested_program_created_by_fkey FOREIGN KEY (created_by) REFERENCES public.users(id);


ALTER TABLE ONLY public.hdb_swap_requested_program
    ADD CONSTRAINT hdb_swap_requested_program_program_id_fkey FOREIGN KEY (program_id) REFERENCES public.program_v1(id);


ALTER TABLE ONLY public.hdb_swap_requested_program
    ADD CONSTRAINT hdb_swap_requested_program_swap_request_id_fkey FOREIGN KEY (swap_request_id) REFERENCES public.hdb_program_registration_swap(id);


ALTER TABLE ONLY public.hdb_swap_requested_program
    ADD CONSTRAINT hdb_swap_requested_program_updated_by_fkey FOREIGN KEY (updated_by) REFERENCES public.users(id);

ALTER TABLE ONLY public.program_message
    ADD CONSTRAINT program_message_cc_role_id_fkey FOREIGN KEY (cc_role_id) REFERENCES public.user_role(id);



ALTER TABLE ONLY public.program_message
    ADD CONSTRAINT program_message_program_id_fkey FOREIGN KEY (program_id) REFERENCES public.program_v1(id);



ALTER TABLE ONLY public.program_message
    ADD CONSTRAINT program_message_receiver_id_fkey FOREIGN KEY (receiver_id) REFERENCES public.users(id);


ALTER TABLE ONLY public.program_message
    ADD CONSTRAINT program_message_seeker_id_fkey FOREIGN KEY (seeker_id) REFERENCES public.users(id);


ALTER TABLE ONLY public.program_message
    ADD CONSTRAINT program_message_sender_id_fkey FOREIGN KEY (sender_id) REFERENCES public.users(id);

ALTER TABLE ONLY public.user_role_map
    ADD CONSTRAINT user_role_map_role_id_fkey FOREIGN KEY (role_id) REFERENCES public.user_role(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.user_role_map
    ADD CONSTRAINT user_role_map_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE;


ALTER TABLE public.program_type_v1
ALTER COLUMN program_type DROP NOT NULL;


ALTER TABLE public.program_v1
ALTER COLUMN program DROP NOT NULL;

ALTER TABLE public.program_type
ADD COLUMN deleted_at timestamp without time zone;



-- 1. Expand length of firebase_id_ext
ALTER TABLE public.users
ALTER COLUMN firebase_id_ext TYPE character varying(255);

-- 2. Expand length of alternate_country_code
ALTER TABLE public.users
ALTER COLUMN alternate_country_code TYPE character varying(255);

-- 3. Expand length of alternate_phone_number
ALTER TABLE public.users
ALTER COLUMN alternate_phone_number TYPE character varying(255);

-- 4. Expand length of alternate_email
ALTER TABLE public.users
ALTER COLUMN alternate_email TYPE character varying(255);


CREATE SEQUENCE hdb_program_registration_rm_rating_type_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE hdb_program_registration_rm_rating_type
    ALTER COLUMN id SET DEFAULT nextval('hdb_program_registration_rm_rating_type_id_seq');

ALTER SEQUENCE public.hdb_program_registration_rm_rating_type_id_seq OWNED BY public.hdb_program_registration_rm_rating_type.id;

ALTER TABLE ONLY public.program_v1
    ADD CONSTRAINT fk_program_v1_type FOREIGN KEY (program_type_id) REFERENCES public.program_type_v1(id) ON DELETE SET NULL;  
