const { pool } = require("../../config/db");


// const createTransportAgency = async (req, res) => {
//   const client = await pool.connect();

//   try {
//     await client.query("BEGIN");

//     const org_id = req.session.user.orgId;

//     const {
//       agency_name,
//       contact_person,
//       phone_number,
//       email_address,
//       gst_number,
//       address,
//       transport_mode,
//     } = req.body;

//     const vehicles = req.body.vehicles
//       ? JSON.parse(req.body.vehicles)
//       : [];

//     // =========================
//     // VALIDATIONS
//     // =========================

//     if (!agency_name) {
//       return res.status(400).json({
//         success: false,
//         message: "Agency name is required",
//       });
//     }

//     // =========================
//     // CREATE AGENCY
//     // =========================

//     const agencyResult = await client.query(
//       `
//       INSERT INTO transport_agencies (
//         org_id,
//         agency_name,
//         contact_person,
//         phone_number,
//         email_address,
//         gst_number,
//         address,
//         transport_mode
//       )
//       VALUES ($1,$2,$3,$4,$5,$6,$7,$8)
//       RETURNING *;
//       `,
//       [
//         org_id,
//         agency_name,
//         contact_person,
//         phone_number,
//         email_address,
//         gst_number,
//         address,
//         transport_mode,
//       ]
//     );

//     const agency = agencyResult.rows[0];

//     const uploadedFiles = req.files || [];

//     const insertedVehicles = [];

//     // =========================
//     // INSERT VEHICLES + DRIVERS
//     // =========================

//     // for (const [index, vehicle] of vehicles.entries()) {

//     //   let driverId = null;

//     //   // =========================
//     //   // CREATE DRIVER
//     //   // =========================

//     //   if (
//     //     vehicle.driver_name &&
//     //     vehicle.driver_contact_number &&
//     //     vehicle.license_number
//     //   ) {

//     //     const driverResult = await client.query(
//     //       `
//     //       INSERT INTO transport_drivers (
//     //         transport_agency_id,
//     //         driver_name,
//     //         driver_contact_number,
//     //         driver_license_number
//     //       )
//     //       VALUES ($1,$2,$3,$4)
//     //       RETURNING id;
//     //       `,
//     //       [
//     //         agency.id,
//     //         vehicle.driver_name,
//     //         vehicle.driver_contact_number,
//     //         vehicle.license_number,
//     //       ]
//     //     );

//     //     driverId = driverResult.rows[0].id;
//     //   }

//     //   // =========================
//     //   // CREATE VEHICLE
//     //   // =========================

//     //   const vehicleResult = await client.query(
//     //     `
//     //     INSERT INTO transport_vehicles (
//     //       transport_agency_id,
//     //       vehicle_name,
//     //       vehicle_number,
//     //       vehicle_type,
//     //       capacity_tons,
//     //       driver_id,
//     //       vehicle_document
//     //     )
//     //     VALUES ($1,$2,$3,$4,$5,$6,$7)
//     //     RETURNING *;
//     //     `,
//     //     [
//     //       agency.id,
//     //       vehicle.vehicle_name,
//     //       vehicle.vehicle_number,
//     //       vehicle.vehicle_type,
//     //       vehicle.capacity_tons,
//     //       driverId,
//     //       uploadedFiles[index]?.filename || null,
//     //     ]
//     //   );

//     //   insertedVehicles.push(vehicleResult.rows[0]);
//     // }

// for (const [index, vehicle] of vehicles.entries()) {

//   // =========================
//   // VALIDATIONS
//   // =========================

//   if (!vehicle.vehicle_name) {
//     throw new Error(`Vehicle name required at row ${index + 1}`);
//   }

//   if (!vehicle.vehicle_number) {
//     throw new Error(`Vehicle number required at row ${index + 1}`);
//   }

//   // =========================
//   // DRIVER
//   // =========================

//   let driverId = null;

//   // CREATE DRIVER ONLY IF DRIVER DATA EXISTS
//   const hasDriverData =
//     vehicle.driver_name ||
//     vehicle.driver_contact_number ||
//     vehicle.license_number;

//   if (hasDriverData) {

//     // STRICT VALIDATION
//     if (
//       !vehicle.driver_name ||
//       !vehicle.driver_contact_number ||
//       !vehicle.license_number
//     ) {
//       throw new Error(
//         `Incomplete driver details at row ${index + 1}`
//       );
//     }

//     // =========================
//     // CHECK EXISTING DRIVER
//     // =========================

//     const existingDriver = await client.query(
//       `
//       SELECT id
//       FROM transport_drivers
//       WHERE
//         driver_contact_number = $1
//         AND org_id = $2
//         AND COALESCE(is_deleted,false) = false
//       LIMIT 1
//       `,
//       [
//         vehicle.driver_contact_number,
//         org_id,
//       ]
//     );

//     // =========================
//     // REUSE EXISTING DRIVER
//     // =========================

//     if (existingDriver.rows.length > 0) {

//       driverId = existingDriver.rows[0].id;

//     } else {

//       // =========================
//       // CREATE DRIVER
//       // =========================

//       const driverResult = await client.query(
//         `
//         INSERT INTO transport_drivers (
//           transport_agency_id,
//           org_id,
//           driver_name,
//           driver_contact_number,
//           driver_license_number,
//           is_available,
//           created_at
//         )
//         VALUES ($1,$2,$3,$4,$5,true,CURRENT_TIMESTAMP)
//         RETURNING id;
//         `,
//         [
//           agency.id,
//           org_id,
//           vehicle.driver_name,
//           vehicle.driver_contact_number,
//           vehicle.license_number,
//         ]
//       );

