import { NextRequest, NextResponse } from 'next/server';
import pool from '../../../../../lib/db/db';
import { RowDataPacket, ResultSetHeader } from 'mysql2/promise';

interface ConsultantStatusRow extends RowDataPacket {
  consultant_id: number;
  user_id: number;
  email: string;
  consultant_active: string; // '1' | '0'
  user_active: number;       // 1 | 0
}

export async function POST(request: NextRequest) {
  const connection = await pool.getConnection();

  try {
    /* =========================
       AUTHORIZATION
    ========================== */
    const authHeader = request.headers.get('authorization');
    const expectedApiKey = `Bearer ${process.env.CRM_API_KEY}`;

    if (!authHeader || authHeader !== expectedApiKey) {
      return NextResponse.json(
        { success: false, message: 'Unauthorized: Invalid API key' },
        { status: 401 }
      );
    }

    /* =========================
       REQUEST BODY
    ========================== */
    const body = await request.json();
    const { consultant_id, status } = body;

    if (!consultant_id) {
      return NextResponse.json(
        { success: false, message: 'Consultant ID is required' },
        { status: 400 }
      );
    }

    const numericStatus = Number(status);
    if (![0, 1].includes(numericStatus)) {
      return NextResponse.json(
        {
          success: false,
          message: 'Status must be either 1 (active) or 0 (inactive)',
        },
        { status: 400 }
      );
    }

    await connection.beginTransaction();

    /* =========================
       CHECK CONSULTANT
    ========================== */
    const [consultantCheck] = await connection.query<ConsultantStatusRow[]>(
      `
      SELECT 
        c.id   AS consultant_id,
        c.user_id,
        c.active AS consultant_active,
        u.email,
        u.is_active AS user_active
      FROM consultants c
      JOIN users u ON c.user_id = u.id
      WHERE c.id = ? OR c.user_id = ?
      `,
      [consultant_id, consultant_id]
    );

    if (consultantCheck.length === 0) {
      await connection.rollback();
      return NextResponse.json(
        { success: false, message: 'Consultant not found' },
        { status: 404 }
      );
    }

    const consultant = consultantCheck[0];

    /* =========================
       NO CHANGE NEEDED
    ========================== */
    if (consultant.consultant_active === numericStatus.toString()) {
      await connection.rollback();

      return NextResponse.json(
        {
          success: true,
          message: `Consultant already ${numericStatus === 1 ? 'active' : 'inactive'}`,
          data: {
            consultant_id: consultant.consultant_id,
            user_id: consultant.user_id,
            current_status: consultant.consultant_active,
            requested_status: numericStatus,
            no_change_needed: true,
          },
        },
        { status: 200 }
      );
    }

    /* =========================
       UPDATE CONSULTANTS TABLE
    ========================== */
    const [consultantUpdate] = await connection.query<ResultSetHeader>(
      `
      UPDATE consultants
      SET active = ?, updated_at = NOW()
      WHERE id = ? OR user_id = ?
      `,
      [numericStatus.toString(), consultant_id, consultant_id]
    );

    if (consultantUpdate.affectedRows === 0) {
      await connection.rollback();
      return NextResponse.json(
        { success: false, message: 'Failed to update consultant status' },
        { status: 500 }
      );
    }

    /* =========================
       UPDATE USERS TABLE
    ========================== */
    await connection.query<ResultSetHeader>(
      `
      UPDATE users
      SET is_active = ?, updated_at = NOW()
      WHERE id = ?
      `,
      [numericStatus, consultant.user_id]
    );

    await connection.commit();

    /* =========================
       FETCH UPDATED DATA
    ========================== */
    const [updatedData] = await connection.query<RowDataPacket[]>(
      `
      SELECT 
        c.id AS consultant_id,
        c.active AS consultant_active,
        u.id AS user_id,
        u.is_active AS user_active,
        u.updated_at
      FROM consultants c
      JOIN users u ON c.user_id = u.id
      WHERE c.id = ? OR c.user_id = ?
      `,
      [consultant_id, consultant_id]
    );

    return NextResponse.json(
      {
        success: true,
        message: `Consultant ${numericStatus === 1 ? 'activated' : 'deactivated'} successfully`,
        data: updatedData[0] || {},
      },
      { status: 200 }
    );
  } catch (error: any) {
    try {
      await connection.rollback();
    } catch {}

    console.error('CRM Status Update API error:', error);

    return NextResponse.json(
      {
        success: false,
        message: error.message || 'Internal server error',
      },
      { status: 500 }
    );
  } finally {
    connection.release();
  }
}
