const db = require("../db");
const { DEV_MOCK } = require("../lib/env");
const { store: _mock } = require("../mock/store");
const { calculateDepartureTimes, parseWeekdaysValue, expandRecurringScheduleRows, mapAdminScheduleRow, scheduleMatchesFilters, parseScheduleFilters, checkDriverConflict } = require("../lib/schedule");
const { reportError } = require("../lib/report-error");

// Admin schedules repository methods (PostgreSQL mode). Each returns
// { statusCode, body } mirroring the original handler's DB branch.

async function listAdminSchedules(req) {
  try {
    const templatesOnly =
      String(
        req.query.templates || req.query.includeTemplates || "",
      ).toLowerCase() === "true" ||
      String(req.query.templates || req.query.includeTemplates || "") === "1";

    const hasWeekdaysColumn = await db.query(`
      SELECT 1 FROM information_schema.columns 
      WHERE table_schema='transit' AND table_name='schedules' AND column_name='weekdays'
    `);

    const baseSelect = `
      SELECT 
        s.id,
        s.bus_line_id,
        s.bus_id,
        s.driver_id,
        s.start_time,
        s.end_time,
        s.status,
        s.created_at,
        bl.line_number,
        bl.estimated_duration_minutes,
        b.name as bus_name,
        b.license_plate,
        TRIM(COALESCE(e.firstname, '') || ' ' || COALESCE(e.lastname, '')) as driver_name`;

    const result =
      hasWeekdaysColumn.rows.length > 0
        ? await db.query(`${baseSelect}, s.weekdays as weekdays
          FROM schedules s
          LEFT JOIN bus_lines bl ON s.bus_line_id = bl.id
          LEFT JOIN fleet.bus b ON s.bus_id = b.id
          LEFT JOIN workers.employees e ON s.driver_id = e.id
          ORDER BY s.start_time DESC`)
        : await db.query(`${baseSelect}, NULL as weekdays
          FROM schedules s
          LEFT JOIN bus_lines bl ON s.bus_line_id = bl.id
          LEFT JOIN fleet.bus b ON s.bus_id = b.id
          LEFT JOIN workers.employees e ON s.driver_id = e.id
          ORDER BY s.start_time DESC`);

    const lineIds = Array.from(
      new Set(
        result.rows
          .map((row) => Number(row.bus_line_id))
          .filter(Number.isFinite),
      ),
    );

    const routeStopsByLine = new Map();
    if (lineIds.length > 0) {
      const routeStopsResult = await db.query(
        `
        SELECT
          rs.bus_line_id,
          rs.stop_id,
          rs.stop_order,
          rs.estimated_arrival_minutes,
          s.name AS stop_name
        FROM route_stops rs
        JOIN stops s ON s.id = rs.stop_id
        WHERE rs.bus_line_id = ANY($1::int[])
        ORDER BY rs.bus_line_id ASC, rs.stop_order ASC
      `,
        [lineIds],
      );

      for (const row of routeStopsResult.rows) {
        const busLineId = Number(row.bus_line_id);
        if (!routeStopsByLine.has(busLineId)) {
          routeStopsByLine.set(busLineId, []);
        }
        routeStopsByLine.get(busLineId).push(row);
      }
    }

    const filters = parseScheduleFilters(req.query);
    const rowSource = templatesOnly
      ? result.rows
      : expandRecurringScheduleRows(result.rows);
    const expandedSchedules = rowSource.filter((row) =>
      scheduleMatchesFilters(row, filters, routeStopsByLine),
    );

    const schedules = expandedSchedules
      .map((row) => mapAdminScheduleRow(row, routeStopsByLine))
      .sort((a, b) => new Date(a.startTime) - new Date(b.startTime));

    return { statusCode: 200, body: { schedules } };
  } catch (err) {
    console.error("GET /admin/schedules error:", err);
    reportError({ partofservice: "GET /admin/schedules", error: err.stack || err.message });
    return { statusCode: 500, body: { error: "internal_error", details: err.message } };
  }
}