//       driverId = driverResult.rows[0].id;
//     }
//   }

//   // =========================
//   // VEHICLE DOCUMENT
//   // =========================

//   const vehicleDocument =
//     uploadedFiles[index]?.filename || null;

//   // =========================
//   // CREATE VEHICLE
//   // =========================

//   const vehicleResult = await client.query(
//     `
//     INSERT INTO transport_vehicles (
//       transport_agency_id,

//       vehicle_name,
//       vehicle_number,
//       vehicle_type,
//       capacity_tons,

//       driver_id,

//       driver_name,
//       driver_contact_number,
//       license_number,

//       vehicle_document,

//       status,
//       created_at,
//       updated_at,
//       is_deleted
//     )
//     VALUES (
//       $1,
//       $2,
//       $3,
//       $4,
//       $5,

//       $6,

//       $7,
//       $8,
//       $9,

//       $10,

//       'Active',
//       CURRENT_TIMESTAMP,
//       CURRENT_TIMESTAMP,
//       false
//     )
//     RETURNING *;
//     `,
//     [
//       agency.id,

//       vehicle.vehicle_name,
//       vehicle.vehicle_number,
//       vehicle.vehicle_type,
//       vehicle.capacity_tons,

//       driverId,

//       vehicle.driver_name || null,
//       vehicle.driver_contact_number || null,
//       vehicle.license_number || null,

//       vehicleDocument,
//     ]
//   );

//   insertedVehicles.push(vehicleResult.rows[0]);
// }


//     await client.query("COMMIT");

//     return res.status(201).json({
//       success: true,
//       message: "Transport agency created successfully",
//       data: {
//         agency,
//         vehicles: insertedVehicles,
//       },
//     });

//   } catch (error) {

//     await client.query("ROLLBACK");

//     console.error("CREATE TRANSPORT ERROR:", error);

//     return res.status(500).json({
//       success: false,
//       message: "Internal server error",
//       error: error.message,
//     });

