-- ================================================
-- 1. Create workflow_variable_master table
-- ================================================
-- NOTE: This is the LEGACY schema. For production, use workflow-stage-tracking-schema.sql which has ENUMs.
-- This file is kept for reference and migration purposes only.
CREATE TABLE IF NOT EXISTS workflow_variable_master (
    id SERIAL PRIMARY KEY,
    variable_key VARCHAR(100) UNIQUE NOT NULL,
    variable_name VARCHAR(255) NOT NULL,
    source_table VARCHAR(100) NOT NULL,
    source_column VARCHAR(100) NOT NULL,
    alias_name VARCHAR(100),
    data_type workflow_data_type_enum NOT NULL, -- 'string', 'number', 'boolean', 'date', 'json'
    join_type workflow_join_type_enum, -- 'INNER', 'LEFT', 'RIGHT', 'FULL'
    join_condition TEXT, -- e.g., 'rpd.registration_id = pr.id AND rpd.deleted_at IS NULL'
    table_alias VARCHAR(50), -- e.g., 'rpd' for registration_payment_detail
    description TEXT,
    is_active BOOLEAN DEFAULT true,
    default_value TEXT, -- Default SQL expression if applicable
    is_mandatory_join BOOLEAN DEFAULT false,
    execution_order INT, -- Order in which joins should be executed
    variable_scope workflow_variable_scope_enum DEFAULT 'DEFAULT', -- 'DEFAULT' or 'SPECIFIC'
    metadata JSONB,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMPTZ,
    created_by INT,
    updated_by INT,
    CONSTRAINT fk_wvm_created_by FOREIGN KEY (created_by) REFERENCES users(id),
    CONSTRAINT fk_wvm_updated_by FOREIGN KEY (updated_by) REFERENCES users(id)
);

CREATE INDEX idx_workflow_variable_master_key ON workflow_variable_master(variable_key);
CREATE INDEX idx_workflow_variable_master_source_table ON workflow_variable_master(source_table);

-- ================================================
-- 2. Create workflow_configuration_variable table
-- ================================================
CREATE TABLE IF NOT EXISTS workflow_configuration_variable (
    id SERIAL PRIMARY KEY,
    workflow_config_id INT NOT NULL,
    variable_master_id INT NOT NULL,
    is_required BOOLEAN DEFAULT false,
    variable_order INT,
    override_alias VARCHAR(100),
    override_default_value TEXT,
    metadata JSONB,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMPTZ,
    created_by INT,
    updated_by INT,
    CONSTRAINT fk_wcv_workflow_config FOREIGN KEY (workflow_config_id) 
        REFERENCES workflow_configuration(id) ON DELETE CASCADE,
    CONSTRAINT fk_wcv_variable_master FOREIGN KEY (variable_master_id) 
        REFERENCES workflow_variable_master(id) ON DELETE CASCADE,
    CONSTRAINT fk_wcv_created_by FOREIGN KEY (created_by) REFERENCES users(id),
    CONSTRAINT fk_wcv_updated_by FOREIGN KEY (updated_by) REFERENCES users(id)
);

CREATE INDEX idx_wf_config_variable_workflow ON workflow_configuration_variable(workflow_config_id);
CREATE INDEX idx_wf_config_variable_variable ON workflow_configuration_variable(variable_master_id);

-- ================================================
-- 3. Populate workflow_variable_master with common variables
-- ================================================

