-- Migration: POS Sessions Management Enhancement
-- Date: 2025-11-15
-- Purpose: Add supervisor verification, cash reconciliation, and approval workflow fields
--          to support enhanced POS Sessions Management module
-- Dependencies: Requires pos_sessions table to exist
-- Related: docs/POS_Sessions_Enhancement_Design.md

-- ========================================
-- PART 1: Add Verification and Reconciliation Fields
-- ========================================

-- Verification tracking
ALTER TABLE pos_sessions 
ADD COLUMN IF NOT EXISTS verified BOOLEAN DEFAULT FALSE;

ALTER TABLE pos_sessions 
ADD COLUMN IF NOT EXISTS verified_by UUID;

ALTER TABLE pos_sessions 
ADD COLUMN IF NOT EXISTS verified_at TIMESTAMP;

-- Add foreign key constraint for verified_by
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint 
        WHERE conname = 'fk_pos_sessions_verified_by'
    ) THEN
        ALTER TABLE pos_sessions 
        ADD CONSTRAINT fk_pos_sessions_verified_by 
        FOREIGN KEY (verified_by) REFERENCES users(user_id);
    END IF;
END $$;

-- Cash reconciliation fields
ALTER TABLE pos_sessions 
ADD COLUMN IF NOT EXISTS supervisor_counted_cash NUMERIC(10,2);

ALTER TABLE pos_sessions 
ADD COLUMN IF NOT EXISTS adjusted_variance NUMERIC(10,2);

ALTER TABLE pos_sessions 
ADD COLUMN IF NOT EXISTS variance_explanation TEXT;

ALTER TABLE pos_sessions 
ADD COLUMN IF NOT EXISTS verification_notes TEXT;

-- Approval workflow
ALTER TABLE pos_sessions 
ADD COLUMN IF NOT EXISTS approval_status VARCHAR(50) DEFAULT 'pending';

-- Add check constraint for approval_status
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint 
        WHERE conname = 'chk_pos_sessions_approval_status'
    ) THEN
        ALTER TABLE pos_sessions 
        ADD CONSTRAINT chk_pos_sessions_approval_status 
        CHECK (approval_status IN ('pending', 'approved', 'discrepancy', 'adjusted'));
    END IF;
END $$;

-- Financial summary fields
ALTER TABLE pos_sessions 
ADD COLUMN IF NOT EXISTS total_sales NUMERIC(10,2);

ALTER TABLE pos_sessions 
ADD COLUMN IF NOT EXISTS cash_sales NUMERIC(10,2);

-- ========================================
-- PART 2: Create Indexes for Performance
-- ========================================

-- Index for filtering by verification status
CREATE INDEX IF NOT EXISTS idx_pos_sessions_verified 
ON pos_sessions(verified) WHERE NOT isdeleted;

-- Index for filtering by verified_by (supervisor queries)
CREATE INDEX IF NOT EXISTS idx_pos_sessions_verified_by 
ON pos_sessions(verified_by) WHERE verified_by IS NOT NULL;

-- Index for filtering by approval_status
CREATE INDEX IF NOT EXISTS idx_pos_sessions_approval_status 
ON pos_sessions(approval_status) WHERE NOT isdeleted;

-- Composite index for cashier + date range queries (cashier view)
CREATE INDEX IF NOT EXISTS idx_pos_sessions_cashier_date 
ON pos_sessions(cashier_id, session_start DESC) WHERE NOT isdeleted;

-- Composite index for status + verification queries
CREATE INDEX IF NOT EXISTS idx_pos_sessions_status_verified 
ON pos_sessions(status, verified, session_start DESC) WHERE NOT isdeleted;

-- ========================================
-- PART 3: Create Enhanced View
-- ========================================

-- Drop existing view if it exists
DROP VIEW IF EXISTS pos_sessions_enhanced;

