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

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

  const org_id = req.session.user.orgId;
  const userId = req.session.user.id;

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

    const {
      agency_name,
      contact_person,
      phone_number,
      email_address,
      gst_number,
      address,
      transport_mode
    //   vehicles = [],
    } = 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",
      });
    }

    if (!org_id) {
      return res.status(400).json({
        success: false,
        message: "Org ID is required",
      });
    }

    // =========================
    // INSERT 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];

    // =========================
    // INSERT VEHICLES
    // =========================

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

    for (const [index, vehicle] of vehicles.entries()) {
      const vehicleResult = await client.query(
        `
        INSERT INTO transport_vehicles (
          transport_agency_id,
          vehicle_name,
          vehicle_number,
          vehicle_type,
          capacity_tons,
          driver_name,
          driver_contact_number,
          license_number,
          vehicle_document
        )
        VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9)
        RETURNING *;
        `,
        [
          agency.id,
          vehicle.vehicle_name,
          vehicle.vehicle_number,
          vehicle.vehicle_type,
          vehicle.capacity_tons,
          vehicle.driver_name,
          vehicle.driver_contact_number,
          vehicle.license_number,
          uploadedFiles[index]?.filename || null
        ]
      );

      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 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",
      vehicle_type:
        "tv.vehicle_type",
      capacity_tons:
        "tv.capacity_tons",
      created_at:
        "ta.created_at",
    };

    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 tv.driver_name ILIKE $${count}
        )
      `;

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

      count++;
    }

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

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

      FROM transport_agencies ta

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

      ${whereClause}
    `;

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

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

    const listQuery = `
      SELECT
        ta.id,
        ta.agency_name,
        ta.contact_person,
        ta.phone_number,
        ta.email_address,
        ta.gst_number,
        ta.address,
        ta.created_at,

        tv.id AS vehicle_id,
        tv.vehicle_name,
        tv.vehicle_number,
        tv.vehicle_type,
        tv.capacity_tons,
        tv.driver_name,
        tv.driver_contact_number,
        tv.vehicle_document,

        CASE
          WHEN tv.id IS NOT NULL
          THEN 'Active'
          ELSE 'Inactive'
        END AS status

      FROM transport_agencies ta

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

      ${whereClause}

      ORDER BY ${finalSortBy} ${finalSortOrder}

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

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

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

    // =========================
    // STATISTICS 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(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

      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,

        /* ================================================= */
        /* 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,

              '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,

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

        COALESCE(

          (

            SELECT json_agg(

              json_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

              )

            )

            FROM transport_drivers td

            WHERE
              td.transport_agency_id = ta.id

            AND td.is_available = true

          ),

          '[]'

        ) AS drivers

      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 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) => {
  try {
    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 (!driver_license_number?.trim()) {
      return res.status(400).json({
        success: false,
        message: "Driver license number is required",
      });
    }

    // =====================================================
    // CHECK TRANSPORT AGENCY
    // IMPORTANT:
    // USE SAME TABLE AS /transport/dropdown API
    // =====================================================

    console.log(
      transport_agency_id,
      "incoming transport agency id",
    );

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

    console.log(agencyCheck.rows, "agency rows");

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

    // =====================================================
    // DUPLICATE DRIVER CHECK
    // =====================================================

    const duplicateCheck = await pool.query(
      `
      SELECT id
      FROM transport_drivers
      WHERE transport_agency_id = $1
      AND driver_contact_number = $2
      `,
      [transport_agency_id, driver_contact_number],
    );

    if (duplicateCheck.rows.length > 0) {
      return res.status(400).json({
        success: false,
        message: "Driver already exists for this transporter",
      });
    }

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

    const result = await pool.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(),
      ],
    );

    res.status(201).json({
      success: true,
      message: "Driver added successfully",
      data: result.rows[0],
    });
  } catch (err) {
    console.error("ADD DRIVER ERROR:", err);

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


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

    const { transport_mode } = req.query;

    const agencyQuery = `
      SELECT *
      FROM transport_agencies
      WHERE is_deleted = false
      AND org_id = $1
      AND transport_mode = $2
      ORDER BY agency_name ASC
    `;

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

    const agencies = await Promise.all(
      agencyResult.rows.map(async (agency) => {

const vehiclesResult = await pool.query(
  `
  SELECT
    tv.*,

    sd.expected_delivery_datetime,

    CASE
      WHEN sd.expected_delivery_datetime IS NOT NULL
       AND sd.expected_delivery_datetime >= CURRENT_DATE
      THEN false
      ELSE true
    END AS is_available,

    CASE
      WHEN sd.expected_delivery_datetime IS NOT NULL
       AND sd.expected_delivery_datetime >= CURRENT_DATE
      THEN
        'Vehicle already assigned till ' ||
        TO_CHAR(sd.expected_delivery_datetime, 'DD Mon YYYY')
      ELSE
        'Available'
    END AS availability_message

  FROM transport_vehicles tv

  LEFT JOIN LATERAL (
    SELECT
      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 tv.transport_agency_id = $1

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

        const driversResult = await pool.query(
          `
          SELECT *
          FROM transport_drivers
          WHERE transport_agency_id = $1
          `,
          [agency.id]
        );

        return {
          ...agency,
          vehicles: vehiclesResult.rows,
          drivers: driversResult.rows,
        };
      })
    );

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

  } catch (error) {
    console.error(error);

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


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

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

    const { id } = req.params;

    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)
      : [];

    // =========================
    // 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,
      ]
    );

    // =========================
    // DELETE OLD VEHICLES
    // =========================

    await client.query(
      `
      DELETE FROM transport_vehicles
      WHERE transport_agency_id = $1
      `,
      [id]
    );

    // =========================
    // INSERT UPDATED VEHICLES
    // =========================

    const uploadedFiles = req.files || [];

    const insertedVehicles = [];

    for (const [index, vehicle] of vehicles.entries()) {
      const vehicleResult = await client.query(
        `
        INSERT INTO transport_vehicles (
          transport_agency_id,
          vehicle_name,
          vehicle_number,
          vehicle_type,
          capacity_tons,
          driver_name,
          driver_contact_number,
          license_number,
          vehicle_document
        )
        VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9)
        RETURNING *;
        `,
        [
          id,
          vehicle.vehicle_name,
          vehicle.vehicle_number,
          vehicle.vehicle_type,
          vehicle.capacity_tons,
          vehicle.driver_name,
          vehicle.driver_contact_number,
          vehicle.license_number,
          uploadedFiles[index]?.filename ||
            vehicle.vehicle_document ||
            null,
        ]
      );

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

    await client.query("COMMIT");

    return res.status(200).json({
      success: true,
      message: "Transport agency updated successfully",
      data: {
        agency: agencyResult.rows[0],
        vehicles: insertedVehicles,
      },
    });
  } catch (error) {
    await client.query("ROLLBACK");

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

    return res.status(500).json({
      success: false,
      message: "Internal server error",
      error: error.message,
    });
  } 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 *
      FROM transport_agencies
      WHERE id = $1
      `,
      [id]
    );

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

    // =========================
    // GET VEHICLES
    // =========================

    const vehicleResult = await client.query(
      `
      SELECT *
      FROM transport_vehicles
      WHERE transport_agency_id = $1
      ORDER BY 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,
};