-- Base Registration Fields
INSERT INTO workflow_variable_master 
(variable_key, variable_name, source_table, source_column, alias_name, data_type, execution_order, description, is_active)
VALUES
('REGISTRATION_ID', 'Registration ID', 'hdb_program_registration', 'id', 'registrationId', 'number', 1, 'Primary registration identifier', true),
('USER_ID', 'User ID', 'hdb_program_registration', 'user_id', 'userId', 'number', 1, 'User identifier', true),
('PROGRAM_ID', 'Program ID', 'hdb_program_registration', 'program_id', 'programId', 'number', 1, 'Program identifier', true),
('REGISTRATION_STATUS', 'Registration Status', 'hdb_program_registration', 'registration_status', 'registrationStatus', 'string', 1, 'Current registration status', true),
('BASIC_DETAILS_STATUS', 'Basic Details Status', 'hdb_program_registration', 'basic_details_status', 'basicDetailsFilled', 'string', 1, 'Status of basic details form', true),
('PRO_FORMA_INVOICE_PDF_URL', 'Pro Forma Invoice URL', 'hdb_program_registration', 'pro_forma_invoice_pdf_url', 'proFormaInvoicePdfUrl', 'string', 1, 'URL to pro forma invoice PDF', true),
('IS_FREE_SEAT', 'Is Free Seat', 'hdb_program_registration', 'is_free_seat', NULL, 'boolean', 1, 'Whether registration is a free seat', true),
('WAITLIST_POSITION', 'Waitlist Position', 'hdb_program_registration', 'waiting_list_seq_number', 'waitlistPosition', 'number', 1, 'Position in waitlist', true);

-- IS_WAITLISTED with default value using CASE expression
INSERT INTO workflow_variable_master 
(variable_key, variable_name, source_table, source_column, alias_name, data_type, default_value, execution_order, description, is_active)
VALUES
('IS_WAITLISTED', 'Is Waitlisted', 'hdb_program_registration', 'registration_status', 'isWaitlisted', 'boolean', 
 'CASE WHEN pr.registration_status = ''waitlisted'' THEN true ELSE false END', 1, 'Whether registration is waitlisted', true);

INSERT INTO workflow_variable_master 
(variable_key, variable_name, source_table, source_column, alias_name, data_type, join_type, join_condition, table_alias, execution_order, description, is_active)
VALUES
('PAYMENT_STATUS', 'Payment Status', 'hdb_registration_payment_detail', 'payment_status', 'paymentStatus', 'string', 
 'LEFT', 'rpd.registration_id = pr.id AND rpd.deleted_at IS NULL', 'rpd', 10, 'Current payment status', true),
('PAYMENT_MODE', 'Payment Mode', 'hdb_registration_payment_detail', 'payment_mode', 'paymentMode', 'string', 
 'LEFT', 'rpd.registration_id = pr.id AND rpd.deleted_at IS NULL', 'rpd', 10, 'Mode of payment', true);

-- Edit Request Status (subquery to hdb_payment_change_request table)
-- Note: editRequests is NOT a column - it's a separate table (hdb_payment_change_request)
INSERT INTO workflow_variable_master 
(variable_key, variable_name, source_table, source_column, alias_name, data_type, default_value, execution_order, description, is_active, metadata)
VALUES
('EDIT_REQUEST_STATUS', 'Edit Request Status', 'hdb_program_registration', 'id', 'editRequestStatus', 'string',
 '(SELECT pcr.request_status 
   FROM hdb_payment_change_request pcr 
   INNER JOIN hdb_registration_payment_detail rpd ON pcr.payment_id = rpd.id 
   WHERE rpd.registration_id = pr.id 
     AND rpd.deleted_at IS NULL 
     AND pcr.created_at IS NOT NULL
   ORDER BY pcr.updated_at DESC 
   LIMIT 1)',
 11, 
 'Status of the latest payment edit request from hdb_payment_change_request table', 
 true,
 '{"query_type": "subquery", "source_entity": "PaymentEditRequest", "relationship": "payment_detail -> edit_requests"}');

-- Travel Info Fields (with JOIN)
INSERT INTO workflow_variable_master 
(variable_key, variable_name, source_table, source_column, alias_name, data_type, join_type, join_condition, table_alias, execution_order, description, is_active)
VALUES
('TRAVEL_INFO_STATUS', 'Travel Info Status', 'hdb_registration_travel_info', 'travel_info_status', 'travelInfoStatus', 'string', 
 'LEFT', 'rti.registration_id = pr.id AND rti.deleted_at IS NULL', 'rti', 20, 'Status of travel information', true),