//   } finally {
//     client.release();
//   }
// };
const createTransportAgency = async (req, res) => {
  const client = await pool.connect();

  try {
    await client.query("BEGIN");

    const org_id = req.session.user.orgId;

    const {
      agency_name,
      contact_person,
      phone_number,
      email_address,
      gst_number,
      address,
      transport_mode,
    } = req.body;

    // =========================
    // PARSE VEHICLES
    // =========================

    const vehicles = req.body.vehicles
      ? JSON.parse(req.body.vehicles)
      : [];

    // =========================
    // VALIDATIONS
    // =========================

    if (!agency_name?.trim()) {
      return res.status(400).json({
        success: false,
        message: "Agency name is required",
      });
    }

    if (!transport_mode) {
      return res.status(400).json({
        success: false,
        message: "Transport mode is required",
      });
    }

    // =========================
    // CHECK DUPLICATE AGENCY
    // =========================

    const existingAgency = await client.query(
      `
      SELECT id
      FROM transport_agencies
      WHERE
        LOWER(agency_name) = LOWER($1)
        AND org_id = $2
        AND COALESCE(is_deleted,false) = false
      LIMIT 1
      `,
      [agency_name.trim(), org_id]
    );

    if (existingAgency.rows.length > 0) {
      return res.status(400).json({
        success: false,
        message: "Transport agency already exists",
      });
    }

    // =========================
    // CREATE AGENCY
    // =========================

    const agencyResult = await client.query(
      `
      INSERT INTO transport_agencies (
        org_id,
        agency_name,
        contact_person,
        phone_number,
        email_address,
        gst_number,
        address,
        transport_mode,
        created_at,
        updated_at
      )
      VALUES (
        $1,$2,$3,$4,$5,$6,$7,$8,
        CURRENT_TIMESTAMP,
        CURRENT_TIMESTAMP
      )
      RETURNING *;
      `,
      [
        org_id,
        agency_name.trim(),
        contact_person || null,
        phone_number || null,
        email_address || null,
        gst_number || null,
        address || null,
        transport_mode,
      ]
    );

    const agency = agencyResult.rows[0];

    // =========================
    // FILES
    // =========================

    const uploadedFiles = req.files || [];

    // =========================
    // STORAGE ARRAYS
    // =========================

    const insertedVehicles = [];
    const insertedDrivers = [];

    // =========================
    // LOOP VEHICLES
    // =========================

    for (const [index, vehicle] of vehicles.entries()) {

      // =========================
      // VEHICLE VALIDATION
      // =========================

      if (!vehicle.vehicle_name?.trim()) {
        throw new Error(
          `Vehicle name required at row ${index + 1}`
        );
      }

      if (!vehicle.vehicle_number?.trim()) {
        throw new Error(
          `Vehicle number required at row ${index + 1}`
        );
      }

      if (!vehicle.vehicle_type?.trim()) {
        throw new Error(
          `Vehicle type required at row ${index + 1}`
        );
      }

      // =========================
      // DRIVER SECTION
      // =========================

      let driverId = null;
      let driverObject = null;

      const hasDriverData =
        vehicle.driver_name ||
        vehicle.driver_contact_number ||
        vehicle.license_number;

      if (hasDriverData) {

        // =========================
        // DRIVER VALIDATION
        // =========================

        if (
          !vehicle.driver_name?.trim() ||
          !vehicle.driver_contact_number?.trim() ||
          !vehicle.license_number?.trim()
        ) {
          throw new Error(
            `Incomplete driver details at row ${index + 1}`
          );
        }

        // =========================
        // CHECK EXISTING DRIVER
        // =========================

        const existingDriver = await client.query(
          `
          SELECT *
          FROM transport_drivers
          WHERE
            driver_contact_number = $1
            AND org_id = $2
            AND COALESCE(is_deleted,false) = false
          LIMIT 1
          `,
          [
            vehicle.driver_contact_number,
            org_id,
          ]
        );

        // =========================
        // REUSE DRIVER
        // =========================

        if (existingDriver.rows.length > 0) {

          driverObject = existingDriver.rows[0];

          driverId = driverObject.id;

        } else {

          // =========================
          // CREATE DRIVER
          // =========================

          const driverResult = await client.query(
            `
            INSERT INTO transport_drivers (
              transport_agency_id,
              org_id,
              driver_name,
              driver_contact_number,
              driver_license_number,
              is_available,
              created_at,
              updated_at
            )
            VALUES (
              $1,$2,$3,$4,$5,
              true,
              CURRENT_TIMESTAMP,
              CURRENT_TIMESTAMP
            )
            RETURNING *;
            `,
            [
              agency.id,
              org_id,
              vehicle.driver_name.trim(),
              vehicle.driver_contact_number.trim(),
              vehicle.license_number.trim(),
            ]
          );

          driverObject = driverResult.rows[0];

          driverId = driverObject.id;
        }

        // =========================
        // PUSH DRIVER
        // =========================

        const alreadyAdded = insertedDrivers.find(
          (d) => d.id === driverObject.id
        );

        if (!alreadyAdded) {
          insertedDrivers.push(driverObject);
        }
      }

      // =========================
      // VEHICLE DOCUMENT
      // =========================

      const vehicleDocument =
        uploadedFiles[index]?.filename || null;

      // =========================
      // CREATE VEHICLE
      // =========================

      const vehicleResult = await client.query(
        `
        INSERT INTO transport_vehicles (
          transport_agency_id,
          vehicle_name,
          vehicle_number,
          vehicle_type,
          capacity_tons,
          driver_id,

          driver_name,
          driver_contact_number,
          license_number,

          vehicle_document,

          status,
          is_available,

          created_at,
          updated_at,
          is_deleted
        )
        VALUES (
          $1,
          $2,
          $3,
          $4,
          $5,
          $6,

          $7,
          $8,
          $9,

          $10,

          'Active',
          true,

          CURRENT_TIMESTAMP,
          CURRENT_TIMESTAMP,
          false
        )
        RETURNING *;
        `,
        [
          agency.id,

          vehicle.vehicle_name.trim(),
          vehicle.vehicle_number.trim(),
          vehicle.vehicle_type.trim(),
          vehicle.capacity_tons || null,

          driverId,

          vehicle.driver_name || null,
          vehicle.driver_contact_number || null,
          vehicle.license_number || null,

          vehicleDocument,
        ]
      );

      // =========================
      // VEHICLE OBJECT
      // =========================

      const vehicleObject = {
        ...vehicleResult.rows[0],

        driver: driverObject
          ? {
              id: driverObject.id,
              driver_name: driverObject.driver_name,
              driver_contact_number:
                driverObject.driver_contact_number,
              driver_license_number:
                driverObject.driver_license_number,
            }
          : null,

        availability_message: "Available",
      };

      insertedVehicles.push(vehicleObject);
    }

    // =========================
    // FINAL TRANSPORT OBJECT
    // =========================

    const createdTransport = {
      ...agency,

      vehicles: insertedVehicles,

      drivers: insertedDrivers,
    };

    // =========================
    // COMMIT
    // =========================

    await client.query("COMMIT");

    return res.status(201).json({
      success: true,
      message: "Transport agency created successfully",
      data: createdTransport,
    });

  } catch (error) {

    await client.query("ROLLBACK");

    console.error(
      "CREATE TRANSPORT ERROR:",
      error
    );

    return res.status(500).json({
      success: false,
      message: error.message || "Internal server error",
    });

  } finally {

    client.release();

  }
};



