-- Add constraint to ensure licenses always inherit is_fake from their customer
-- This migration adds a trigger to automatically sync is_fake status

-- Function to sync is_fake status
CREATE OR REPLACE FUNCTION sync_license_fake_status()
RETURNS TRIGGER AS $$
BEGIN
    -- When inserting or updating a license, get is_fake from customer
    IF TG_OP = 'INSERT' OR (TG_OP = 'UPDATE' AND NEW.customer_id != OLD.customer_id) THEN
        SELECT is_fake INTO NEW.is_fake 
        FROM customers 
        WHERE id = NEW.customer_id;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger for licenses table
DROP TRIGGER IF EXISTS sync_license_fake_before_insert_update ON licenses;
CREATE TRIGGER sync_license_fake_before_insert_update
    BEFORE INSERT OR UPDATE ON licenses
    FOR EACH ROW
    EXECUTE FUNCTION sync_license_fake_status();

-- Function to update licenses when customer is_fake changes
CREATE OR REPLACE FUNCTION sync_customer_fake_to_licenses()
RETURNS TRIGGER AS $$
BEGIN
    -- When customer is_fake changes, update all their licenses
    IF TG_OP = 'UPDATE' AND NEW.is_fake != OLD.is_fake THEN
        UPDATE licenses 
        SET is_fake = NEW.is_fake 
        WHERE customer_id = NEW.id;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger for customers table
DROP TRIGGER IF EXISTS sync_customer_fake_after_update ON customers;
CREATE TRIGGER sync_customer_fake_after_update
    AFTER UPDATE ON customers
    FOR EACH ROW
    EXECUTE FUNCTION sync_customer_fake_to_licenses();

-- Verify current data is consistent (should return 0)
DO $$
DECLARE
    mismatch_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO mismatch_count
    FROM licenses l
    JOIN customers c ON l.customer_id = c.id
    WHERE l.is_fake != c.is_fake;
    
    IF mismatch_count > 0 THEN
        RAISE NOTICE 'Found % mismatches. Fixing...', mismatch_count;
        
        -- Fix any existing mismatches
        UPDATE licenses l
        SET is_fake = c.is_fake
        FROM customers c
        WHERE l.customer_id = c.id
        AND l.is_fake != c.is_fake;
        
        RAISE NOTICE 'Fixed all mismatches.';
    ELSE
        RAISE NOTICE 'No mismatches found. Data is consistent.';
    END IF;
END $$;