('TRAVEL_PLAN_STATUS', 'Travel Plan Status', 'hdb_registration_travel_plan', 'travel_plan_status', 'travelPlanStatus', 'string', 
 'LEFT', 'rtp.registration_id = pr.id AND rtp.deleted_at IS NULL', 'rtp', 30, 'Status of travel plan', true);

-- Approval Fields (with JOIN)
INSERT INTO workflow_variable_master 
(variable_key, variable_name, source_table, source_column, alias_name, data_type, join_type, join_condition, table_alias, execution_order, description, is_active)
VALUES
('APPROVAL_STATUS', 'Approval Status', 'hdb_registration_approval', 'approval_status', 'approvalStatus', 'string', 
 'LEFT', 'ra.registration_id = pr.id AND ra.deleted_at IS NULL', 'ra', 5, 'Registration approval status', true);

-- Goodies Fields (with CASE expression)
INSERT INTO workflow_variable_master 
(variable_key, variable_name, source_table, source_column, alias_name, data_type, join_type, join_condition, table_alias, default_value, execution_order, description, is_active)
VALUES
('GOODIES_STATUS', 'Goodies Status', 'hdb_program_registration_goodies', 'id', 'goodiesStatus', 'string', 
 'LEFT', 'rg.registration_id = pr.id AND rg.deleted_at IS NULL', 'rg', 
 'CASE WHEN rg.id IS NOT NULL THEN ''completed'' ELSE ''pending'' END', 40, 'Status of goodies selection', true);

-- Invoice Fields (with CASE expression)
INSERT INTO workflow_variable_master 
(variable_key, variable_name, source_table, source_column, alias_name, data_type, join_type, join_condition, table_alias, default_value, execution_order, description, is_active)
VALUES
('INVOICE_GENERATED', 'Invoice Generated', 'hdb_registration_invoice_detail', 'id', 'invoiceGenerated', 'boolean', 
 'LEFT', 'rid.registration_id = pr.id AND rid.deleted_at IS NULL', 'rid', 
 'CASE WHEN rid.id IS NOT NULL AND rid.invoice_status = ''invoice_completed'' THEN true ELSE false END', 50, 'Whether invoice has been generated', true);

-- User Fields (with JOIN)
INSERT INTO workflow_variable_master 
(variable_key, variable_name, source_table, source_column, alias_name, data_type, join_type, join_condition, table_alias, default_value, execution_order, description, is_active)
VALUES
('IS_DEFAULTER', 'Is Defaulter', 'users', 'hdb_defaulter', 'isDefaulter', 'boolean', 
 'LEFT', 'u.id = pr.user_id', 'u', 'COALESCE(u.hdb_defaulter, false)', 60, 'Whether user is a defaulter', true);

-- Swap Fields (with CASE expression)
INSERT INTO workflow_variable_master 
(variable_key, variable_name, source_table, source_column, alias_name, data_type, join_type, join_condition, table_alias, default_value, execution_order, description, is_active)
VALUES
('HAS_ACTIVE_SWAP', 'Has Active Swap', 'hdb_program_registration_swap', 'id', 'hasActiveSwap', 'boolean', 
 'LEFT', 'ps.program_registration_id = pr.id AND ps.status = ''active''', 'ps', 
 'CASE WHEN ps.id IS NOT NULL THEN true ELSE false END', 70, 'Whether registration has active swap request', true),
('SWAP_STATUS', 'Swap Status', 'hdb_program_registration_swap', 'status', 'swapStatus', 'string', 
 'LEFT', 'ps.program_registration_id = pr.id AND ps.status = ''active''', 'ps', NULL, 70, 'Status of swap request', true),
('SWAP_TYPE', 'Swap Type', 'hdb_program_registration_swap', 'type', 'swapType', 'string', 
 'LEFT', 'ps.program_registration_id = pr.id AND ps.status = ''active''', 'ps', NULL, 70, 'Type of swap request', true),
