-- ============================================================================
-- PHASE 7 TASK 3: CREDIT MANAGEMENT SCHEMA
-- ============================================================================
-- Description: Customer credit limits, utilization tracking, and approval workflows
-- Dependencies: customers, invoices, quotations tables
-- Author: AI Assistant
-- Date: October 18, 2025
-- ============================================================================

-- Enable UUID extension if not already enabled
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ============================================================================
-- TABLE: customer_credit_limits
-- ============================================================================
-- Purpose: Store credit limit configuration per customer
-- ============================================================================

CREATE TABLE IF NOT EXISTS customer_credit_limits (
    credit_limit_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    customer_id UUID NOT NULL,
    org_id UUID NOT NULL,
    
    -- Credit limit configuration
    credit_limit DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    currency VARCHAR(3) DEFAULT 'USD',
    
    -- Credit status
    available_credit DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    utilized_credit DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    overdue_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    
    -- Credit terms
    payment_terms_days INTEGER DEFAULT 30,
    grace_period_days INTEGER DEFAULT 7,
    
    -- Credit hold settings
    is_on_hold BOOLEAN DEFAULT false,
    hold_reason TEXT,
    hold_date TIMESTAMP,
    hold_by UUID,
    
    -- Alert thresholds (percentage of credit limit)
    alert_threshold_80 BOOLEAN DEFAULT true,
    alert_threshold_90 BOOLEAN DEFAULT true,
    alert_threshold_100 BOOLEAN DEFAULT true,
    
    -- Last credit check
    last_credit_check TIMESTAMP,
    last_utilization_update TIMESTAMP,
    
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by UUID,
    updated_by UUID,
    isdeleted BOOLEAN DEFAULT false,
    deleted_at TIMESTAMP,
    deleted_by UUID,
    
    -- Constraints
    CONSTRAINT fk_customer_credit_limits_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    CONSTRAINT fk_customer_credit_limits_org FOREIGN KEY (org_id) REFERENCES orgs(org_id),
    CONSTRAINT chk_credit_limit_positive CHECK (credit_limit >= 0),
    CONSTRAINT chk_utilized_credit_non_negative CHECK (utilized_credit >= 0),
    CONSTRAINT chk_available_credit_non_negative CHECK (available_credit >= 0),
    CONSTRAINT unique_customer_org_credit_limit UNIQUE (customer_id, org_id, isdeleted)
);

-- Indexes for performance
CREATE INDEX idx_customer_credit_limits_customer ON customer_credit_limits(customer_id) WHERE isdeleted = false;
CREATE INDEX idx_customer_credit_limits_org ON customer_credit_limits(org_id) WHERE isdeleted = false;
CREATE INDEX idx_customer_credit_limits_on_hold ON customer_credit_limits(is_on_hold) WHERE isdeleted = false AND is_on_hold = true;
CREATE INDEX idx_customer_credit_limits_utilization ON customer_credit_limits(utilized_credit, credit_limit) WHERE isdeleted = false;

-- Comments
COMMENT ON TABLE customer_credit_limits IS 'Customer credit limit configuration and real-time utilization tracking';
COMMENT ON COLUMN customer_credit_limits.available_credit IS 'Real-time available credit (credit_limit - utilized_credit)';
COMMENT ON COLUMN customer_credit_limits.utilized_credit IS 'Sum of outstanding invoices + pending quotations';
COMMENT ON COLUMN customer_credit_limits.is_on_hold IS 'Automatic hold when overdue or over limit';

-- ============================================================================
-- TABLE: credit_limit_history
-- ============================================================================
-- Purpose: Audit trail for credit limit changes
-- ============================================================================

CREATE TABLE IF NOT EXISTS credit_limit_history (
    history_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    credit_limit_id UUID NOT NULL,
    customer_id UUID NOT NULL,
    org_id UUID NOT NULL,
    
    -- Change details
    change_type VARCHAR(50) NOT NULL, -- 'limit_increase', 'limit_decrease', 'hold_applied', 'hold_removed', 'utilization_update'
    previous_limit DECIMAL(15,2),
    new_limit DECIMAL(15,2),
    previous_hold_status BOOLEAN,
    new_hold_status BOOLEAN,
    
    -- Change reason
    reason TEXT,
    notes TEXT,
    
    -- Approval information
    requested_by UUID,
    approved_by UUID,
    approved_at TIMESTAMP,
    
    -- Change metadata
    metadata JSONB,
    
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by UUID,
    
    -- Constraints
    CONSTRAINT fk_credit_limit_history_credit_limit FOREIGN KEY (credit_limit_id) REFERENCES customer_credit_limits(credit_limit_id),
    CONSTRAINT fk_credit_limit_history_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    CONSTRAINT fk_credit_limit_history_org FOREIGN KEY (org_id) REFERENCES orgs(org_id)
);