const getTransportAgencyList = async (req, res) => {
  try {

    const org_id = req.session.user.orgId;

    const {
      page = 1,
      limit = 10,
      search = "",
      sort_by = "created_at",
      sort_order = "DESC",
    } = req.query;

    // =========================
    // PAGINATION
    // =========================

    const currentPage = Number(page);

    const perPage = Number(limit);

    const offset = (currentPage - 1) * perPage;

    // =========================
    // ALLOWED SORT FIELDS
    // =========================

    const allowedSortFields = {
      agency_name: "ta.agency_name",
      contact_person: "ta.contact_person",
      phone_number: "ta.phone_number",
      email_address: "ta.email_address",
      created_at: "ta.created_at",
      total_capacity: "total_capacity",
      total_vehicles: "total_vehicles",
    };

    const finalSortBy =
      allowedSortFields[sort_by] || "ta.created_at";

    const finalSortOrder =
      sort_order.toUpperCase() === "ASC"
        ? "ASC"
        : "DESC";

    // =========================
    // WHERE CONDITIONS
    // =========================

    let whereClause = `
      WHERE ta.is_deleted = false
      AND ta.org_id = $1
    `;

    let values = [org_id];

    let count = 2;

    // =========================
    // SEARCH
    // =========================

    if (search) {

      whereClause += `
        AND (
          ta.agency_name ILIKE $${count}
          OR ta.contact_person ILIKE $${count}
          OR ta.phone_number ILIKE $${count}
          OR ta.email_address ILIKE $${count}
          OR ta.gst_number ILIKE $${count}
          OR tv.vehicle_type ILIKE $${count}
          OR tv.vehicle_number ILIKE $${count}
          OR td.driver_name ILIKE $${count}
          OR td.driver_contact_number ILIKE $${count}
        )
      `;

      values.push(`%${search}%`);

      count++;
    }

    // =========================
    // TOTAL COUNT
    // =========================

    const totalQuery = `
      SELECT COUNT(DISTINCT ta.id)

      FROM transport_agencies ta

      LEFT JOIN transport_vehicles tv
        ON tv.transport_agency_id = ta.id
        AND tv.is_deleted = false

      LEFT JOIN transport_drivers td
        ON td.id = tv.driver_id
        AND td.is_deleted = false

      ${whereClause}
    `;

    const totalResult = await pool.query(
      totalQuery,
      values
    );

    // =========================
    // MAIN QUERY
    // =========================

    const listQuery = `
      SELECT

        ta.id,
        ta.agency_name,
        ta.contact_person,
        ta.phone_number,
        ta.email_address,
        ta.gst_number,
        ta.address,
        ta.transport_mode,
        ta.created_at,

        COUNT(DISTINCT tv.id)
          AS total_vehicles,

        COALESCE(
          SUM(tv.capacity_tons),
          0
        ) AS total_capacity,

        COALESCE(
          STRING_AGG(
            DISTINCT tv.vehicle_type,
            ', '
          ),
          '-'
        ) AS vehicle_types,

        COALESCE(
          STRING_AGG(
            DISTINCT td.driver_name,
            ', '
          ),
          '-'
        ) AS drivers,

        CASE
          WHEN COUNT(tv.id) > 0
          THEN 'Active'
          ELSE 'Inactive'
        END AS status

      FROM transport_agencies ta

      LEFT JOIN transport_vehicles tv
        ON tv.transport_agency_id = ta.id
        AND tv.is_deleted = false

      LEFT JOIN transport_drivers td
        ON td.id = tv.driver_id
        AND td.is_deleted = false

      ${whereClause}

      GROUP BY
        ta.id,
        ta.agency_name,
        ta.contact_person,
        ta.phone_number,
        ta.email_address,
        ta.gst_number,
        ta.address,
        ta.transport_mode,
        ta.created_at

      ORDER BY ${finalSortBy} ${finalSortOrder}

      LIMIT $${count}
      OFFSET $${count + 1}
    `;

    const finalValues = [
      ...values,
      perPage,
      offset,
    ];

    const result = await pool.query(
      listQuery,
      finalValues
    );

    // =========================
    // STATISTICS
    // =========================

    const statsQuery = `
      SELECT

        COUNT(DISTINCT ta.id)
          AS total_agencies,

        COUNT(
          DISTINCT CASE
            WHEN tv.id IS NOT NULL
            THEN ta.id
          END
        ) AS active_agencies,

        COUNT(DISTINCT tv.id)
          AS total_vehicles,

        COALESCE(
          SUM(tv.capacity_tons),
          0
        ) AS total_capacity

      FROM transport_agencies ta

      LEFT JOIN transport_vehicles tv
        ON tv.transport_agency_id = ta.id
        AND tv.is_deleted = false

      WHERE ta.is_deleted = false
      AND ta.org_id = $1
    `;

    const statsResult = await pool.query(
      statsQuery,
      [org_id]
    );

    // =========================
    // RESPONSE
    // =========================

    return res.status(200).json({
      success: true,

      message:
        "Transport agencies fetched successfully",

      data: result.rows,

      pagination: {
        total_records: Number(
          totalResult.rows[0].count
        ),

        current_page: currentPage,

        per_page: perPage,

        total_pages: Math.ceil(
          totalResult.rows[0].count / perPage
        ),
      },

      statistics: statsResult.rows[0],
    });

  } catch (error) {

    console.error(
      "GET TRANSPORT LIST ERROR:",
      error
    );

    return res.status(500).json({
      success: false,
      message: "Internal server error",
      error: error.message,
    });

  }
};


// const getTransportAgencyDropdown = async (req, res) => {
//   try {
//     const org_id = req.session.user.orgId;

//     const query = `
//       SELECT
//         ta.id,
//         ta.agency_name,

//         COALESCE(
//           json_agg(
//             json_build_object(
//               'id', tv.id,
//               'vehicle_name', tv.vehicle_name,
//               'vehicle_number', tv.vehicle_number,
//               'vehicle_type', tv.vehicle_type,
//               'capacity_tons', tv.capacity_tons,
//               'driver_name', tv.driver_name,
//               'driver_contact_number', tv.driver_contact_number,
//               'driver_license_number', tv.driver_license_number
//             )
//           ) FILTER (WHERE tv.id IS NOT NULL),
//           '[]'
//         ) AS vehicles

//       FROM transport_agencies ta

//       LEFT JOIN transport_vehicles tv
//       ON tv.transport_agency_id = ta.id

//       WHERE ta.org_id = $1
//       AND ta.is_deleted = false

//       GROUP BY ta.id

//       ORDER BY ta.agency_name ASC
//     `;

//     const result = await pool.query(query, [org_id]);

//     return res.status(200).json({
//       success: true,
//       message: "Transport dropdown fetched successfully",
//       data: result.rows,
//     });
//   } catch (error) {
//     console.error("TRANSPORT DROPDOWN ERROR:", error);

//     return res.status(500).json({
//       success: false,
//       message: "Internal server error",
//       error: error.message,
//     });
//   }
// };

