import { query } from '../config/database';

export const getAllPendings = async (filters?: any) => {
    let queryText = `
    SELECT p.*, 
           l.lead_id,
           u1.name as assigned_to_name,
           u2.name as created_by_name,
           CASE 
             WHEN p.sla_breach_at < CURRENT_TIMESTAMP AND p.status != 'Resolved' THEN true
             ELSE false
           END as is_breached
    FROM pendings p
    LEFT JOIN leads l ON p.lead_id = l.id
    LEFT JOIN users u1 ON p.assigned_to = u1.id
    LEFT JOIN users u2 ON p.created_by = u2.id
    ORDER BY p.created_at DESC
  `;

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

export const createPending = async (
    leadId: number,
    pendingType: string,
    description: string,
    dueDate: string,
    assignedTo: number,
    createdBy: number,
    slaHours: number = 24
) => {
    const result = await query(
        `INSERT INTO pendings (lead_id, pending_type, description, due_date, assigned_to, created_by, sla_hours)
     VALUES ($1, $2, $3, $4, $5, $6, $7)
     RETURNING *`,
        [leadId, pendingType, description, dueDate, assignedTo, createdBy, slaHours]
    );

    return result.rows[0];
};

export const updatePending = async (
    id: number,
    data: { status?: string; description?: string }
) => {
    const fields: string[] = [];
    const values: any[] = [];
    let paramIndex = 1;

    if (data.status) {
        fields.push(`status = $${paramIndex++}`);
        values.push(data.status);

        if (data.status === 'Resolved') {
            fields.push(`resolved_at = CURRENT_TIMESTAMP`);
        }
    }

    if (data.description) {
        fields.push(`description = $${paramIndex++}`);
        values.push(data.description);
    }

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

    values.push(id);

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

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

    return result.rows[0];
};

export const escalatePending = async (
    id: number,
    escalatedTo: number,
    escalatedBy: number
) => {
    const result = await query(
        `UPDATE pendings 
     SET is_escalated = true,
         escalated_to = $1,
         escalated_at = CURRENT_TIMESTAMP
     WHERE id = $2
     RETURNING *`,
        [escalatedTo, id]
    );

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

    // Log activity
    const pending = result.rows[0];
    await query(
        `INSERT INTO activities (lead_id, activity_type, title, description, performed_by)
     VALUES ($1, $2, $3, $4, $5)`,
        [pending.lead_id, 'PENDING_ESCALATED', 'Pending Escalated', `Pending item escalated due to SLA breach`, escalatedBy]
    );

    return result.rows[0];
};

export const getBreachedPendings = async () => {
    const result = await query(`
    SELECT p.*, 
           l.lead_id,
           u.name as assigned_to_name
    FROM pendings p
    LEFT JOIN leads l ON p.lead_id = l.id
    LEFT JOIN users u ON p.assigned_to = u.id
    WHERE p.sla_breach_at < CURRENT_TIMESTAMP 
      AND p.status != 'Resolved'
      AND p.is_escalated = false
    ORDER BY p.sla_breach_at ASC
  `);

    return result.rows;
};
