-- ============================================================================
-- Phase 7: Finance Integration - Payment Reminders Database Schema
-- ============================================================================
-- Creates tables for automated payment reminder system
--
-- Tables Created:
-- 1. payment_reminders - Track all reminder sends
-- 2. payment_reminder_config - Per-org configuration
-- 3. customer_reminder_preferences - Customer-specific preferences
--
-- Author: TAF Development Team
-- Date: October 17, 2025
-- Phase: 7 - Finance Integration
-- Task: 2 - Payment Reminders
-- ============================================================================

-- Drop existing tables if they exist (for clean reinstall)

-- ============================================================================
-- Table 1: payment_reminder_config
-- ============================================================================
-- Per-organization payment reminder configuration
-- ============================================================================

CREATE TABLE payment_reminder_config (
    config_id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    org_id uuid NOT NULL UNIQUE REFERENCES orgs(org_id) ON DELETE CASCADE,
    
    -- General settings
    enabled boolean NOT NULL DEFAULT true,
    max_reminders_per_invoice integer NOT NULL DEFAULT 5,
    min_amount_threshold numeric(10,2) DEFAULT 0.00,
    
    -- Reminder stages configuration
    stages jsonb NOT NULL DEFAULT '[
        {"stage": "7_days_before", "enabled": true, "channels": ["email"], "template": "reminder_7_days_before"},
        {"stage": "due_date", "enabled": true, "channels": ["email", "portal"], "template": "reminder_due_date"},
        {"stage": "3_days_after", "enabled": true, "channels": ["email"], "template": "reminder_3_days_after"},
        {"stage": "7_days_after", "enabled": true, "channels": ["email", "sms"], "template": "reminder_7_days_after"},
        {"stage": "30_days_after", "enabled": true, "channels": ["email", "sms"], "template": "reminder_30_days_after"}
    ]'::jsonb,
    
    -- Email configuration
    from_name text,
    from_email text,
    reply_to_email text,
    cc_emails text[],
    
    -- Email templates (HTML)
    email_templates jsonb DEFAULT '{}'::jsonb,
    
    -- SMS templates (plain text)
    sms_templates jsonb DEFAULT '{}'::jsonb,
    
    -- Twilio configuration
    twilio_enabled boolean DEFAULT false,
    twilio_account_sid text,
    twilio_auth_token text,
    twilio_from_number text,
    
    -- Exclusions
    exclude_customer_ids uuid[],
    
    -- Audit fields
    isdeleted boolean NOT NULL DEFAULT false,
    updatedat timestamp with time zone NOT NULL DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_payment_reminder_config_org ON payment_reminder_config(org_id) WHERE isdeleted = false;

-- Trigger for updatedat
CREATE TRIGGER trg_payment_reminder_config_updatedat
    BEFORE UPDATE ON payment_reminder_config
    FOR EACH ROW
    EXECUTE FUNCTION set_updatedat();

COMMENT ON TABLE payment_reminder_config IS 'Per-organization payment reminder configuration and templates';

-- ============================================================================
-- Table 2: payment_reminders
-- ============================================================================
-- Track all payment reminder sends with delivery and engagement metrics
-- ============================================================================