const getTransportAgencyDropdown = async (req, res) => {
  try {

    const org_id = req.session.user.orgId;

    const query = `
      SELECT

        ta.id,

        ta.agency_name,

        ta.transport_mode,

        /* ========================================= */
        /* VEHICLES */
        /* ========================================= */

        COALESCE(

          json_agg(

            DISTINCT jsonb_build_object(

              'id', tv.id,

              'vehicle_name',
              tv.vehicle_name,

              'vehicle_number',
              tv.vehicle_number,

              'vehicle_type',
              tv.vehicle_type,

              'capacity_tons',
              tv.capacity_tons,

              'vehicle_document',
              tv.vehicle_document,

              'driver', CASE
                WHEN td.id IS NOT NULL
                THEN jsonb_build_object(

                  'id', td.id,

                  'driver_name',
                  td.driver_name,

                  'driver_contact_number',
                  td.driver_contact_number,

                  'driver_license_number',
                  td.driver_license_number,

                  'is_available',
                  td.is_available

                )
                ELSE NULL
              END

            )

          ) FILTER (
            WHERE tv.id IS NOT NULL
          ),

          '[]'

        ) AS vehicles,

        /* ========================================= */
        /* DRIVERS */
        /* ========================================= */

        COALESCE(

          (
            SELECT json_agg(

              json_build_object(

                'id', td2.id,

                'driver_name',
                td2.driver_name,

                'driver_contact_number',
                td2.driver_contact_number,

                'driver_license_number',
                td2.driver_license_number,

                'is_available',
                td2.is_available

              )

            )

            FROM transport_drivers td2

            WHERE td2.transport_agency_id = ta.id

            AND td2.is_deleted = false

            AND td2.is_available = true
          ),

          '[]'

        ) AS drivers

      FROM transport_agencies ta

      LEFT JOIN transport_vehicles tv
        ON tv.transport_agency_id = ta.id
        AND tv.is_deleted = false

      LEFT JOIN transport_drivers td
        ON td.id = tv.driver_id
        AND td.is_deleted = false

      WHERE ta.org_id = $1

      AND ta.is_deleted = false

      GROUP BY
        ta.id,
        ta.agency_name,
        ta.transport_mode

      ORDER BY ta.agency_name ASC
    `;

    const result = await pool.query(query, [org_id]);

    return res.status(200).json({
      success: true,

      message:
        "Transport dropdown fetched successfully",

      data: result.rows,
    });

  } catch (error) {

    console.error(
      "TRANSPORT DROPDOWN ERROR:",
      error
    );

    return res.status(500).json({
      success: false,
      message: "Internal server error",
      error: error.message,
    });

  }
};


const getTransportStatistics = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;

    // =========================
    // STATS QUERY
    // =========================

    const statsQuery = `
      SELECT

        COUNT(DISTINCT ta.id)
        AS total_agencies,

        COUNT(
          DISTINCT CASE
            WHEN tv.id IS NOT NULL
            THEN ta.id
          END
        ) AS active_agencies,

        COUNT(
          DISTINCT CASE
            WHEN tv.id IS NULL
            THEN ta.id
          END
        ) AS inactive_agencies,

        COUNT(tv.id)
        AS vehicles_attached

      FROM transport_agencies ta

      LEFT JOIN transport_vehicles tv
      ON tv.transport_agency_id = ta.id

      WHERE ta.is_deleted = false
      AND ta.org_id = $1
    `;

    const result = await pool.query(statsQuery, [org_id]);

    return res.status(200).json({
      success: true,

      message: "Transport statistics fetched successfully",

      data: {
        total_agencies: Number(result.rows[0].total_agencies),

        active_agencies: Number(result.rows[0].active_agencies),

        inactive_agencies: Number(result.rows[0].inactive_agencies),

        vehicles_attached: Number(result.rows[0].vehicles_attached),
      },
    });
  } catch (error) {
    console.error("GET TRANSPORT STATS ERROR:", error);

    return res.status(500).json({
      success: false,

      message: "Internal server error",

      error: error.message,
    });
  }
};

const deleteTransportAgency = async (req, res) => {
  const client = await pool.connect();

  try {
    await client.query("BEGIN");

    const org_id = req.session.user.orgId;

    const userId = req.session.user.id;

    const { id } = req.params;

    // =========================================
    // CHECK EXISTS
    // =========================================

    const checkResult = await client.query(
      `
      SELECT *
      FROM transport_agencies
      WHERE id = $1
      AND org_id = $2
      AND is_deleted = false
      `,
      [id, org_id],
    );

    if (checkResult.rows.length === 0) {
      await client.query("ROLLBACK");

      return res.status(404).json({
        success: false,
        message: "Transport agency not found",
      });
    }

    // =========================================
    // SOFT DELETE AGENCY
    // =========================================

    await client.query(
      `
      UPDATE transport_agencies
      SET

          is_deleted = true,

          updated_by = $1,

          updated_at = CURRENT_TIMESTAMP

      WHERE id = $2
      `,
      [userId, id],
    );

    // =========================================
    // OPTIONAL VEHICLE SOFT DELETE
    // =========================================

    await client.query(
      `
      UPDATE transport_vehicles
      SET

          is_deleted = true,

          updated_at = CURRENT_TIMESTAMP

      WHERE transport_agency_id = $1
      `,
      [id],
    );

    await client.query("COMMIT");

    return res.status(200).json({
      success: true,
      message: "Transport agency deleted successfully",
    });
  } catch (error) {
    await client.query("ROLLBACK");

    console.error("DELETE TRANSPORT ERROR:", error);

    return res.status(500).json({
      success: false,
      message: "Failed to delete transport agency",
      error: error.message,
    });
  } finally {
    client.release();
  }
};