('SWAP_REQUIREMENT', 'Swap Requirement', 'hdb_program_registration_swap', 'swap_requirement', 'swapRequirement', 'string', 
 'LEFT', 'ps.program_registration_id = pr.id AND ps.status = ''active''', 'ps', NULL, 70, 'Swap requirement type (SWAP_REQUEST or SWAP_DEMAND)', true);

-- Free Seat Field (with COALESCE)
INSERT INTO workflow_variable_master 
(variable_key, variable_name, source_table, source_column, alias_name, data_type, default_value, execution_order, description, is_active)
VALUES
('IS_FREE_SEAT_COMPUTED', 'Is Free Seat (Computed)', 'hdb_program_registration', 'is_free_seat', 'isFreeSeat', 'boolean', 
 'COALESCE(pr.is_free_seat, false)', 1, 'Whether registration is a free seat (with default)', true);

-- Invoice Detail Fields (from hdb_registration_invoice_detail)
INSERT INTO workflow_variable_master 
(variable_key, variable_name, source_table, source_column, alias_name, data_type, join_type, join_condition, table_alias, execution_order, description, is_active)
VALUES
('INVOICE_PDF_URL', 'Invoice PDF URL', 'hdb_registration_invoice_detail', 'invoice_pdf_url', 'invoicePdfUrl', 'string', 
 'LEFT', 'rid.registration_id = pr.id AND rid.deleted_at IS NULL', 'rid', 50, 'URL to generated invoice PDF', true),
('INVOICE_STATUS', 'Invoice Status', 'hdb_registration_invoice_detail', 'invoice_status', 'invoiceStatus', 'string', 
 'LEFT', 'rid.registration_id = pr.id AND rid.deleted_at IS NULL', 'rid', 50, 'Status of invoice generation', true),
('INVOICE_SEQUENCE_NUMBER', 'Invoice Sequence Number', 'hdb_registration_invoice_detail', 'invoice_sequence_number', 'invoiceSequenceNumber', 'string', 
 'LEFT', 'rid.registration_id = pr.id AND rid.deleted_at IS NULL', 'rid', 50, 'Invoice sequence/reference number', true);

-- Cancellation Fields (from hdb_program_registration)
INSERT INTO workflow_variable_master 
(variable_key, variable_name, source_table, source_column, alias_name, data_type, execution_order, description, is_active)
VALUES
('CANCELLATION_DATE', 'Cancellation Date', 'hdb_program_registration', 'cancellation_date', 'cancellationDate', 'date', 1, 'Date when registration was cancelled', true),
('CANCELLATION_REASON', 'Cancellation Reason', 'hdb_program_registration', 'cancellation_reason', 'cancellationReason', 'string', 1, 'Reason for cancellation', true),
('CANCELLATION_COMMENTS', 'Cancellation Comments', 'hdb_program_registration', 'cancellation_comments', 'cancellationComments', 'string', 1, 'Additional cancellation comments', true);

-- Additional Registration Fields
INSERT INTO workflow_variable_master 
(variable_key, variable_name, source_table, source_column, alias_name, data_type, execution_order, description, is_active)
VALUES
('INVOICE_CONFIRMATION', 'Invoice Confirmation', 'hdb_program_registration', 'invoice_confirmation', 'invoiceConfirmation', 'boolean', 1, 'Whether invoice details have been confirmed', true),
('REGISTRATION_NUMBER', 'Registration Number', 'hdb_program_registration', 'registration_seq_number', 'registrationNumber', 'string', 1, 'Registration sequence number', true),
('SEEKER_NAME', 'Seeker Name', 'hdb_program_registration', 'full_name', 'seekerName', 'string', 1, 'Full name of the seeker', true),
('SEEKER_EMAIL', 'Seeker Email', 'hdb_program_registration', 'email_address', 'seekerEmail', 'string', 1, 'Email address of the seeker', true),
('SEEKER_PHONE', 'Seeker Phone', 'hdb_program_registration', 'mobile_number', 'seekerPhone', 'string', 1, 'Mobile number of the seeker', true);