-- Indexes
CREATE INDEX idx_credit_limit_history_credit_limit ON credit_limit_history(credit_limit_id);
CREATE INDEX idx_credit_limit_history_customer ON credit_limit_history(customer_id);
CREATE INDEX idx_credit_limit_history_org ON credit_limit_history(org_id);
CREATE INDEX idx_credit_limit_history_created ON credit_limit_history(created_at DESC);
CREATE INDEX idx_credit_limit_history_change_type ON credit_limit_history(change_type);

-- Comments
COMMENT ON TABLE credit_limit_history IS 'Audit trail for all credit limit changes and holds';
COMMENT ON COLUMN credit_limit_history.change_type IS 'Type of change: limit_increase, limit_decrease, hold_applied, hold_removed, utilization_update';

-- ============================================================================
-- TABLE: credit_approval_requests
-- ============================================================================
-- Purpose: Workflow for over-limit quotation approvals
-- ============================================================================

CREATE TABLE IF NOT EXISTS credit_approval_requests (
    request_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    customer_id UUID NOT NULL,
    org_id UUID NOT NULL,
    
    -- Request details
    request_type VARCHAR(50) NOT NULL, -- 'quotation_approval', 'limit_increase', 'hold_override'
    reference_type VARCHAR(50), -- 'quotations', 'invoices', etc.
    reference_id UUID,
    
    -- Credit information
    current_credit_limit DECIMAL(15,2) NOT NULL,
    current_utilized_credit DECIMAL(15,2) NOT NULL,
    requested_amount DECIMAL(15,2) NOT NULL,
    new_total_utilization DECIMAL(15,2) NOT NULL,
    over_limit_amount DECIMAL(15,2) NOT NULL,
    
    -- Request details
    reason TEXT,
    justification TEXT,
    priority VARCHAR(20) DEFAULT 'normal', -- 'low', 'normal', 'high', 'urgent'
    
    -- Approval workflow
    status VARCHAR(50) DEFAULT 'pending', -- 'pending', 'approved', 'rejected', 'cancelled'
    requested_by UUID NOT NULL,
    requested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    approved_by UUID,
    approved_at TIMESTAMP,
    approval_notes TEXT,
    
    rejected_by UUID,
    rejected_at TIMESTAMP,
    rejection_reason TEXT,
    
    -- Expiration
    expires_at TIMESTAMP,
    
    -- Metadata
    metadata JSONB,
    
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Constraints
    CONSTRAINT fk_credit_approval_requests_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    CONSTRAINT fk_credit_approval_requests_org FOREIGN KEY (org_id) REFERENCES orgs(org_id),
    CONSTRAINT chk_requested_amount_positive CHECK (requested_amount > 0),
    CONSTRAINT chk_approval_status CHECK (status IN ('pending', 'approved', 'rejected', 'cancelled'))
);

-- Indexes
CREATE INDEX idx_credit_approval_requests_customer ON credit_approval_requests(customer_id);
CREATE INDEX idx_credit_approval_requests_org ON credit_approval_requests(org_id);
CREATE INDEX idx_credit_approval_requests_status ON credit_approval_requests(status) WHERE status = 'pending';
CREATE INDEX idx_credit_approval_requests_reference ON credit_approval_requests(reference_type, reference_id);
CREATE INDEX idx_credit_approval_requests_requested_by ON credit_approval_requests(requested_by);
CREATE INDEX idx_credit_approval_requests_created ON credit_approval_requests(created_at DESC);

-- Comments
COMMENT ON TABLE credit_approval_requests IS 'Approval workflow for over-limit transactions and credit increases';
COMMENT ON COLUMN credit_approval_requests.request_type IS 'quotation_approval, limit_increase, hold_override';
COMMENT ON COLUMN credit_approval_requests.over_limit_amount IS 'Amount exceeding current credit limit';

-- ============================================================================
-- TABLE: credit_alerts
-- ============================================================================
-- Purpose: Log credit limit alerts and notifications
-- ============================================================================