const addDriver = async (req, res) => {
  const client = await pool.connect();

  try {

    await client.query("BEGIN");

    const {
      transport_agency_id,
      driver_name,
      driver_contact_number,
      driver_license_number,
    } = req.body;

    // =========================
    // VALIDATIONS
    // =========================

    if (!transport_agency_id) {
      return res.status(400).json({
        success: false,
        message: "Transport agency is required",
      });
    }

    if (!driver_name?.trim()) {
      return res.status(400).json({
        success: false,
        message: "Driver name is required",
      });
    }

    if (!driver_contact_number?.trim()) {
      return res.status(400).json({
        success: false,
        message: "Driver contact number is required",
      });
    }

    if (!/^\d{10}$/.test(driver_contact_number)) {
      return res.status(400).json({
        success: false,
        message: "Invalid driver contact number",
      });
    }

    if (!driver_license_number?.trim()) {
      return res.status(400).json({
        success: false,
        message: "Driver license number is required",
      });
    }

    // =========================
    // CHECK AGENCY
    // =========================

    const agencyCheck = await client.query(
      `
      SELECT id, agency_name
      FROM transport_agencies
      WHERE id = $1
      AND is_deleted = false
      `,
      [transport_agency_id]
    );

    if (agencyCheck.rows.length === 0) {
      return res.status(404).json({
        success: false,
        message: "Transport agency not found",
      });
    }

    // =========================
    // DUPLICATE CONTACT CHECK
    // =========================

    const duplicateContactCheck =
      await client.query(
        `
        SELECT id
        FROM transport_drivers
        WHERE transport_agency_id = $1
        AND driver_contact_number = $2
        AND is_deleted = false
        `,
        [
          transport_agency_id,
          driver_contact_number,
        ]
      );

    if (duplicateContactCheck.rows.length > 0) {
      return res.status(400).json({
        success: false,
        message:
          "Driver already exists with this contact number",
      });
    }

    // =========================
    // DUPLICATE LICENSE CHECK
    // =========================

    const duplicateLicenseCheck =
      await client.query(
        `
        SELECT id
        FROM transport_drivers
        WHERE driver_license_number = $1
        AND is_deleted = false
        `,
        [driver_license_number]
      );

    if (duplicateLicenseCheck.rows.length > 0) {
      return res.status(400).json({
        success: false,
        message:
          "Driver license number already exists",
      });
    }

    // =========================
    // INSERT DRIVER
    // =========================

    const result = await client.query(
      `
      INSERT INTO transport_drivers
      (
        transport_agency_id,
        driver_name,
        driver_contact_number,
        driver_license_number
      )
      VALUES ($1,$2,$3,$4)
      RETURNING *;
      `,
      [
        transport_agency_id,
        driver_name.trim(),
        driver_contact_number.trim(),
        driver_license_number.trim(),
      ]
    );

    await client.query("COMMIT");

    return res.status(201).json({
      success: true,
      message: "Driver added successfully",
      data: result.rows[0],
    });

  } catch (err) {

    await client.query("ROLLBACK");

    console.error("ADD DRIVER ERROR:", err);

    return res.status(500).json({
      success: false,
      message: "Failed to add driver",
      error: err.message,
    });

  } finally {
    client.release();
  }
};


const getTransportAgenciesByMode = async (req, res) => {
  try {

    const org_id = req.session.user.orgId;

    const { transport_mode } = req.query;

    // =========================
    // VALIDATION
    // =========================

    if (!transport_mode) {
      return res.status(400).json({
        success: false,
        message: "Transport mode is required",
      });
    }

    // =========================
    // MAIN QUERY
    // =========================

    const query = `
      SELECT

        ta.id,
        ta.agency_name,
        ta.contact_person,
        ta.phone_number,
        ta.email_address,
        ta.transport_mode,

        /* ===================================== */
        /* VEHICLES */
        /* ===================================== */

        COALESCE(

          json_agg(

            DISTINCT jsonb_build_object(

              'id', tv.id,

              'vehicle_name',
              tv.vehicle_name,

              'vehicle_number',
              tv.vehicle_number,

              'vehicle_type',
              tv.vehicle_type,

              'capacity_tons',
              tv.capacity_tons,

              'vehicle_document',
              tv.vehicle_document,

              'expected_delivery_datetime',
              sd.expected_delivery_datetime,

              'is_available',

              CASE
                WHEN sd.expected_delivery_datetime IS NOT NULL
                 AND sd.expected_delivery_datetime >= NOW()
                THEN false
                ELSE true
              END,

              'availability_message',

              CASE
                WHEN sd.expected_delivery_datetime IS NOT NULL
                 AND sd.expected_delivery_datetime >= NOW()
                THEN
                  'Vehicle already assigned till ' ||

                  TO_CHAR(
                    sd.expected_delivery_datetime,
                    'DD Mon YYYY'
                  )

                ELSE
                  'Available'
              END,

              'driver',

              CASE
                WHEN td.id IS NOT NULL
                THEN jsonb_build_object(

                  'id', td.id,

                  'driver_name',
                  td.driver_name,

                  'driver_contact_number',
                  td.driver_contact_number,

                  'driver_license_number',
                  td.driver_license_number,

                  'is_available',
                  td.is_available

                )

                ELSE NULL
              END

            )

          ) FILTER (
            WHERE tv.id IS NOT NULL
          ),

          '[]'

        ) AS vehicles,

        /* ===================================== */
        /* AVAILABLE DRIVERS */
        /* ===================================== */

        COALESCE(

          (
            SELECT json_agg(

              json_build_object(

                'id', td2.id,

                'driver_name',
                td2.driver_name,

                'driver_contact_number',
                td2.driver_contact_number,

                'driver_license_number',
                td2.driver_license_number,

                'is_available',
                td2.is_available

              )

            )

            FROM transport_drivers td2

            WHERE td2.transport_agency_id = ta.id

            AND td2.is_deleted = false

            AND td2.is_available = true

          ),

          '[]'

        ) AS drivers

      FROM transport_agencies ta

      LEFT JOIN transport_vehicles tv
        ON tv.transport_agency_id = ta.id
        AND tv.is_deleted = false

      LEFT JOIN transport_drivers td
        ON td.id = tv.driver_id
        AND td.is_deleted = false

      /* ===================================== */
      /* VEHICLE ASSIGNMENT CHECK */
      /* ===================================== */

      LEFT JOIN LATERAL (

        SELECT
          sd.expected_delivery_datetime

        FROM shipment_dispatches sd

        WHERE sd.vehicle_number = tv.vehicle_number

        AND sd.shipment_status != 'Delivered'

        ORDER BY sd.expected_delivery_datetime DESC

        LIMIT 1

      ) sd ON true

      WHERE ta.is_deleted = false

      AND ta.org_id = $1

      AND ta.transport_mode = $2

      GROUP BY
        ta.id,
        ta.agency_name,
        ta.contact_person,
        ta.phone_number,
        ta.email_address,
        ta.transport_mode

      ORDER BY ta.agency_name ASC
    `;

    const result = await pool.query(query, [
      org_id,
      transport_mode,
    ]);

    return res.status(200).json({
      success: true,
      data: result.rows,
    });

  } catch (error) {

    console.error(
      "GET TRANSPORT BY MODE ERROR:",
      error
    );

    return res.status(500).json({
      success: false,
      message: "Internal server error",
      error: error.message,
    });

  }
};