-- Create enhanced view with verification status and computed fields
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,
    
    -- 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,
    
    -- Variance level classification
    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,
    
    -- Display variance (use adjusted if available, otherwise original)
    COALESCE(s.adjusted_variance, s.variance) as display_variance,
    
    -- Transaction count (from sales_receipts)
    (SELECT COUNT(*) 
     FROM sales_receipts sr 
     WHERE sr.session_id = s.session_id 
       AND NOT sr.isdeleted
    ) as transaction_count,
    
    -- Total transaction amount
    (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,
    
    -- Cash payment totals
    (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,
    
    -- Session duration in minutes
    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
WHERE NOT s.isdeleted;

-- Add comment to the view
COMMENT ON VIEW pos_sessions_enhanced IS 
'Enhanced view of POS sessions with verification status, computed variance levels, and aggregated transaction data. Used by POS Sessions Management module.';

-- ========================================
-- PART 4: Create Payment Breakdown Function
-- ========================================

-- Function to get payment method breakdown for a session
CREATE OR REPLACE FUNCTION get_session_payment_breakdown(p_session_id UUID)
RETURNS TABLE(
    payment_method VARCHAR,
    transaction_count BIGINT,
    total_amount NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        payment->>'method' as payment_method,
        COUNT(DISTINCT sr.sales_receipt_id) as transaction_count,
        SUM((payment->>'amount')::numeric) as total_amount
    FROM sales_receipts sr
    INNER JOIN lineitems_payments lp ON sr.sales_receipt_id = lp.sales_receipt_id
    CROSS JOIN LATERAL jsonb_array_elements(lp.payments) AS payment
    WHERE sr.session_id = p_session_id
      AND NOT sr.isdeleted
      AND sr.transactiontype != 'refund'
      AND jsonb_typeof(lp.payments) = 'array'
    GROUP BY payment->>'method'
    ORDER BY SUM((payment->>'amount')::numeric) DESC;
END;
$$ LANGUAGE plpgsql;

-- Add comment to the function
COMMENT ON FUNCTION get_session_payment_breakdown(UUID) IS 
'Returns payment method breakdown for a given session including transaction counts and totals. Used by session verification modal.';

-- ========================================
-- PART 5: Create Audit Trigger for Verification
-- ========================================

-- Create audit log table for session verifications
CREATE TABLE IF NOT EXISTS pos_session_verification_log (
    log_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    session_id UUID NOT NULL REFERENCES pos_sessions(session_id),
    verified_by UUID REFERENCES users(user_id),
    verified_at TIMESTAMP NOT NULL DEFAULT NOW(),
    action VARCHAR(50) NOT NULL, -- 'verified', 'adjusted', 'rejected'
    previous_variance NUMERIC(10,2),
    new_variance NUMERIC(10,2),
    variance_explanation TEXT,
    verification_notes TEXT,
    approval_status VARCHAR(50),
    org_id UUID,
    created TIMESTAMP DEFAULT NOW(),
    CONSTRAINT chk_verification_action CHECK (action IN ('verified', 'adjusted', 'rejected', 'reopened'))
);

-- Create index for audit log queries
CREATE INDEX IF NOT EXISTS idx_verification_log_session 
ON pos_session_verification_log(session_id, verified_at DESC);

CREATE INDEX IF NOT EXISTS idx_verification_log_verifier 
ON pos_session_verification_log(verified_by, verified_at DESC);

-- Add comment
COMMENT ON TABLE pos_session_verification_log IS 
'Audit log for all POS session verification actions. Tracks who verified sessions, when, and what changes were made.';

-- Create trigger function to log verification changes
CREATE OR REPLACE FUNCTION log_session_verification()
RETURNS TRIGGER AS $$
BEGIN
    -- Only log if verification fields changed
    IF (NEW.verified IS DISTINCT FROM OLD.verified) OR
       (NEW.verified_by IS DISTINCT FROM OLD.verified_by) OR
       (NEW.adjusted_variance IS DISTINCT FROM OLD.adjusted_variance) OR
       (NEW.approval_status IS DISTINCT FROM OLD.approval_status) THEN
        
        INSERT INTO pos_session_verification_log (
            session_id,
            verified_by,
            verified_at,
            action,
            previous_variance,
            new_variance,
            variance_explanation,
            verification_notes,
            approval_status,
            org_id
        ) VALUES (
            NEW.session_id,
            NEW.verified_by,
            NEW.verified_at,
            CASE 
                WHEN NEW.verified AND NOT COALESCE(OLD.verified, FALSE) THEN 'verified'
                WHEN NEW.adjusted_variance IS DISTINCT FROM OLD.adjusted_variance THEN 'adjusted'
                WHEN NOT NEW.verified AND COALESCE(OLD.verified, FALSE) THEN 'reopened'
                ELSE 'verified'
            END,
            OLD.variance,
            NEW.adjusted_variance,
            NEW.variance_explanation,
            NEW.verification_notes,
            NEW.approval_status,
            NEW.org_id
        );
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger
DROP TRIGGER IF EXISTS trg_log_session_verification ON pos_sessions;
CREATE TRIGGER trg_log_session_verification
    AFTER UPDATE ON pos_sessions
    FOR EACH ROW
    EXECUTE FUNCTION log_session_verification();

-- ========================================
-- PART 6: Update Existing Data (Safe Defaults)
-- ========================================

-- Set default approval_status for existing closed sessions
UPDATE pos_sessions 
SET approval_status = 'pending'
WHERE status IN ('closed', 'abandoned')
  AND approval_status IS NULL;

-- Calculate total_sales and cash_sales for existing sessions
UPDATE pos_sessions s
SET 
    total_sales = COALESCE((
        SELECT SUM(sr.totalamount)
        FROM sales_receipts sr
        WHERE sr.session_id = s.session_id
          AND NOT sr.isdeleted
          AND sr.transactiontype != 'refund'
    ), 0),
    cash_sales = COALESCE((
        SELECT 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
        )
        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'
    ), 0)
WHERE s.total_sales IS NULL OR s.cash_sales IS NULL;

-- ========================================
-- PART 7: Grant Permissions
-- ========================================

-- Grant SELECT on enhanced view to relevant roles
GRANT SELECT ON pos_sessions_enhanced TO PUBLIC;

-- Grant EXECUTE on payment breakdown function
GRANT EXECUTE ON FUNCTION get_session_payment_breakdown(UUID) TO PUBLIC;

-- Grant SELECT on audit log to supervisors/managers (adjust role names as needed)
GRANT SELECT ON pos_session_verification_log TO PUBLIC;

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

-- Log migration completion
DO $$
BEGIN
    RAISE NOTICE 'Migration 20251115_pos_sessions_management_enhancement completed successfully';
    RAISE NOTICE 'Added fields: verified, verified_by, verified_at, supervisor_counted_cash, adjusted_variance, variance_explanation, verification_notes, approval_status, total_sales, cash_sales';
    RAISE NOTICE 'Created view: pos_sessions_enhanced';
    RAISE NOTICE 'Created function: get_session_payment_breakdown(UUID)';
    RAISE NOTICE 'Created table: pos_session_verification_log';
    RAISE NOTICE 'Created trigger: trg_log_session_verification';
END $$;
