BEGIN;

DO $$
DECLARE
    v_tenant record;
    v_pipeline_id bigint;
BEGIN
    FOR v_tenant IN
        SELECT id
        FROM platform.tenants
        WHERE deleted_at IS NULL
    LOOP
        INSERT INTO crm.lead_statuses (
            tenant_id, status_code, status_name, sequence_no, is_default, is_closed,
            color_code, is_active, is_deleted, created_at, updated_at
        )
        SELECT v_tenant.id, v.status_code, v.status_name, v.sequence_no, v.is_default, v.is_closed,
               v.color_code, TRUE, FALSE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
        FROM (VALUES
            ('OPEN', 'Open', 10, TRUE, FALSE, '#3b82f6'),
            ('WORKING', 'Working', 20, FALSE, FALSE, '#06b6d4'),
            ('NURTURING', 'Nurturing', 30, FALSE, FALSE, '#8b5cf6'),
            ('QUALIFIED', 'Qualified', 40, FALSE, FALSE, '#22c55e'),
            ('UNQUALIFIED', 'Unqualified', 90, FALSE, TRUE, '#ef4444')
        ) AS v(status_code, status_name, sequence_no, is_default, is_closed, color_code)
        WHERE NOT EXISTS (
            SELECT 1
            FROM crm.lead_statuses st
            WHERE st.tenant_id = v_tenant.id
              AND st.status_code = v.status_code
              AND COALESCE(st.is_deleted, FALSE) = FALSE
        );

        UPDATE crm.lead_statuses st
        SET status_name = v.status_name,
            sequence_no = v.sequence_no,
            is_default = v.is_default,
            is_closed = v.is_closed,
            color_code = v.color_code,
            is_active = TRUE,
            updated_at = CURRENT_TIMESTAMP
        FROM (VALUES
            ('OPEN', 'Open', 10, TRUE, FALSE, '#3b82f6'),
            ('WORKING', 'Working', 20, FALSE, FALSE, '#06b6d4'),
            ('NURTURING', 'Nurturing', 30, FALSE, FALSE, '#8b5cf6'),
            ('QUALIFIED', 'Qualified', 40, FALSE, FALSE, '#22c55e'),
            ('UNQUALIFIED', 'Unqualified', 90, FALSE, TRUE, '#ef4444')
        ) AS v(status_code, status_name, sequence_no, is_default, is_closed, color_code)
        WHERE st.tenant_id = v_tenant.id
          AND st.status_code = v.status_code
          AND COALESCE(st.is_deleted, FALSE) = FALSE;

        INSERT INTO crm.pipelines (
            tenant_id, pipeline_code, pipeline_name, description, is_default,
            is_active, is_deleted, created_at, updated_at
        )
        SELECT v_tenant.id, 'OPPORTUNITY', 'Opportunity Pipeline',
               'Default revenue pipeline for opportunity forecasting and close probability.',
               TRUE, TRUE, FALSE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
        WHERE NOT EXISTS (
            SELECT 1
            FROM crm.pipelines p
            WHERE p.tenant_id = v_tenant.id
              AND p.pipeline_code = 'OPPORTUNITY'
              AND COALESCE(p.is_deleted, FALSE) = FALSE
        );

        UPDATE crm.pipelines
        SET pipeline_name = 'Opportunity Pipeline',
            description = 'Default revenue pipeline for opportunity forecasting and close probability.',
            is_default = TRUE,
            is_active = TRUE,
            updated_at = CURRENT_TIMESTAMP
        WHERE tenant_id = v_tenant.id
          AND pipeline_code = 'OPPORTUNITY'
          AND COALESCE(is_deleted, FALSE) = FALSE;

        UPDATE crm.pipelines
        SET is_default = FALSE,
            updated_at = CURRENT_TIMESTAMP
        WHERE tenant_id = v_tenant.id
          AND pipeline_code <> 'OPPORTUNITY'
          AND COALESCE(is_deleted, FALSE) = FALSE;

        SELECT id INTO v_pipeline_id
        FROM crm.pipelines
        WHERE tenant_id = v_tenant.id
          AND pipeline_code = 'OPPORTUNITY'
          AND COALESCE(is_deleted, FALSE) = FALSE
        ORDER BY id ASC
        LIMIT 1;

        INSERT INTO crm.pipeline_stages (
            tenant_id, pipeline_id, stage_code, stage_name, sequence_no, probability_percent,
            is_won_stage, is_lost_stage, color_code, is_active, is_deleted, created_at, updated_at
        )
        SELECT v_tenant.id, v_pipeline_id, v.stage_code, v.stage_name, v.sequence_no, v.probability_percent,
               v.is_won_stage, v.is_lost_stage, v.color_code, TRUE, FALSE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
        FROM (VALUES
            ('prospecting', 'Prospecting', 10, 10.00, FALSE, FALSE, '#64748b'),
            ('qualification', 'Qualification', 20, 10.00, FALSE, FALSE, '#3b82f6'),
            ('needs_analysis', 'Needs Analysis', 30, 20.00, FALSE, FALSE, '#06b6d4'),
            ('value_proposition', 'Value Proposition', 40, 50.00, FALSE, FALSE, '#8b5cf6'),
            ('decision_makers', 'Decision Makers', 50, 60.00, FALSE, FALSE, '#14b8a6'),
            ('perception_analysis', 'Perception Analysis', 60, 70.00, FALSE, FALSE, '#f59e0b'),
            ('proposal_price_quote', 'Proposal/Price Quote', 70, 75.00, FALSE, FALSE, '#f97316'),
            ('negotiation_review', 'Negotiation/Review', 80, 90.00, FALSE, FALSE, '#ea580c'),
            ('closed_won', 'Closed Won', 90, 100.00, TRUE, FALSE, '#22c55e'),
            ('closed_lost', 'Closed Lost', 99, 0.00, FALSE, TRUE, '#ef4444')
        ) AS v(stage_code, stage_name, sequence_no, probability_percent, is_won_stage, is_lost_stage, color_code)
        WHERE NOT EXISTS (
            SELECT 1
            FROM crm.pipeline_stages ps
            WHERE ps.tenant_id = v_tenant.id
              AND ps.pipeline_id = v_pipeline_id
              AND ps.stage_code = v.stage_code
              AND COALESCE(ps.is_deleted, FALSE) = FALSE
        );

        UPDATE crm.pipeline_stages ps
        SET stage_name = v.stage_name,
            sequence_no = v.sequence_no,
            probability_percent = v.probability_percent,
            is_won_stage = v.is_won_stage,
            is_lost_stage = v.is_lost_stage,
            color_code = v.color_code,
            is_active = TRUE,
            updated_at = CURRENT_TIMESTAMP
        FROM (VALUES
            ('prospecting', 'Prospecting', 10, 10.00, FALSE, FALSE, '#64748b'),
            ('qualification', 'Qualification', 20, 10.00, FALSE, FALSE, '#3b82f6'),
            ('needs_analysis', 'Needs Analysis', 30, 20.00, FALSE, FALSE, '#06b6d4'),
            ('value_proposition', 'Value Proposition', 40, 50.00, FALSE, FALSE, '#8b5cf6'),
            ('decision_makers', 'Decision Makers', 50, 60.00, FALSE, FALSE, '#14b8a6'),
            ('perception_analysis', 'Perception Analysis', 60, 70.00, FALSE, FALSE, '#f59e0b'),
            ('proposal_price_quote', 'Proposal/Price Quote', 70, 75.00, FALSE, FALSE, '#f97316'),
            ('negotiation_review', 'Negotiation/Review', 80, 90.00, FALSE, FALSE, '#ea580c'),
            ('closed_won', 'Closed Won', 90, 100.00, TRUE, FALSE, '#22c55e'),
            ('closed_lost', 'Closed Lost', 99, 0.00, FALSE, TRUE, '#ef4444')
        ) AS v(stage_code, stage_name, sequence_no, probability_percent, is_won_stage, is_lost_stage, color_code)
        WHERE ps.tenant_id = v_tenant.id
          AND ps.pipeline_id = v_pipeline_id
          AND ps.stage_code = v.stage_code
          AND COALESCE(ps.is_deleted, FALSE) = FALSE;
    END LOOP;
END $$;

COMMIT;