-- Payment Detail Fields
INSERT INTO workflow_variable_master 
(variable_key, variable_name, source_table, source_column, alias_name, data_type, join_type, join_condition, table_alias, execution_order, description, is_active)
VALUES
('AMOUNT_PAID', 'Amount Paid', 'hdb_registration_payment_detail', 'sub_total', 'amountPaid', 'number', 
 'LEFT', 'rpd.registration_id = pr.id AND rpd.deleted_at IS NULL', 'rpd', 10, 'Total amount paid', true);

INSERT INTO workflow_variable_master 
(variable_key, variable_name, source_table, source_column, alias_name, data_type, join_type, join_condition, table_alias, default_value, execution_order, description, is_active)
VALUES
('OFFLINE_PAYMENT_METHOD', 'Offline Payment Method', 'hdb_registration_payment_detail', 'offline_meta', 'offlinePaymentMethod', 'string', 
 'LEFT', 'rpd.registration_id = pr.id AND rpd.deleted_at IS NULL', 'rpd', 
 'COALESCE(rpd.offline_meta::jsonb->>''paymentMode'', null)', 10, 'Offline payment method extracted from JSON metadata', true);

-- Edit Request Status (JSONB array extraction)

-- Program Fields (with JOIN to program_v1)
INSERT INTO workflow_variable_master 
(variable_key, variable_name, source_table, source_column, alias_name, data_type, join_type, join_condition, table_alias, execution_order, description, is_active)
VALUES
('PROGRAM_NAME', 'Program Name', 'program_v1', 'name', 'programName', 'string', 
 'LEFT', 'p.id = pr.program_id AND p.deleted_at IS NULL', 'p', 80, 'Name of the program', true),
('PROGRAM_VENUE', 'Program Venue', 'program_v1', 'venue', 'programVenue', 'string', 
 'LEFT', 'p.id = pr.program_id AND p.deleted_at IS NULL', 'p', 80, 'Venue of the program', true),
('PROGRAM_FEE', 'Program Fee', 'program_v1', 'base_price', 'programFee', 'number', 
 'LEFT', 'p.id = pr.program_id AND p.deleted_at IS NULL', 'p', 80, 'Base price/fee of the program', true);

-- Program Date Fields (with formatted dates)
INSERT INTO workflow_variable_master 
(variable_key, variable_name, source_table, source_column, alias_name, data_type, join_type, join_condition, table_alias, default_value, execution_order, description, is_active)
VALUES
('PROGRAM_START_DATE', 'Program Start Date', 'program_v1', 'starts_at', 'programStartDate', 'string', 
 'LEFT', 'p.id = pr.program_id AND p.deleted_at IS NULL', 'p', 
 'TO_CHAR(p.starts_at, ''DD Mon YYYY'')', 80, 'Formatted program start date', true),
('PROGRAM_END_DATE', 'Program End Date', 'program_v1', 'ends_at', 'programEndDate', 'string', 
 'LEFT', 'p.id = pr.program_id AND p.deleted_at IS NULL', 'p', 
 'TO_CHAR(p.ends_at, ''DD Mon YYYY'')', 80, 'Formatted program end date', true);

-- ================================================
-- 4. Link variables to existing HDB/MSD workflow
-- ================================================
-- Note: Adjust workflow_configuration_id based on your actual workflow IDs
-- You can find the workflow ID by running: SELECT id, workflow_key FROM workflow_configuration;

DO $$
DECLARE
    v_workflow_id INT;
    v_var_id INT;
    v_order INT := 1;