const updateTransportAgency = async (req, res) => {

  const client = await pool.connect();

  try {

    await client.query("BEGIN");

    const org_id = req.session.user.orgId;

    const { id } = req.params;
    // console.log(id, "vehicle id not getting")

    const {
      agency_name,
      contact_person,
      phone_number,
      email_address,
      gst_number,
      address,
      transport_mode,
    } = req.body;

    const vehicles = req.body.vehicles
      ? JSON.parse(req.body.vehicles)
      : [];

    // =========================
    // VALIDATE AGENCY
    // =========================

    const existingAgency = await client.query(
      `
      SELECT id
      FROM transport_agencies
      WHERE id = $1
      `,
      [id]
    );

    if (existingAgency.rows.length === 0) {

      await client.query("ROLLBACK");

      return res.status(404).json({
        success: false,
        message: "Transport agency not found",
      });
    }

    // =========================
    // UPDATE AGENCY
    // =========================

    const agencyResult = await client.query(
      `
      UPDATE transport_agencies
      SET
        agency_name = $1,
        contact_person = $2,
        phone_number = $3,
        email_address = $4,
        gst_number = $5,
        address = $6,
        transport_mode = $7,
        updated_at = NOW()
      WHERE id = $8
      RETURNING *
      `,
      [
        agency_name,
        contact_person,
        phone_number,
        email_address,
        gst_number,
        address,
        transport_mode,
        id,
      ]
    );

    // =========================
    // FILES
    // =========================

    const uploadedFiles = req.files || [];

    const finalVehicles = [];

    // =========================
    // LOOP VEHICLES
    // =========================

    for (const [index, vehicle] of vehicles.entries()) {

      // =========================
      // VALIDATE VEHICLE
      // =========================

      if (!vehicle.vehicle_name) {
        throw new Error(
          `Vehicle name required at row ${index + 1}`
        );
      }

      if (!vehicle.vehicle_number) {
        throw new Error(
          `Vehicle number required at row ${index + 1}`
        );
      }

      // =========================
      // CHECK DUPLICATE VEHICLE
      // =========================

      const duplicateVehicle = await client.query(
        `
        SELECT id
        FROM transport_vehicles
        WHERE vehicle_number = $1
        AND id != $2
        LIMIT 1
        `,
        [
          vehicle.vehicle_number,
          vehicle.id || 0,
        ]
      );

      if (duplicateVehicle.rows.length > 0) {

        throw new Error(
          `Vehicle number already exists`
        );
      }

      // =========================
      // DRIVER
      // =========================

      let driverId = vehicle.driver_id || null;

      const hasDriverData =
        vehicle.driver_name &&
        vehicle.driver_contact_number &&
        vehicle.license_number;

      // =========================
      // UPDATE EXISTING DRIVER
      // =========================

      if (driverId && hasDriverData) {

        // CHECK DUPLICATE PHONE
        const duplicateDriver = await client.query(
          `
          SELECT id
          FROM transport_drivers
          WHERE driver_contact_number = $1
          AND id != $2
          LIMIT 1
          `,
          [
            vehicle.driver_contact_number,
            driverId,
          ]
        );

        if (duplicateDriver.rows.length > 0) {

          throw new Error(
            `Driver phone already exists`
          );
        }

        await client.query(
          `
          UPDATE transport_drivers
          SET
            driver_name = $1,
            driver_contact_number = $2,
            driver_license_number = $3
          WHERE id = $4
          `,
          [
            vehicle.driver_name,
            vehicle.driver_contact_number,
            vehicle.license_number,
            driverId,
          ]
        );
      }

      // =========================
      // CREATE DRIVER
      // =========================

      else if (!driverId && hasDriverData) {

        // REUSE EXISTING DRIVER
        const existingDriver = await client.query(
          `
          SELECT id
          FROM transport_drivers
          WHERE driver_contact_number = $1
          LIMIT 1
          `,
          [
            vehicle.driver_contact_number,
          ]
        );

        if (existingDriver.rows.length > 0) {

          driverId = existingDriver.rows[0].id;

        } else {

          // INSERT DRIVER
          const driverResult = await client.query(
            `
            INSERT INTO transport_drivers (
              transport_agency_id,
              org_id,
              driver_name,
              driver_contact_number,
              driver_license_number
            )
            VALUES ($1,$2,$3,$4,$5)
            RETURNING id
            `,
            [
              id,
              org_id,
              vehicle.driver_name,
              vehicle.driver_contact_number,
              vehicle.license_number,
            ]
          );

          driverId = driverResult.rows[0].id;
        }
      }

      // =========================
      // VEHICLE DOCUMENT
      // =========================

      const vehicleDocument =
        uploadedFiles[index]?.filename ||
        vehicle.vehicle_document ||
        null;

      // =========================
      // UPDATE VEHICLE
      // =========================

      if (vehicle.id) {

        const vehicleResult = await client.query(
          `
          UPDATE transport_vehicles
          SET
            vehicle_name = $1,
            vehicle_number = $2,
            vehicle_type = $3,
            capacity_tons = $4,

            driver_id = $5,

            driver_name = $6,
            driver_contact_number = $7,
            license_number = $8,

            vehicle_document = $9,

            updated_at = NOW()

          WHERE id = $10

          RETURNING *
          `,
          [
            vehicle.vehicle_name,
            vehicle.vehicle_number,
            vehicle.vehicle_type,
            vehicle.capacity_tons,

            driverId,

            vehicle.driver_name,
            vehicle.driver_contact_number,
            vehicle.license_number,

            vehicleDocument,

            vehicle.id,
          ]
        );

        finalVehicles.push(vehicleResult.rows[0]);

      }

      // =========================
      // INSERT VEHICLE
      // =========================

      else {

        const vehicleResult = await client.query(
          `
          INSERT INTO transport_vehicles (
            transport_agency_id,

            vehicle_name,
            vehicle_number,
            vehicle_type,
            capacity_tons,

            driver_id,

            driver_name,
            driver_contact_number,
            license_number,

            vehicle_document
          )
          VALUES (
            $1,$2,$3,$4,$5,
            $6,$7,$8,$9,$10
          )
          RETURNING *
          `,
          [
            id,

            vehicle.vehicle_name,
            vehicle.vehicle_number,
            vehicle.vehicle_type,
            vehicle.capacity_tons,

            driverId,

            vehicle.driver_name,
            vehicle.driver_contact_number,
            vehicle.license_number,

            vehicleDocument,
          ]
        );

        finalVehicles.push(vehicleResult.rows[0]);
      }
    }

    // =========================
    // COMMIT
    // =========================

    await client.query("COMMIT");

    return res.status(200).json({
      success: true,
      message: "Transport agency updated successfully",

      data: {
        agency: agencyResult.rows[0],
        vehicles: finalVehicles,
      },
    });

  } catch (error) {

    await client.query("ROLLBACK");

    console.error(
      "UPDATE TRANSPORT ERROR:",
      error
    );

    return res.status(500).json({
      success: false,
      message:
        error.message || "Internal server error",
    });

  } finally {

    client.release();

  }
};




