-- Migration: POS Session Merge Support
-- Date: 2025-11-16
-- Purpose: enable managers to combine overlapping/unclosed POS sessions by
--          introducing merge metadata, audit logging, and updated enhanced view

-- ========================================
-- PART 1: Add merge tracking fields to pos_sessions
-- ========================================

ALTER TABLE pos_sessions 
    ADD COLUMN IF NOT EXISTS merge_group_id UUID,
    ADD COLUMN IF NOT EXISTS merged_into_session_id UUID,
    ADD COLUMN IF NOT EXISTS merged_by UUID,
    ADD COLUMN IF NOT EXISTS merged_at TIMESTAMP,
    ADD COLUMN IF NOT EXISTS merge_notes TEXT;

-- Add foreign key constraints where applicable
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint 
        WHERE conname = 'fk_pos_sessions_merged_by'
    ) THEN
        ALTER TABLE pos_sessions 
        ADD CONSTRAINT fk_pos_sessions_merged_by 
        FOREIGN KEY (merged_by) REFERENCES users(user_id);
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint 
        WHERE conname = 'fk_pos_sessions_merged_into'
    ) THEN
        ALTER TABLE pos_sessions 
        ADD CONSTRAINT fk_pos_sessions_merged_into 
        FOREIGN KEY (merged_into_session_id) REFERENCES pos_sessions(session_id);
    END IF;
END $$;

CREATE INDEX IF NOT EXISTS idx_pos_sessions_merge_group 
    ON pos_sessions(merge_group_id) WHERE merge_group_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_pos_sessions_merged_into 
    ON pos_sessions(merged_into_session_id) WHERE merged_into_session_id IS NOT NULL;

-- ========================================
-- PART 2: Merge audit log table
-- ========================================

CREATE TABLE IF NOT EXISTS pos_session_merge_log (
    merge_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    merge_group_id UUID NOT NULL,
    target_session_id UUID NOT NULL REFERENCES pos_sessions(session_id),
    source_session_id UUID NOT NULL REFERENCES pos_sessions(session_id),
    merged_by UUID REFERENCES users(user_id),
    merged_at TIMESTAMP NOT NULL DEFAULT NOW(),
    note TEXT,
    transfer_summary JSONB,
    org_id UUID,
    created TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_pos_session_merge_target 
    ON pos_session_merge_log(target_session_id, merged_at DESC);

CREATE INDEX IF NOT EXISTS idx_pos_session_merge_group 
    ON pos_session_merge_log(merge_group_id);

COMMENT ON TABLE pos_session_merge_log IS 
    'Tracks every POS session merge action, including source/target sessions, actor, and transfer summary.';

-- ========================================
-- PART 3: Refresh enhanced view with merge metadata
-- ========================================

DROP VIEW IF EXISTS pos_sessions_enhanced;

CREATE OR REPLACE VIEW pos_sessions_enhanced AS
SELECT 
    s.session_id,
    s.cashier_id,
    s.cashier_name,
    s.pos_number,
    s.session_start,
    s.session_end,
    s.status,
    s.opening_float,
    s.closing_cash_expected,
    s.closing_cash_declared,
    s.variance,
    s.org_id,
    s.isdeleted,
    s.updatedat,
    
    -- Verification fields
    s.verified,
    s.verified_by,
    s.verified_at,
    u.full_name AS verified_by_name,
    
    -- Reconciliation fields
    s.supervisor_counted_cash,
    s.adjusted_variance,
    s.variance_explanation,
    s.verification_notes,
    s.approval_status,
    
    -- Financial fields
    s.total_sales,
    s.cash_sales,
    
    -- Merge fields
    s.merge_group_id,
    s.merged_into_session_id,
    s.merged_by,
    u_merge.full_name AS merged_by_name,
    s.merged_at,
    s.merge_notes,
    
    -- Computed fields
    CASE 
        WHEN s.verified THEN 'verified'
        WHEN s.status = 'closed' AND NOT s.verified THEN 'unverified'
        WHEN s.status = 'active' THEN 'active'
        WHEN s.status = 'abandoned' THEN 'abandoned'
        ELSE 'unknown'
    END AS verification_status,
    
    CASE
        WHEN s.merged_into_session_id IS NOT NULL THEN 'merged-child'
        WHEN s.merge_group_id IS NOT NULL THEN 'merged-parent'
        ELSE 'standalone'
    END AS merge_status,
    
    (
        SELECT COUNT(*) 
        FROM pos_sessions ps_child
        WHERE ps_child.merged_into_session_id = s.session_id
          AND NOT ps_child.isdeleted
    ) AS merged_session_count,
    
    CASE
        WHEN s.adjusted_variance IS NOT NULL THEN
            CASE
                WHEN ABS(s.adjusted_variance) <= 1.00 THEN 'acceptable'
                WHEN ABS(s.adjusted_variance) <= 10.00 THEN 'review'
                ELSE 'critical'
            END
        WHEN s.variance IS NOT NULL THEN
            CASE
                WHEN ABS(s.variance) <= 1.00 THEN 'acceptable'
                WHEN ABS(s.variance) <= 10.00 THEN 'review'
                ELSE 'critical'
            END
        ELSE 'pending'
    END AS variance_level,
    
    COALESCE(s.adjusted_variance, s.variance) AS display_variance,
    
    (
        SELECT COUNT(*) 
        FROM sales_receipts sr 
        WHERE sr.session_id = s.session_id 
          AND NOT sr.isdeleted
    ) AS transaction_count,
    
    (
        SELECT COALESCE(SUM(sr.totalamount), 0) 
        FROM sales_receipts sr 
        WHERE sr.session_id = s.session_id 
          AND NOT sr.isdeleted
          AND sr.transactiontype != 'refund'
    ) AS calculated_total_sales,
    
    (
        SELECT COALESCE(SUM(
            CASE 
                WHEN jsonb_typeof(lp.payments) = 'array' THEN
                    (
                        SELECT SUM((payment->>'amount')::numeric)
                        FROM jsonb_array_elements(lp.payments) AS payment
                        WHERE payment->>'method' = 'Cash'
                    )
                ELSE 0
            END
        ), 0)
        FROM lineitems_payments lp
        INNER JOIN sales_receipts sr ON lp.sales_receipt_id = sr.sales_receipt_id
        WHERE sr.session_id = s.session_id
          AND NOT sr.isdeleted
          AND sr.transactiontype != 'refund'
    ) AS calculated_cash_sales,
    
    CASE 
        WHEN s.session_end IS NOT NULL THEN
            EXTRACT(EPOCH FROM (s.session_end - s.session_start)) / 60
        ELSE
            EXTRACT(EPOCH FROM (NOW() - s.session_start)) / 60
    END AS duration_minutes

FROM pos_sessions s
LEFT JOIN users u ON s.verified_by = u.user_id
LEFT JOIN users u_merge ON s.merged_by = u_merge.user_id
WHERE NOT s.isdeleted;

COMMENT ON VIEW pos_sessions_enhanced IS 
'Enhanced view of POS sessions with verification, merge metadata, computed variance levels, and aggregated transaction data. Used by POS Sessions Management module.';

GRANT SELECT ON pos_sessions_enhanced TO PUBLIC;

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

DO $$
BEGIN
    RAISE NOTICE 'Migration 20251116_pos_session_merge_support completed';
END $$;