CREATE TABLE IF NOT EXISTS credit_alerts (
    alert_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    customer_id UUID NOT NULL,
    org_id UUID NOT NULL,
    credit_limit_id UUID NOT NULL,
    
    -- Alert details
    alert_type VARCHAR(50) NOT NULL, -- 'approaching_limit_80', 'approaching_limit_90', 'limit_exceeded', 'hold_applied', 'overdue_payment'
    severity VARCHAR(20) DEFAULT 'info', -- 'info', 'warning', 'error', 'critical'
    
    -- Credit snapshot at alert time
    credit_limit DECIMAL(15,2) NOT NULL,
    utilized_credit DECIMAL(15,2) NOT NULL,
    available_credit DECIMAL(15,2) NOT NULL,
    utilization_percentage DECIMAL(5,2) NOT NULL,
    
    -- Alert message
    message TEXT NOT NULL,
    details JSONB,
    
    -- Notification status
    notification_sent BOOLEAN DEFAULT false,
    notification_sent_at TIMESTAMP,
    notification_method VARCHAR(50), -- 'email', 'sms', 'portal', 'webhook'
    
    -- Alert resolution
    acknowledged BOOLEAN DEFAULT false,
    acknowledged_by UUID,
    acknowledged_at TIMESTAMP,
    resolution_notes TEXT,
    
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Constraints
    CONSTRAINT fk_credit_alerts_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    CONSTRAINT fk_credit_alerts_org FOREIGN KEY (org_id) REFERENCES orgs(org_id),
    CONSTRAINT fk_credit_alerts_credit_limit FOREIGN KEY (credit_limit_id) REFERENCES customer_credit_limits(credit_limit_id)
);

-- Indexes
CREATE INDEX idx_credit_alerts_customer ON credit_alerts(customer_id);
CREATE INDEX idx_credit_alerts_org ON credit_alerts(org_id);
CREATE INDEX idx_credit_alerts_credit_limit ON credit_alerts(credit_limit_id);
CREATE INDEX idx_credit_alerts_type ON credit_alerts(alert_type);
CREATE INDEX idx_credit_alerts_severity ON credit_alerts(severity);
CREATE INDEX idx_credit_alerts_unacknowledged ON credit_alerts(acknowledged) WHERE acknowledged = false;
CREATE INDEX idx_credit_alerts_created ON credit_alerts(created_at DESC);

-- Comments
COMMENT ON TABLE credit_alerts IS 'Log of credit limit alerts and notifications';
COMMENT ON COLUMN credit_alerts.alert_type IS 'Type: approaching_limit_80, approaching_limit_90, limit_exceeded, hold_applied, overdue_payment';

-- ============================================================================
-- VIEW: credit_utilization_summary
-- ============================================================================
-- Purpose: Real-time credit utilization summary with health indicators
-- ============================================================================

CREATE OR REPLACE VIEW credit_utilization_summary AS
SELECT 
    ccl.credit_limit_id,
    ccl.customer_id,
    c.customer_name,
    c.email as customer_email,
    ccl.org_id,
    
    -- Credit limits
    ccl.credit_limit,
    ccl.available_credit,
    ccl.utilized_credit,
    ccl.overdue_amount,
    ccl.currency,
    
    -- Utilization percentage
    CASE 
        WHEN ccl.credit_limit > 0 THEN 
            ROUND((ccl.utilized_credit / ccl.credit_limit * 100)::numeric, 2)
        ELSE 0
    END as utilization_percentage,
    
    -- Health status
    CASE 
        WHEN ccl.is_on_hold THEN 'on_hold'
        WHEN ccl.utilized_credit > ccl.credit_limit THEN 'over_limit'
        WHEN ccl.credit_limit > 0 AND (ccl.utilized_credit / ccl.credit_limit) >= 0.90 THEN 'critical'
        WHEN ccl.credit_limit > 0 AND (ccl.utilized_credit / ccl.credit_limit) >= 0.80 THEN 'warning'
        WHEN ccl.credit_limit > 0 AND (ccl.utilized_credit / ccl.credit_limit) >= 0.70 THEN 'caution'
        ELSE 'healthy'
    END as credit_health,
    
    -- Hold information
    ccl.is_on_hold,
    ccl.hold_reason,
    ccl.hold_date,
    
    -- Outstanding invoices count
    (SELECT COUNT(*) 
     FROM invoices i 
     WHERE i.customer_id = ccl.customer_id 
       AND i.org_id = ccl.org_id
       AND i.status IN ('finalized', 'overdue')
       AND i.outstanding_amount > 0
       AND i.isdeleted = false
    ) as outstanding_invoices_count,
    
    -- Pending quotations count
    (SELECT COUNT(*) 
     FROM quotations q 
     WHERE q.customer_id = ccl.customer_id 
       AND q.org_id = ccl.org_id
       AND q.status IN ('draft', 'sent')
       AND q.isdeleted = false
    ) as pending_quotations_count,
    
    -- Days since last payment
    (SELECT MAX(CURRENT_DATE - p.payment_date::date)
     FROM payments p
     JOIN invoices i ON p.invoice_id = i.invoice_id
     WHERE i.customer_id = ccl.customer_id
       AND i.org_id = ccl.org_id
       AND p.isdeleted = false
    ) as days_since_last_payment,
    
    -- Terms and dates
    ccl.payment_terms_days,
    ccl.grace_period_days,
    ccl.last_credit_check,
    ccl.last_utilization_update,
    
    -- Timestamps
    ccl.created_at,
    ccl.updated_at
    