async function createAdminSchedule(req) {
  try {
    const { busLineId, busId, driverId, startTime, weekdays } = req.body;

    if (!busLineId || !busId || !startTime) {
      return {
        statusCode: 400,
        body: {
          error:
            "missing_required_fields: busLineId, busId, startTime required",
        },
      };
    }

    // Validate weekdays - should be array of day numbers (0-6) or empty for single schedule
    const scheduleWeekdays =
      Array.isArray(weekdays) && weekdays.length > 0
        ? weekdays.map((w) => Number(w)).filter((w) => w >= 0 && w <= 6)
        : [];

    // Database mode (production)
    const busLine = await db.query(
      "SELECT estimated_duration_minutes FROM bus_lines WHERE id = $1",
      [busLineId],
    );
    if (!busLine.rows[0]) {
      return { statusCode: 404, body: { error: "bus_line_not_found" } };
    }

    const startDate = new Date(startTime);
    const endDate = new Date(startDate);
    endDate.setMinutes(
      endDate.getMinutes() +
        (busLine.rows[0].estimated_duration_minutes || 60),
    );

    // Check if weekdays column exists before including it in INSERT
    const hasWeekdaysColumn = await db.query(`
      SELECT 1 FROM information_schema.columns 
      WHERE table_schema='transit' AND table_name='schedules' AND column_name='weekdays'
    `);

    let result;
    if (hasWeekdaysColumn.rows.length > 0) {
      result = await db.query(
        `
        WITH next_id AS (
          SELECT COALESCE(MAX(id), 0) + 1 AS id FROM schedules
        )
        INSERT INTO schedules (id, bus_line_id, bus_id, driver_id, start_time, end_time, status, weekdays)
        SELECT next_id.id, $1, $2, $3, $4, $5, 'planned', $6
        FROM next_id
        RETURNING id, bus_line_id, bus_id, driver_id, start_time, end_time, status, weekdays, created_at
      `,
        [
          busLineId,
          busId,
          driverId || null,
          startTime,
          endDate.toISOString(),
          JSON.stringify(scheduleWeekdays),
        ],
      );
    } else {
      result = await db.query(
        `
        WITH next_id AS (
          SELECT COALESCE(MAX(id), 0) + 1 AS id FROM schedules
        )
        INSERT INTO schedules (id, bus_line_id, bus_id, driver_id, start_time, end_time, status)
        SELECT next_id.id, $1, $2, $3, $4, $5, 'planned'
        FROM next_id
        RETURNING id, bus_line_id, bus_id, driver_id, start_time, end_time, status, created_at
      `,
        [
          busLineId,
          busId,
          driverId || null,
          startTime,
          endDate.toISOString(),
        ],
      );
    }

    const schedule = result.rows[0];
const departureTimes = calculateDepartureTimes(
        startTime,
        busLineId,
        _mock,
      );

    const weekdaysResult = parseWeekdaysValue(schedule.weekdays);

    return {
      statusCode: 201,
      body: {
        schedule: {
          id: Number(schedule.id),
          busLineId: Number(schedule.bus_line_id),
          busId: Number(schedule.bus_id),
          driverId:
            schedule.driver_id !== null && schedule.driver_id !== undefined
              ? Number(schedule.driver_id)
              : null,
          startTime: schedule.start_time,
          endTime: schedule.end_time,
          weekdays: weekdaysResult,
          departureTimes,
          status: schedule.status,
          createdAt: schedule.created_at,
        },
      },
    };
  } catch (err) {
    console.error(err);
    reportError({ partofservice: `${req.method} ${req.originalUrl}`, error: err.stack || err.message });
    return { statusCode: 500, body: { error: "internal_error", details: err.message } };
  }
}