CREATE TABLE payment_reminders (
    reminder_id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    org_id uuid NOT NULL REFERENCES orgs(org_id) ON DELETE CASCADE,
    invoice_id uuid NOT NULL REFERENCES invoices(invoice_id) ON DELETE CASCADE,
    customer_id uuid NOT NULL, -- Polymorphic: customers or clients
    
    -- Reminder details
    reminder_stage text NOT NULL CHECK (reminder_stage IN (
        '7_days_before', 
        'due_date', 
        '3_days_after', 
        '7_days_after', 
        '30_days_after',
        'manual'
    )),
    channel text NOT NULL CHECK (channel IN ('email', 'sms', 'portal')),
    
    -- Timestamps
    sent_at timestamp with time zone NOT NULL DEFAULT NOW(),
    delivered_at timestamp with time zone,
    opened_at timestamp with time zone,
    clicked_at timestamp with time zone,
    
    -- Status tracking
    status text NOT NULL DEFAULT 'pending' CHECK (status IN (
        'pending',
        'sent',
        'delivered',
        'opened',
        'clicked',
        'failed',
        'bounced'
    )),
    error_message text,
    
    -- Template and content
    template_used text,
    subject text,
    message_preview text, -- First 200 chars
    
    -- Recipient information
    recipient_email text,
    recipient_phone text,
    
    -- Tracking metadata
    metadata jsonb DEFAULT '{}'::jsonb,
    -- Example metadata:
    -- {
    --   "email_message_id": "msg_xxx",
    --   "sms_message_sid": "SM_xxx",
    --   "tracking_pixel_url": "https://...",
    --   "payment_link": "https://...",
    --   "ip_address": "1.2.3.4",
    --   "user_agent": "...",
    --   "twilio_status": "delivered"
    -- }
    
    -- Audit fields
    isdeleted boolean NOT NULL DEFAULT false,
    updatedat timestamp with time zone NOT NULL DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX idx_payment_reminders_invoice ON payment_reminders(invoice_id, sent_at) WHERE isdeleted = false;
CREATE INDEX idx_payment_reminders_customer ON payment_reminders(customer_id, sent_at) WHERE isdeleted = false;
CREATE INDEX idx_payment_reminders_status ON payment_reminders(status, sent_at) WHERE isdeleted = false;
CREATE INDEX idx_payment_reminders_org_stage ON payment_reminders(org_id, reminder_stage, sent_at) WHERE isdeleted = false;
CREATE INDEX idx_payment_reminders_channel ON payment_reminders(channel, status) WHERE isdeleted = false;
CREATE INDEX idx_payment_reminders_metadata ON payment_reminders USING gin(metadata) WHERE isdeleted = false;

-- Trigger for updatedat
CREATE TRIGGER trg_payment_reminders_updatedat
    BEFORE UPDATE ON payment_reminders
    FOR EACH ROW
    EXECUTE FUNCTION set_updatedat();

COMMENT ON TABLE payment_reminders IS 'Payment reminder send history with delivery and engagement tracking';

-- ============================================================================
-- Table 3: customer_reminder_preferences
-- ============================================================================
-- Customer-specific reminder preferences and opt-out management
-- ============================================================================

CREATE TABLE customer_reminder_preferences (
    preference_id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    customer_id uuid NOT NULL UNIQUE, -- Polymorphic: customers or clients
    org_id uuid NOT NULL REFERENCES orgs(org_id) ON DELETE CASCADE,
    
    -- Opt-out management
    opted_out boolean NOT NULL DEFAULT false,
    opted_out_at timestamp with time zone,
    opt_out_reason text,
    
    -- Channel preferences
    preferred_channels text[] DEFAULT ARRAY['email']::text[],
    
    -- Contact overrides
    preferred_email text,
    preferred_phone text,
    
    -- Grace period customization
    do_not_remind_before_days integer DEFAULT 0,
    
    -- Notes
    notes text,
    
    -- Audit fields
    isdeleted boolean NOT NULL DEFAULT false,
    updatedat timestamp with time zone NOT NULL DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_customer_reminder_prefs_customer ON customer_reminder_preferences(customer_id) WHERE isdeleted = false;
CREATE INDEX idx_customer_reminder_prefs_org ON customer_reminder_preferences(org_id) WHERE isdeleted = false;
CREATE INDEX idx_customer_reminder_prefs_opted_out ON customer_reminder_preferences(opted_out) WHERE opted_out = true;

-- Trigger for updatedat
CREATE TRIGGER trg_customer_reminder_prefs_updatedat
    BEFORE UPDATE ON customer_reminder_preferences
    FOR EACH ROW
    EXECUTE FUNCTION set_updatedat();

COMMENT ON TABLE customer_reminder_preferences IS 'Customer-specific payment reminder preferences and opt-out status';

-- ============================================================================
-- View: reminder_statistics
-- ============================================================================
-- Aggregated reminder statistics by organization
-- ============================================================================

CREATE OR REPLACE VIEW reminder_statistics AS
SELECT 
    org_id,
    
    -- Overall counts
    COUNT(*) as total_reminders_sent,
    COUNT(*) FILTER (WHERE sent_at >= CURRENT_DATE) as sent_today,
    COUNT(*) FILTER (WHERE sent_at >= CURRENT_DATE - INTERVAL '7 days') as sent_last_7_days,
    COUNT(*) FILTER (WHERE sent_at >= CURRENT_DATE - INTERVAL '30 days') as sent_last_30_days,
    
    -- By channel
    COUNT(*) FILTER (WHERE channel = 'email') as email_count,
    COUNT(*) FILTER (WHERE channel = 'sms') as sms_count,
    COUNT(*) FILTER (WHERE channel = 'portal') as portal_count,
    
    -- By status
    COUNT(*) FILTER (WHERE status = 'delivered') as delivered_count,
    COUNT(*) FILTER (WHERE status = 'opened') as opened_count,
    COUNT(*) FILTER (WHERE status = 'clicked') as clicked_count,
    COUNT(*) FILTER (WHERE status = 'failed') as failed_count,
    COUNT(*) FILTER (WHERE status = 'bounced') as bounced_count,
    
    -- Engagement rates (%)
    ROUND(
        100.0 * COUNT(*) FILTER (WHERE opened_at IS NOT NULL) / NULLIF(COUNT(*), 0),
        2
    ) as open_rate_pct,
    ROUND(
        100.0 * COUNT(*) FILTER (WHERE clicked_at IS NOT NULL) / NULLIF(COUNT(*), 0),
        2
    ) as click_rate_pct,
    ROUND(
        100.0 * COUNT(*) FILTER (WHERE status = 'delivered') / NULLIF(COUNT(*), 0),
        2
    ) as delivery_rate_pct,
    
    -- By stage
    COUNT(*) FILTER (WHERE reminder_stage = '7_days_before') as stage_7_before,
    COUNT(*) FILTER (WHERE reminder_stage = 'due_date') as stage_due_date,
    COUNT(*) FILTER (WHERE reminder_stage = '3_days_after') as stage_3_after,
    COUNT(*) FILTER (WHERE reminder_stage = '7_days_after') as stage_7_after,
    COUNT(*) FILTER (WHERE reminder_stage = '30_days_after') as stage_30_after,
    
    -- Recent activity
    MAX(sent_at) as last_reminder_sent,
    MIN(sent_at) as first_reminder_sent
    
FROM payment_reminders
WHERE isdeleted = false
GROUP BY org_id;

COMMENT ON VIEW reminder_statistics IS 'Aggregated payment reminder statistics and engagement metrics';

-- ============================================================================
-- Function: get_pending_reminders
-- ============================================================================
-- Returns invoices that need reminders sent based on aging and config
-- ============================================================================

CREATE OR REPLACE FUNCTION get_pending_reminders(p_org_id uuid)
RETURNS TABLE (
    invoice_id uuid,
    customer_id uuid,
    invoice_number text,
    customer_name text,
    customer_email text,
    customer_phone text,
    due_date date,
    days_overdue integer,
    outstanding_amount numeric,
    suggested_stage text,
    already_sent_count bigint
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        ia.invoice_id,
        ia.customer_id,
        ia.invoice_number,
        ia.customer_name,
        ia.customer_email,
        ia.customer_phone,
        ia.due_date,
        ia.days_overdue,
        ia.outstanding_amount,
        
        -- Determine suggested reminder stage based on days overdue
        CASE 
            WHEN ia.days_overdue <= -7 THEN '7_days_before'::text
            WHEN ia.days_overdue BETWEEN -6 AND 0 THEN 'due_date'::text
            WHEN ia.days_overdue BETWEEN 1 AND 3 THEN '3_days_after'::text
            WHEN ia.days_overdue BETWEEN 4 AND 7 THEN '7_days_after'::text
            WHEN ia.days_overdue >= 8 THEN '30_days_after'::text
        END as suggested_stage,
        
        -- Count how many reminders already sent for this invoice
        COALESCE(
            (SELECT COUNT(*) 
             FROM payment_reminders pr 
             WHERE pr.invoice_id = ia.invoice_id 
               AND pr.isdeleted = false),
            0
        ) as already_sent_count
        
    FROM invoice_aging ia
    WHERE ia.org_id = p_org_id
      AND ia.outstanding_amount > 0
      AND ia.payment_status IN ('overdue', 'current', 'partial')
      -- Exclude customers who opted out
      AND ia.customer_id NOT IN (
          SELECT customer_id 
          FROM customer_reminder_preferences 
          WHERE opted_out = true 
            AND isdeleted = false
      )
    ORDER BY ia.days_overdue DESC, ia.outstanding_amount DESC;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION get_pending_reminders IS 'Returns invoices needing payment reminders based on aging analysis';

-- ============================================================================
-- Verification
-- ============================================================================

DO $$
DECLARE
    table_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO table_count
    FROM information_schema.tables
    WHERE table_schema = 'public'
      AND table_name IN ('payment_reminders', 'payment_reminder_config', 'customer_reminder_preferences');
    
    IF table_count < 3 THEN
        RAISE WARNING 'Expected 3 tables, found %', table_count;
    ELSE
        RAISE NOTICE 'Successfully created % payment reminder tables', table_count;
    END IF;
    
    -- Check view
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.views 
        WHERE table_schema = 'public' AND table_name = 'reminder_statistics'
    ) THEN
        RAISE WARNING 'reminder_statistics view not created';
    ELSE
        RAISE NOTICE 'reminder_statistics view created successfully';
    END IF;
    
    -- Check function
    IF NOT EXISTS (
        SELECT 1 FROM pg_proc 
        WHERE proname = 'get_pending_reminders'
    ) THEN
        RAISE WARNING 'get_pending_reminders function not created';
    ELSE
        RAISE NOTICE 'get_pending_reminders function created successfully';
    END IF;
END $$;

-- Display created objects
SELECT 
    'Tables' as object_type,
    COUNT(*) as count
FROM information_schema.tables
WHERE table_schema = 'public'
  AND table_name LIKE '%reminder%'

UNION ALL

SELECT 
    'Views' as object_type,
    COUNT(*) as count
FROM information_schema.views
WHERE table_schema = 'public'
  AND table_name LIKE '%reminder%'

UNION ALL

SELECT 
    'Functions' as object_type,
    COUNT(*) as count
FROM pg_proc
WHERE proname LIKE '%reminder%';

-- ============================================================================
-- Sample Configuration Insert
-- ============================================================================
-- Uncomment to create default config for testing
-- ============================================================================

/*
INSERT INTO payment_reminder_config (
    org_id,
    enabled,
    from_name,
    from_email,
    reply_to_email
)
SELECT 
    org_id,
    true,
    'TAF Billing',
    'billing@example.com',
    'support@example.com'
FROM orgs
WHERE NOT EXISTS (
    SELECT 1 FROM payment_reminder_config WHERE payment_reminder_config.org_id = orgs.org_id
)
LIMIT 1;
*/

-- ============================================================================
-- Deployment Instructions
-- ============================================================================
-- 1. Deploy this migration:
--    docker cp migrations/phase7_payment_reminders_schema.sql postgres:/tmp/
--    docker exec postgres psql -U taf -d devdb -f /tmp/phase7_payment_reminders_schema.sql
--
-- 2. Verify tables created:
--    docker exec postgres psql -U taf -d devdb -c "\dt *reminder*"
--
-- 3. Test the function:
--    SELECT * FROM get_pending_reminders('org-uuid-here');
--
-- 4. Next steps:
--    - Deploy PaymentReminderService.php
--    - Deploy PaymentRemindersController.php
--    - Deploy cron job
--    - Create email templates
-- ============================================================================