FROM customer_credit_limits ccl
JOIN customers c ON ccl.customer_id = c.customer_id
WHERE ccl.isdeleted = false
  AND c.isdeleted = false;

-- Comments
COMMENT ON VIEW credit_utilization_summary IS 'Real-time credit utilization with health indicators and statistics';

-- ============================================================================
-- FUNCTION: calculate_customer_credit_utilization
-- ============================================================================
-- Purpose: Calculate and update customer credit utilization
-- ============================================================================

CREATE OR REPLACE FUNCTION calculate_customer_credit_utilization(
    p_customer_id UUID,
    p_org_id UUID
)
RETURNS TABLE (
    utilized_credit DECIMAL(15,2),
    available_credit DECIMAL(15,2),
    overdue_amount DECIMAL(15,2),
    should_hold BOOLEAN
) AS $$
DECLARE
    v_credit_limit DECIMAL(15,2);
    v_outstanding_amount DECIMAL(15,2);
    v_pending_quotations DECIMAL(15,2);
    v_overdue DECIMAL(15,2);
    v_utilized DECIMAL(15,2);
    v_available DECIMAL(15,2);
    v_should_hold BOOLEAN;
BEGIN
    -- Get current credit limit
    SELECT credit_limit INTO v_credit_limit
    FROM customer_credit_limits
    WHERE customer_id = p_customer_id
      AND org_id = p_org_id
      AND isdeleted = false;
    
    -- Default to 0 if no limit found
    v_credit_limit := COALESCE(v_credit_limit, 0);
    
    -- Calculate outstanding invoices
    SELECT COALESCE(SUM(outstanding_amount), 0) INTO v_outstanding_amount
    FROM invoices
    WHERE customer_id = p_customer_id
      AND org_id = p_org_id
      AND status IN ('finalized', 'overdue')
      AND outstanding_amount > 0
      AND isdeleted = false;
    
    -- Calculate pending quotations (not yet converted)
    SELECT COALESCE(SUM(total_amount), 0) INTO v_pending_quotations
    FROM quotations
    WHERE customer_id = p_customer_id
      AND org_id = p_org_id
      AND status IN ('sent')
      AND isdeleted = false;
    
    -- Calculate overdue amount
    SELECT COALESCE(SUM(outstanding_amount), 0) INTO v_overdue
    FROM invoices
    WHERE customer_id = p_customer_id
      AND org_id = p_org_id
      AND status = 'overdue'
      AND outstanding_amount > 0
      AND isdeleted = false;
    
    -- Total utilization (outstanding + pending quotations)
    v_utilized := v_outstanding_amount + v_pending_quotations;
    
    -- Available credit
    v_available := GREATEST(v_credit_limit - v_utilized, 0);
    
    -- Determine if hold should be applied
    v_should_hold := (v_overdue > 0 OR v_utilized > v_credit_limit);
    
    -- Return results
    RETURN QUERY SELECT v_utilized, v_available, v_overdue, v_should_hold;
END;
$$ LANGUAGE plpgsql;

-- Comments
COMMENT ON FUNCTION calculate_customer_credit_utilization IS 'Calculate real-time credit utilization for a customer';

-- ============================================================================
-- FUNCTION: update_credit_utilization
-- ============================================================================
-- Purpose: Update customer_credit_limits table with calculated utilization
-- ============================================================================

CREATE OR REPLACE FUNCTION update_credit_utilization(
    p_customer_id UUID,
    p_org_id UUID,
    p_updated_by UUID DEFAULT NULL
)
RETURNS BOOLEAN AS $$
DECLARE
    v_utilized DECIMAL(15,2);
    v_available DECIMAL(15,2);
    v_overdue DECIMAL(15,2);
    v_should_hold BOOLEAN;
    v_current_hold BOOLEAN;
    v_credit_limit_id UUID;
