import { query } from '../config/database';
import { generateLeadId, generateFileNumber } from '../utils/idGenerator';

interface CreateLeadData {
    leadType: string;
    source?: string;
    assignedTo?: number;
    pipelineId?: number;
    stageId?: number;
    loanAmount?: number;
    propertyType?: string;
    propertyValue?: number;
    propertyAddress?: string;
    refereeName?: string;
    refereePhone?: string;
    pocName?: string;
    pocPhone?: string;
    pocEmail?: string;
    priority?: string;
    applicant: {
        firstName: string;
        lastName?: string;
        email?: string;
        phone: string;
        dateOfBirth?: string;
        monthlyIncome?: number;
        employmentType?: string;
        employerName?: string;
        panNumber?: string;
        aadharNumber?: string;
        currentAddress?: string;
    };
    coApplicant?: {
        firstName: string;
        lastName?: string;
        email?: string;
        phone?: string;
        relationship?: string;
        monthlyIncome?: number;
    };
}

export const createLead = async (data: CreateLeadData, createdBy: number) => {
    const client = await query('BEGIN');

    try {
        // Generate unique lead_id
        const leadId = await generateLeadId();

        // Insert lead
        const leadResult = await query(
            `INSERT INTO leads (
        lead_id, lead_type, source, assigned_to, pipeline_id, stage_id,
        loan_amount, property_type, property_value, property_address,
        referee_name, referee_phone, poc_name, poc_phone, poc_email,
        priority, status, created_by
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18)
      RETURNING id, lead_id`,
            [
                leadId,
                data.leadType,
                data.source,
                data.assignedTo,
                data.pipelineId,
                data.stageId,
                data.loanAmount,
                data.propertyType,
                data.propertyValue,
                data.propertyAddress,
                data.refereeName,
                data.refereePhone,
                data.pocName,
                data.pocPhone,
                data.pocEmail,
                data.priority || 'Medium',
                'New',
                createdBy,
            ]
        );

        const lead = leadResult.rows[0];

        // Insert applicant
        await query(
            `INSERT INTO applicants (
        lead_id, first_name, last_name, email, phone, date_of_birth,
        monthly_income, employment_type, employer_name, pan_number, aadhar_number,
        current_address
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)`,
            [
                lead.id,
                data.applicant.firstName,
                data.applicant.lastName,
                data.applicant.email,
                data.applicant.phone,
                data.applicant.dateOfBirth,
                data.applicant.monthlyIncome,
                data.applicant.employmentType,
                data.applicant.employerName,
                data.applicant.panNumber,
                data.applicant.aadharNumber,
                data.applicant.currentAddress,
            ]
        );

        // Insert co-applicant if provided
        if (data.coApplicant) {
            await query(
                `INSERT INTO co_applicants (
          lead_id, first_name, last_name, email, phone, relationship, monthly_income
        ) VALUES ($1, $2, $3, $4, $5, $6, $7)`,
                [
                    lead.id,
                    data.coApplicant.firstName,
                    data.coApplicant.lastName,
                    data.coApplicant.email,
                    data.coApplicant.phone,
                    data.coApplicant.relationship,
                    data.coApplicant.monthlyIncome,
                ]
            );
        }

        // Log activity
        await query(
            `INSERT INTO activities (lead_id, activity_type, title, description, performed_by)
       VALUES ($1, $2, $3, $4, $5)`,
            [lead.id, 'LEAD_CREATED', 'Lead Created', `Lead ${leadId} was created`, createdBy]
        );

        await query('COMMIT');

        return lead;
    } catch (error) {
        await query('ROLLBACK');
        throw error;
    }
};

export const getAllLeads = async (filters?: any) => {
    let queryText = `
    SELECT l.*, 
           u.name as assigned_to_name,
           s.name as stage_name,
           p.name as pipeline_name,
           a.first_name || ' ' || COALESCE(a.last_name, '') as applicant_name
    FROM leads l
    LEFT JOIN users u ON l.assigned_to = u.id
    LEFT JOIN stages s ON l.stage_id = s.id
    LEFT JOIN pipelines p ON l.pipeline_id = p.id
    LEFT JOIN applicants a ON l.id = a.lead_id
    ORDER BY l.created_at DESC
  `;

    const result = await query(queryText);
    return result.rows;
};

export const getLeadById = async (id: number) => {
    const leadResult = await query(
        `SELECT l.*, 
            u.name as assigned_to_name,
            s.name as stage_name,
            p.name as pipeline_name
     FROM leads l
     LEFT JOIN users u ON l.assigned_to = u.id
     LEFT JOIN stages s ON l.stage_id = s.id
     LEFT JOIN pipelines p ON l.pipeline_id = p.id
     WHERE l.id = $1`,
        [id]
    );

    if (leadResult.rows.length === 0) {
        throw new Error('Lead not found');
    }

    const lead = leadResult.rows[0];

    // Get applicant
    const applicantResult = await query(
        'SELECT * FROM applicants WHERE lead_id = $1',
        [id]
    );
    lead.applicant = applicantResult.rows[0];

    // Get co-applicant
    const coApplicantResult = await query(
        'SELECT * FROM co_applicants WHERE lead_id = $1',
        [id]
    );
    lead.coApplicant = coApplicantResult.rows.length > 0 ? coApplicantResult.rows[0] : null;

    // Get documents
    const documentsResult = await query(
        'SELECT * FROM documents WHERE lead_id = $1 ORDER BY uploaded_at DESC',
        [id]
    );
    lead.documents = documentsResult.rows;

    // Get activities
    const activitiesResult = await query(
        `SELECT a.*, u.name as performed_by_name 
     FROM activities a
     LEFT JOIN users u ON a.performed_by = u.id
     WHERE a.lead_id = $1 
     ORDER BY a.performed_at DESC`,
        [id]
    );
    lead.activities = activitiesResult.rows;

    return lead;
};

export const updateLead = async (id: number, data: Partial<CreateLeadData>, updatedBy: number) => {
    // Build dynamic update query
    const fields: string[] = [];
    const values: any[] = [];
    let paramIndex = 1;

    if (data.source) {
        fields.push(`source = $${paramIndex++}`);
        values.push(data.source);
    }
    if (data.assignedTo) {
        fields.push(`assigned_to = $${paramIndex++}`);
        values.push(data.assignedTo);
    }
    if (data.stageId) {
        fields.push(`stage_id = $${paramIndex++}`);
        values.push(data.stageId);
    }
    if (data.loanAmount) {
        fields.push(`loan_amount = $${paramIndex++}`);
        values.push(data.loanAmount);
    }
    if (data.priority) {
        fields.push(`priority = $${paramIndex++}`);
        values.push(data.priority);
    }

    if (fields.length === 0) {
        throw new Error('No fields to update');
    }

    fields.push(`updated_at = CURRENT_TIMESTAMP`);
    values.push(id);

    const result = await query(
        `UPDATE leads SET ${fields.join(', ')} WHERE id = $${paramIndex} RETURNING *`,
        values
    );

    if (result.rows.length === 0) {
        throw new Error('Lead not found');
    }

    // Log activity
    await query(
        `INSERT INTO activities (lead_id, activity_type, title, description, performed_by)
     VALUES ($1, $2, $3, $4, $5)`,
        [id, 'LEAD_UPDATED', 'Lead Updated', 'Lead information was updated', updatedBy]
    );

    return result.rows[0];
};