async function updateAdminSchedule(req) {
  try {
    const { scheduleId } = req.params;
    const { busLineId, busId, driverId, startTime, weekdays } = req.body;

    if (!scheduleId || !Number.isFinite(Number(scheduleId))) {
      return { statusCode: 400, body: { error: "invalid_schedule_id" } };
    }

    if (!busLineId || !busId || !startTime) {
      return {
        statusCode: 400,
        body: {
          error:
            "missing_required_fields: busLineId, busId, startTime required",
        },
      };
    }

    const scheduleWeekdays =
      Array.isArray(weekdays) && weekdays.length > 0
        ? weekdays.map((w) => Number(w)).filter((w) => w >= 0 && w <= 6)
        : [];

    const existingResult = await db.query(
      `
      SELECT id, status
      FROM schedules
      WHERE id = $1
    `,
      [scheduleId],
    );

    if (existingResult.rows.length === 0) {
      return { statusCode: 404, body: { error: "schedule_not_found" } };
    }

    const busLine = await db.query(
      "SELECT estimated_duration_minutes FROM bus_lines WHERE id = $1",
      [busLineId],
    );
    if (!busLine.rows[0]) {
      return { statusCode: 404, body: { error: "bus_line_not_found" } };
    }

    const startDate = new Date(startTime);
    const endDate = new Date(startDate);
    endDate.setMinutes(
      endDate.getMinutes() +
        (busLine.rows[0].estimated_duration_minutes || 60),
    );

    const hasWeekdaysColumn = await db.query(`
      SELECT 1 FROM information_schema.columns
      WHERE table_schema='transit' AND table_name='schedules' AND column_name='weekdays'
    `);

    let result;
    if (hasWeekdaysColumn.rows.length > 0) {
      result = await db.query(
        `
        UPDATE schedules
        SET
          bus_line_id = $2,
          bus_id = $3,
          driver_id = $4,
          start_time = $5,
          end_time = $6,
          weekdays = $7,
          updated_at = NOW()
        WHERE id = $1
        RETURNING id, bus_line_id, bus_id, driver_id, start_time, end_time, status, weekdays, created_at
      `,
        [
          scheduleId,
          busLineId,
          busId,
          driverId || null,
          startTime,
          endDate.toISOString(),
          JSON.stringify(scheduleWeekdays),
        ],
      );
    } else {
      result = await db.query(
        `
        UPDATE schedules
        SET
          bus_line_id = $2,
          bus_id = $3,
          driver_id = $4,
          start_time = $5,
          end_time = $6,
          updated_at = NOW()
        WHERE id = $1
        RETURNING id, bus_line_id, bus_id, driver_id, start_time, end_time, status, created_at
      `,
        [
          scheduleId,
          busLineId,
          busId,
          driverId || null,
          startTime,
          endDate.toISOString(),
        ],
      );
    }

    const schedule = result.rows[0];
    return {
      statusCode: 200,
      body: {
        schedule: {
          id: Number(schedule.id),
          busLineId: Number(schedule.bus_line_id),
          busId: Number(schedule.bus_id),
          driverId:
            schedule.driver_id !== null && schedule.driver_id !== undefined
              ? Number(schedule.driver_id)
              : null,
          startTime: schedule.start_time,
          endTime: schedule.end_time,
          weekdays: parseWeekdaysValue(schedule.weekdays),
          status: schedule.status,
          createdAt: schedule.created_at,
        },
      },
    };
  } catch (err) {
    console.error(err);
    reportError({ partofservice: `${req.method} ${req.originalUrl}`, error: err.stack || err.message });
    return { statusCode: 500, body: { error: "internal_error", details: err.message } };
  }
}