BEGIN
    -- Get HDB/MSD workflow ID
    SELECT id INTO v_workflow_id FROM workflow_configuration WHERE workflow_key = 'WF_HDBMSD_V6' LIMIT 1;
    
    IF v_workflow_id IS NOT NULL THEN
        -- Link all relevant variables for HDB/MSD workflow
        FOR v_var_id IN 
            SELECT id FROM workflow_variable_master 
            WHERE variable_key IN (
                'REGISTRATION_ID', 'USER_ID', 'PROGRAM_ID', 'REGISTRATION_STATUS',
                'BASIC_DETAILS_STATUS', 'APPROVAL_STATUS', 'PAYMENT_STATUS', 'PAYMENT_MODE',
                'TRAVEL_INFO_STATUS', 'TRAVEL_PLAN_STATUS', 'PRO_FORMA_INVOICE_PDF_URL',
                'IS_FREE_SEAT_COMPUTED', 'GOODIES_STATUS', 'HAS_ACTIVE_SWAP', 'SWAP_STATUS',
                'SWAP_TYPE', 'SWAP_REQUIREMENT', 'IS_DEFAULTER', 'INVOICE_GENERATED',
                'INVOICE_PDF_URL', 'INVOICE_STATUS', 'INVOICE_CONFIRMATION',
                'REGISTRATION_NUMBER', 'AMOUNT_PAID', 'OFFLINE_PAYMENT_METHOD',
                'SEEKER_NAME', 'SEEKER_EMAIL', 'SEEKER_PHONE',
                'PROGRAM_NAME', 'PROGRAM_START_DATE', 'PROGRAM_END_DATE', 'PROGRAM_VENUE', 'PROGRAM_FEE',
                'EDIT_REQUEST_STATUS'
            )
            ORDER BY execution_order, variable_key
        LOOP
            INSERT INTO workflow_configuration_variable 
            (workflow_config_id, variable_master_id, variable_order, is_required)
            VALUES (v_workflow_id, v_var_id, v_order, false);
            v_order := v_order + 1;
        END LOOP;
        
        RAISE NOTICE 'Linked variables to HDB/MSD workflow (ID: %)', v_workflow_id;
    ELSE
        RAISE NOTICE 'HDB/MSD workflow not found, skipping variable linking';
    END IF;
END $$;

-- ================================================
-- 6. Add comments for documentation
-- ================================================
COMMENT ON TABLE workflow_variable_master IS 
'Global catalog of reusable workflow variables. Defines fields that can be fetched for workflow validation and processing.';

COMMENT ON TABLE workflow_configuration_variable IS 
'Junction table linking workflow configurations to variables from the master catalog. Allows each workflow to use a subset of available variables.';

COMMENT ON COLUMN workflow_variable_master.variable_key IS 
'Unique identifier for the variable (e.g., PAYMENT_STATUS, TRAVEL_INFO_STATUS). Should be UPPERCASE.';

COMMENT ON COLUMN workflow_variable_master.join_condition IS 
'SQL JOIN condition for this table. Use table_alias in the condition (e.g., "rpd.registration_id = pr.id")';

COMMENT ON COLUMN workflow_variable_master.default_value IS 
'SQL expression for default value or CASE statement (e.g., "COALESCE(pr.is_free_seat, false)")';

COMMENT ON COLUMN workflow_variable_master.execution_order IS 
'Order in which joins should be executed. Lower numbers execute first.';

COMMENT ON COLUMN workflow_variable_master.variable_scope IS 
'Indicates if variable is DEFAULT (global standard) or SPECIFIC (workflow-specific). DEFAULT variables are used as fallback when workflow has no specific variables configured.';

-- ================================================
-- 7. Verification Queries
-- ================================================
-- Run these queries to verify the migration:

-- Check all variables with their types
 SELECT variable_key, variable_name, variable_scope, source_table, is_active 
 FROM workflow_variable_master 
 ORDER BY variable_scope, execution_order, variable_key;

-- Check DEFAULT variables (used as fallback when workflow has no specific variables)
SELECT variable_key, variable_name, source_table, variable_scope
FROM workflow_variable_master 
WHERE variable_scope = 'DEFAULT' AND is_active = true
ORDER BY execution_order, variable_key;

-- Check workflow variable mappings
SELECT wc.workflow_key, vm.variable_key, vm.variable_scope, wcv.variable_order 
FROM workflow_configuration_variable wcv
JOIN workflow_configuration wc ON wcv.workflow_config_id = wc.id
JOIN workflow_variable_master vm ON wcv.variable_master_id = vm.id
ORDER BY wc.workflow_key, wcv.variable_order;

-- ================================================
-- END OF MIGRATION
-- ================================================