BEGIN
    -- Calculate utilization
    SELECT * INTO v_utilized, v_available, v_overdue, v_should_hold
    FROM calculate_customer_credit_utilization(p_customer_id, p_org_id);
    
    -- Get current hold status
    SELECT credit_limit_id, is_on_hold INTO v_credit_limit_id, v_current_hold
    FROM customer_credit_limits
    WHERE customer_id = p_customer_id
      AND org_id = p_org_id
      AND isdeleted = false;
    
    -- Update credit limits table
    UPDATE customer_credit_limits
    SET 
        utilized_credit = v_utilized,
        available_credit = v_available,
        overdue_amount = v_overdue,
        last_utilization_update = CURRENT_TIMESTAMP,
        updated_at = CURRENT_TIMESTAMP,
        updated_by = p_updated_by
    WHERE customer_id = p_customer_id
      AND org_id = p_org_id
      AND isdeleted = false;
    
    -- Apply automatic hold if needed and not already on hold
    IF v_should_hold AND NOT v_current_hold THEN
        UPDATE customer_credit_limits
        SET 
            is_on_hold = true,
            hold_reason = CASE 
                WHEN v_overdue > 0 THEN 'Automatic hold: Overdue invoices detected'
                ELSE 'Automatic hold: Credit limit exceeded'
            END,
            hold_date = CURRENT_TIMESTAMP,
            hold_by = p_updated_by
        WHERE customer_id = p_customer_id
          AND org_id = p_org_id
          AND isdeleted = false;
        
        -- Log the hold in history
        INSERT INTO credit_limit_history (
            credit_limit_id, customer_id, org_id,
            change_type, previous_hold_status, new_hold_status,
            reason, created_by
        ) VALUES (
            v_credit_limit_id, p_customer_id, p_org_id,
            'hold_applied', false, true,
            CASE 
                WHEN v_overdue > 0 THEN 'Automatic hold: Overdue invoices detected'
                ELSE 'Automatic hold: Credit limit exceeded'
            END,
            p_updated_by
        );
    END IF;
    
    -- Remove hold if conditions no longer apply
    IF NOT v_should_hold AND v_current_hold THEN
        UPDATE customer_credit_limits
        SET 
            is_on_hold = false,
            hold_reason = NULL,
            hold_date = NULL
        WHERE customer_id = p_customer_id
          AND org_id = p_org_id
          AND isdeleted = false;
        
        -- Log the hold removal in history
        INSERT INTO credit_limit_history (
            credit_limit_id, customer_id, org_id,
            change_type, previous_hold_status, new_hold_status,
            reason, created_by
        ) VALUES (
            v_credit_limit_id, p_customer_id, p_org_id,
            'hold_removed', true, false,
            'Automatic hold removal: Conditions no longer apply',
            p_updated_by
        );
    END IF;
    
    RETURN true;
END;
$$ LANGUAGE plpgsql;

-- Comments
COMMENT ON FUNCTION update_credit_utilization IS 'Update customer credit utilization and apply/remove holds automatically';

-- ============================================================================
-- TRIGGER: update_credit_on_invoice_change
-- ============================================================================
-- Purpose: Automatically update credit utilization when invoices change
-- ============================================================================

CREATE OR REPLACE FUNCTION trigger_update_credit_on_invoice_change()
RETURNS TRIGGER AS $$
BEGIN
    -- Update credit utilization for the affected customer
    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        PERFORM update_credit_utilization(NEW.customer_id, NEW.org_id, NEW.updated_by);
    ELSIF TG_OP = 'DELETE' THEN
        PERFORM update_credit_utilization(OLD.customer_id, OLD.org_id, OLD.updated_by);
    END IF;
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_credit_on_invoice_change
AFTER INSERT OR UPDATE OR DELETE ON invoices
FOR EACH ROW
EXECUTE FUNCTION trigger_update_credit_on_invoice_change();

-- Comments
COMMENT ON FUNCTION trigger_update_credit_on_invoice_change IS 'Trigger to update credit utilization when invoices change';

-- ============================================================================
-- TRIGGER: update_credit_on_quotation_change
-- ============================================================================
-- Purpose: Automatically update credit utilization when quotations change
-- ============================================================================