const getTransportAgencyById = async (req, res) => {

  const client = await pool.connect();

  try {

    const { id } = req.params;

    // =========================
    // GET AGENCY
    // =========================

    const agencyResult = await client.query(
      `
      SELECT
        id,
        org_id,
        agency_name,
        contact_person,
        phone_number,
        email_address,
        gst_number,
        address,
        transport_mode,
        created_at,
        updated_at
      FROM transport_agencies
      WHERE id = $1
      AND is_deleted = false
      `,
      [id]
    );

    if (agencyResult.rows.length === 0) {

      return res.status(404).json({
        success: false,
        message: "Transport agency not found",
      });
    }

    // =========================
    // GET VEHICLES + DRIVERS
    // =========================

    const vehicleResult = await client.query(
      `
      SELECT

        -- VEHICLE
        tv.id AS vehicle_id,

        tv.transport_agency_id,

        tv.vehicle_name,
        tv.vehicle_number,
        tv.vehicle_type,
        tv.capacity_tons,

        tv.vehicle_document,

        tv.driver_id,

        tv.created_at,
        tv.updated_at,

        -- DRIVER
        td.id AS driver_id,

        td.driver_name,
        td.driver_contact_number,
        td.driver_license_number,

        td.is_available

      FROM transport_vehicles tv

      LEFT JOIN transport_drivers td
        ON td.id = tv.driver_id
        AND td.is_deleted = false

      WHERE tv.transport_agency_id = $1
      AND tv.is_deleted = false

      ORDER BY tv.id ASC
      `,
      [id]
    );

    return res.status(200).json({
      success: true,

      data: {
        agency: agencyResult.rows[0],

        vehicles: vehicleResult.rows,
      },
    });

  } catch (error) {

    console.error(
      "GET TRANSPORT AGENCY ERROR:",
      error
    );

    return res.status(500).json({
      success: false,
      message: "Internal server error",
      error: error.message,
    });

  } finally {

    client.release();

  }
};



module.exports = {
  createTransportAgency,
  getTransportAgencyList,
  getTransportStatistics,
  getTransportAgencyDropdown,
  deleteTransportAgency,
  addDriver,
  getTransportAgenciesByMode,
  updateTransportAgency,
  getTransportAgencyById,
};