async function bulkCreateAdminSchedules(req) {
  const requestedSchedules = Array.isArray(req.body?.schedules)
    ? req.body.schedules
    : [];

  if (requestedSchedules.length === 0) {
    return {
      statusCode: 400,
      body: { error: "missing_required_fields: schedules array required" },
    };
  }

  const normalizedSchedules = requestedSchedules.map((schedule, index) => {
    const busLineId = Number(schedule?.busLineId);
    const busId = Number(schedule?.busId);
    const driverId =
      schedule?.driverId !== null &&
      schedule?.driverId !== undefined &&
      schedule?.driverId !== ""
        ? Number(schedule.driverId)
        : null;
    const startTime = schedule?.startTime ? String(schedule.startTime) : "";
    const weekdays =
      Array.isArray(schedule?.weekdays) && schedule.weekdays.length > 0
        ? schedule.weekdays
            .map((day) => Number(day))
            .filter((day) => day >= 0 && day <= 6)
        : [];

    if (
      !Number.isFinite(busLineId) ||
      !Number.isFinite(busId) ||
      !startTime
    ) {
      throw new Error(`invalid_schedule_entry:${index}`);
    }

    return { busLineId, busId, driverId, startTime, weekdays };
  });

  try {
    const dbClient = await db.pool.connect();
    try {
      await dbClient.query("BEGIN");

      const hasWeekdaysColumn = await dbClient.query(`
      SELECT 1 FROM information_schema.columns
      WHERE table_schema='transit' AND table_name='schedules' AND column_name='weekdays'
    `);

      const createdSchedules = [];

      for (const entry of normalizedSchedules) {
        const busLine = await dbClient.query(
          "SELECT estimated_duration_minutes FROM bus_lines WHERE id = $1",
          [entry.busLineId],
        );
        if (!busLine.rows[0]) {
          throw new Error("bus_line_not_found");
        }

        const startDate = new Date(entry.startTime);
        const endDate = new Date(startDate);
        endDate.setMinutes(
          endDate.getMinutes() +
            (busLine.rows[0].estimated_duration_minutes || 60),
        );

        let result;
        if (hasWeekdaysColumn.rows.length > 0) {
          result = await dbClient.query(
            `
            WITH next_id AS (
              SELECT COALESCE(MAX(id), 0) + 1 AS id FROM schedules
            )
            INSERT INTO schedules (id, bus_line_id, bus_id, driver_id, start_time, end_time, status, weekdays)
            SELECT next_id.id, $1, $2, $3, $4, $5, 'planned', $6
            FROM next_id
            RETURNING id, bus_line_id, bus_id, driver_id, start_time, end_time, status, weekdays, created_at
          `,
            [
              entry.busLineId,
              entry.busId,
              entry.driverId,
              entry.startTime,
              endDate.toISOString(),
              JSON.stringify(entry.weekdays),
            ],
          );
        } else {
          result = await dbClient.query(
            `
            WITH next_id AS (
              SELECT COALESCE(MAX(id), 0) + 1 AS id FROM schedules
            )
            INSERT INTO schedules (id, bus_line_id, bus_id, driver_id, start_time, end_time, status)
            SELECT next_id.id, $1, $2, $3, $4, $5, 'planned'
            FROM next_id
            RETURNING id, bus_line_id, bus_id, driver_id, start_time, end_time, status, created_at
          `,
            [
              entry.busLineId,
              entry.busId,
              entry.driverId,
              entry.startTime,
              endDate.toISOString(),
            ],
          );
        }

        const schedule = result.rows[0];
        createdSchedules.push({
          id: Number(schedule.id),
          busLineId: Number(schedule.bus_line_id),
          busId: Number(schedule.bus_id),
          driverId:
            schedule.driver_id !== null && schedule.driver_id !== undefined
              ? Number(schedule.driver_id)
              : null,
          startTime: schedule.start_time,
          endTime: schedule.end_time,
          weekdays: parseWeekdaysValue(schedule.weekdays),
          status: schedule.status,
          createdAt: schedule.created_at,
        });
      }

      await dbClient.query("COMMIT");
      return {
        statusCode: 201,
        body: {
          createdCount: createdSchedules.length,
          schedules: createdSchedules,
        },
      };
    } catch (err) {
      await dbClient.query("ROLLBACK");
      throw err;
    } finally {
      dbClient.release();
    }
  } catch (err) {
    console.error(err);
    reportError({ partofservice: "POST /admin/schedules/bulk", error: err.stack || err.message });
    const errorMessage =
      err.message === "bus_line_not_found"
        ? "bus_line_not_found"
        : err.message.startsWith("invalid_schedule_entry:")
          ? err.message
          : "internal_error";
    const statusCode =
      errorMessage === "bus_line_not_found"
        ? 404
        : errorMessage.startsWith("invalid_schedule_entry:")
          ? 400
          : 500;
    return { statusCode, body: { error: errorMessage, details: err.message } };
  }
}