CREATE OR REPLACE FUNCTION trigger_update_credit_on_quotation_change()
RETURNS TRIGGER AS $$
BEGIN
    -- Update credit utilization for the affected customer
    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        PERFORM update_credit_utilization(NEW.customer_id, NEW.org_id, NEW.updated_by);
    ELSIF TG_OP = 'DELETE' THEN
        PERFORM update_credit_utilization(OLD.customer_id, OLD.org_id, OLD.updated_by);
    END IF;
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_credit_on_quotation_change
AFTER INSERT OR UPDATE OR DELETE ON quotations
FOR EACH ROW
EXECUTE FUNCTION trigger_update_credit_on_quotation_change();

-- Comments
COMMENT ON FUNCTION trigger_update_credit_on_quotation_change IS 'Trigger to update credit utilization when quotations change';

-- ============================================================================
-- TRIGGER: update_customer_credit_limits_timestamp
-- ============================================================================

CREATE OR REPLACE FUNCTION trigger_update_customer_credit_limits_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_customer_credit_limits_timestamp
BEFORE UPDATE ON customer_credit_limits
FOR EACH ROW
EXECUTE FUNCTION trigger_update_customer_credit_limits_timestamp();

-- ============================================================================
-- INITIAL DATA: Default credit limits for existing customers
-- ============================================================================

-- Insert default credit limits for existing customers (if needed)
-- This is commented out by default - run manually if needed
/*
INSERT INTO customer_credit_limits (
    customer_id,
    org_id,
    credit_limit,
    currency,
    available_credit,
    created_by
)
SELECT 
    c.customer_id,
    c.org_id,
    0.00 as credit_limit,
    'USD' as currency,
    0.00 as available_credit,
    NULL as created_by
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM customer_credit_limits ccl
    WHERE ccl.customer_id = c.customer_id
      AND ccl.org_id = c.org_id
      AND ccl.isdeleted = false
)
AND c.isdeleted = false;
*/

-- ============================================================================
-- VERIFICATION QUERIES
-- ============================================================================

-- Verify tables created
SELECT 
    table_name,
    (SELECT COUNT(*) FROM information_schema.columns WHERE table_name = t.table_name) as column_count
FROM information_schema.tables t
WHERE table_schema = 'public'
  AND table_name IN (
    'customer_credit_limits',
    'credit_limit_history',
    'credit_approval_requests',
    'credit_alerts'
  )
ORDER BY table_name;

-- Verify indexes created
SELECT 
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename IN (
    'customer_credit_limits',
    'credit_limit_history',
    'credit_approval_requests',
    'credit_alerts'
  )
ORDER BY tablename, indexname;

-- Verify functions created
SELECT 
    proname as function_name,
    pg_get_function_arguments(oid) as arguments
FROM pg_proc
WHERE proname IN (
    'calculate_customer_credit_utilization',
    'update_credit_utilization',
    'trigger_update_credit_on_invoice_change',
    'trigger_update_credit_on_quotation_change',
    'trigger_update_customer_credit_limits_timestamp'
)
ORDER BY proname;

-- Verify triggers created
SELECT 
    trigger_name,
    event_object_table,
    action_timing,
    event_manipulation
FROM information_schema.triggers
WHERE trigger_schema = 'public'
  AND trigger_name LIKE 'trg_%credit%'
ORDER BY event_object_table, trigger_name;

-- Verify view created
SELECT 
    table_name,
    view_definition
FROM information_schema.views
WHERE table_schema = 'public'
  AND table_name = 'credit_utilization_summary';

-- ============================================================================
-- ROLLBACK (IF NEEDED)
-- ============================================================================
/*
-- Drop triggers
DROP TRIGGER IF EXISTS trg_update_credit_on_invoice_change ON invoices;
DROP TRIGGER IF EXISTS trg_update_credit_on_quotation_change ON quotations;
DROP TRIGGER IF EXISTS trg_update_customer_credit_limits_timestamp ON customer_credit_limits;

-- Drop functions
DROP FUNCTION IF EXISTS trigger_update_credit_on_invoice_change();
DROP FUNCTION IF EXISTS trigger_update_credit_on_quotation_change();
DROP FUNCTION IF EXISTS trigger_update_customer_credit_limits_timestamp();
DROP FUNCTION IF EXISTS update_credit_utilization(UUID, UUID, UUID);
DROP FUNCTION IF EXISTS calculate_customer_credit_utilization(UUID, UUID);

-- Drop view
DROP VIEW IF EXISTS credit_utilization_summary;

-- Drop tables (in reverse order of dependencies)
*/

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

SELECT 'Phase 7 Task 3: Credit Management schema created successfully!' AS status;