async function deleteAdminSchedule(req) {
  try {
    const { scheduleId } = req.params;

    if (!scheduleId || !Number.isFinite(Number(scheduleId))) {
      return { statusCode: 400, body: { error: "invalid_schedule_id" } };
    }

    const result = await db.query(
      `DELETE FROM schedules WHERE id = $1 RETURNING id`,
      [scheduleId],
    );

    if (result.rows.length === 0) {
      return { statusCode: 404, body: { error: "schedule_not_found" } };
    }

    return {
      statusCode: 200,
      body: { success: true, schedule: { id: Number(result.rows[0].id) } },
    };
  } catch (err) {
    console.error(err);
    reportError({ partofservice: `${req.method} ${req.originalUrl}`, error: err.stack || err.message });
    return { statusCode: 500, body: { error: "internal_error", details: err.message } };
  }
}

async function checkDriverConflicts(req) {
  try {
    const {
      generatedTimes,
      drivers,
      busLineId,
      busId,
      weekdays = [],
    } = req.body;

    if (!Array.isArray(generatedTimes) || !Array.isArray(drivers)) {
      return { statusCode: 400, body: { error: "missing_required_fields" } };
    }

    // Get all existing schedules
    const schedResult = DEV_MOCK
      ? { rows: _mock.schedules || [] }
      : await db.query(
          `SELECT * FROM schedules WHERE status IN ('planned', 'active')`,
        );

    const existingSchedules = schedResult.rows.map((row) => ({
      ...row,
      weekdays: parseWeekdaysValue(row.weekdays || row.weekday_list || "[]"),
    }));

    // For each generated time, check conflicts with each driver
    const assignments = generatedTimes.map((timeStr) => {
      const driverAssignments = drivers.map((driver) => {
        const hasConflict = checkDriverConflict(
          driver.id,
          timeStr,
          timeStr, // Will be checked with start/end times of conflicts
          weekdays,
          existingSchedules,
        );

        return {
          driverId: driver.id,
          driverName: driver.name,
          hasConflict,
          assigned: false,
        };
      });

      return {
        time: timeStr,
        driverAssignments,
        assignedDriverId: null,
      };
    });

    return { statusCode: 200, body: { assignments } };
  } catch (err) {
    console.error("/admin/check-driver-conflicts error:", err);
    reportError({ partofservice: "/admin/check-driver-conflicts", error: err.stack || err.message });
    return { statusCode: 500, body: { error: "internal_error", details: err.message } };
  }
}

async function assignSchedules(req) {
  try {
    const { assignments, busLineId, busId, weekdays = [] } = req.body;

    if (!Array.isArray(assignments)) {
      return { statusCode: 400, body: { error: "missing_assignments" } };
    }

    // Group assignments by driver (round-robin or custom)
    const schedulesToCreate = [];
    let driverIndex = 0;

    for (const assignment of assignments) {
      if (!assignment.time || !assignment.assignedDriverId) continue;

      const startTime = assignment.time;
      const endTime = assignment.endTime;

      schedulesToCreate.push({
        busLineId,
        busId,
        driverId: assignment.assignedDriverId,
        startTime,
        endTime,
        weekdays,
      });
    }

    // For DB: use bulk insert
    return {
      statusCode: 201,
      body: {
        createdCount: schedulesToCreate.length,
        message: "would_insert_schedules",
      },
    };
  } catch (err) {
    console.error("/admin/assign-schedules error:", err);
    reportError({ partofservice: "/admin/assign-schedules", error: err.stack || err.message });
    return { statusCode: 500, body: { error: "internal_error", details: err.message } };
  }
}

module.exports = {
  listAdminSchedules,
  createAdminSchedule,
  updateAdminSchedule,
  bulkCreateAdminSchedules,
  deleteAdminSchedule,
  checkDriverConflicts,
  assignSchedules,
};