import { pool } from "../config/db.js";
import { getJwtUserId } from "../middlewares/authMiddleware.js";

const PERIOD_MS = {
  "7d": 7 * 24 * 60 * 60 * 1000,
  "30d": 30 * 24 * 60 * 60 * 1000,
  "90d": 90 * 24 * 60 * 60 * 1000
};

function periodStart(period) {
  const key = String(period || "all").trim().toLowerCase();
  if (key === "year") {
    const now = new Date();
    return new Date(now.getFullYear(), 0, 1);
  }
  const ms = PERIOD_MS[key];
  if (!ms) return null;
  return new Date(Date.now() - ms);
}

function trendGranularity(period) {
  const key = String(period || "all").trim().toLowerCase();
  if (key === "7d" || key === "30d") return "day";
  if (key === "90d") return "week";
  return "month";
}

function isActivePermit(row) {
  if (Number(row.permit_status_id) === 2) return true;
  if (Number(row.permit_status_id) === 3) return false;
  if (row.valid_until) {
    return new Date(row.valid_until).getTime() > Date.now();
  }
  return false;
}

function isClosedPermit(row) {
  if (Number(row.permit_status_id) === 3) return true;
  if (row.valid_until && row.valid_from) {
    return new Date(row.valid_until).getTime() <= Date.now();
  }
  return false;
}

function formatBucketLabel(date, granularity) {
  if (granularity === "day") {
    return date.toLocaleDateString(undefined, { month: "short", day: "numeric" });
  }
  if (granularity === "week") {
    return date.toLocaleDateString(undefined, { month: "short", day: "numeric" });
  }
  return date.toLocaleDateString(undefined, { month: "short", year: "2-digit" });
}

function buildTrendBuckets(period) {
  const granularity = trendGranularity(period);
  const start = periodStart(period);
  const now = new Date();
  const buckets = [];

  if (granularity === "day") {
    const days = period === "7d" ? 7 : 30;
    for (let i = days - 1; i >= 0; i -= 1) {
      const d = new Date(now);
      d.setHours(0, 0, 0, 0);
      d.setDate(d.getDate() - i);
      buckets.push({
        key: d.toISOString().slice(0, 10),
        label: formatBucketLabel(d, "day"),
        count: 0
      });
    }
    return buckets;
  }

  if (granularity === "week") {
    for (let i = 12; i >= 0; i -= 1) {
      const d = new Date(now);
      d.setHours(0, 0, 0, 0);
      d.setDate(d.getDate() - i * 7);
      buckets.push({
        key: d.toISOString().slice(0, 10),
        label: formatBucketLabel(d, "week"),
        count: 0
      });
    }
    return buckets;
  }

  const monthCount = period === "year" ? 12 : 12;
  for (let i = monthCount - 1; i >= 0; i -= 1) {
    const d = new Date(now.getFullYear(), now.getMonth() - i, 1);
    buckets.push({
      key: `${d.getFullYear()}-${String(d.getMonth() + 1).padStart(2, "0")}`,
      label: formatBucketLabel(d, "month"),
      count: 0
    });
  }
  return buckets;
}

function bucketKeyForDate(date, granularity) {
  if (granularity === "day") {
    return date.toISOString().slice(0, 10);
  }
  if (granularity === "week") {
    const d = new Date(date);
    d.setHours(0, 0, 0, 0);
    const day = d.getDay();
    d.setDate(d.getDate() - day);
    return d.toISOString().slice(0, 10);
  }
  return `${date.getFullYear()}-${String(date.getMonth() + 1).padStart(2, "0")}`;
}

function isGeneralJobTypeName(name) {
  const n = String(name || "").trim().toLowerCase();
  return n === "general" || n.includes("general");
}

function isLifecycleJobTypeName(name) {
  const n = String(name || "").trim().toLowerCase();
  return (
    n === "general" ||
    n.includes("general") ||
    n.includes("handover") ||
    n.includes("hand back") ||
    n.includes("handback") ||
    n.includes("audit")
  );
}

async function resolveUserContext(userId) {
  if (!userId) {
    return { userId: null, locationId: null, locationName: null };
  }
  const [rows] = await pool.query(
    `SELECT u.user_id, u.location_id, loc.location_name
     FROM users u
     LEFT JOIN locations loc ON loc.location_id = u.location_id
     WHERE u.user_id = ?
     LIMIT 1`,
    [userId]
  );
  const row = rows[0];
  return {
    userId: row ? Number(row.user_id) : userId,
    locationId: row?.location_id != null ? Number(row.location_id) : null,
    locationName: row?.location_name || null
  };
}

/** GET /dashboard/stats?period=7d|30d|90d|year|all */
export async function getDashboardStats(req, res, next) {
  try {
    const period = String(req.query?.period || "30d").trim().toLowerCase();
    const start = periodStart(period);
    const userIdRaw = getJwtUserId(req.user);
    const userId = userIdRaw != null ? Number(userIdRaw) : null;
    const userCtx = await resolveUserContext(
      Number.isInteger(userId) && userId > 0 ? userId : null
    );

    const permitValues = [];
    let permitSql = `
      SELECT p.permit_id, p.permit_number, p.uploader_user_id, p.permit_status_id,
             p.location_id, p.valid_from, p.valid_until, p.created_at,
             loc.location_name,
             u.location_id AS uploader_location_id
      FROM ptw_permits p
      LEFT JOIN locations loc ON loc.location_id = p.location_id
      LEFT JOIN users u ON u.user_id = p.uploader_user_id`;
    if (start) {
      permitSql += " WHERE p.created_at >= ?";
      permitValues.push(start);
    }
    permitSql += " ORDER BY p.created_at DESC";

    const [permits] = await pool.query(permitSql, permitValues);

    const myPermits = permits.filter(
      (row) => userCtx.userId && Number(row.uploader_user_id) === userCtx.userId
    ).length;

    const myLocationPermits = userCtx.locationId
      ? permits.filter((row) => {
          const userLoc = userCtx.locationId;
          const permitLoc =
            row.location_id != null ? Number(row.location_id) : null;
          const uploaderLoc =
            row.uploader_location_id != null
              ? Number(row.uploader_location_id)
              : null;
          return (
            permitLoc === userLoc ||
            (permitLoc == null && uploaderLoc === userLoc)
          );
        }).length
      : 0;

    const activeCount = permits.filter(isActivePermit).length;
    const closedCount = permits.filter(isClosedPermit).length;

    const granularity = trendGranularity(period);
    const trendBuckets = buildTrendBuckets(period);
    const trendMap = Object.fromEntries(
      trendBuckets.map((b) => [b.key, { ...b, count: 0, closed: 0 }])
    );
    for (const row of permits) {
      const created = row.created_at ? new Date(row.created_at) : null;
      if (!created || Number.isNaN(created.getTime())) continue;
      const key = bucketKeyForDate(created, granularity);
      if (trendMap[key]) {
        trendMap[key].count += 1;
        if (isClosedPermit(row)) {
          trendMap[key].closed += 1;
        }
      }
    }
    const trend = trendBuckets.map(({ key, label }) => ({
      period: key,
      label,
      count: trendMap[key]?.count ?? 0,
      closed: trendMap[key]?.closed ?? 0
    }));

    const locationMap = new Map();
    for (const row of permits) {
      const locId = row.location_id != null ? Number(row.location_id) : 0;
      const label = row.location_name || (locId ? `Location #${locId}` : "Unassigned");
      const entry = locationMap.get(locId) || { label, value: 0 };
      entry.value += 1;
      locationMap.set(locId, entry);
    }
    const locationWise = [...locationMap.values()]
      .sort((a, b) => b.value - a.value)
      .slice(0, 12);

    const submissionValues = [];
    let submissionSql = `
      SELECT s.submission_id, s.permit_id, s.created_at,
             jt.job_type_id, jt.job_type_name, jt.job_colour_code
      FROM ptw_submissions s
      INNER JOIN ptw_form_details f ON f.form_id = s.form_id
      INNER JOIN ptw_job_type jt ON jt.job_type_id = f.job_type_id
      INNER JOIN ptw_permits p ON p.permit_id = s.permit_id`;
    if (start) {
      submissionSql += " WHERE p.created_at >= ?";
      submissionValues.push(start);
    }
    const [submissions] = await pool.query(submissionSql, submissionValues);

    const jobTypeMap = new Map();
    for (const row of submissions) {
      const name = row.job_type_name || "Unknown";
      if (isGeneralJobTypeName(name) || isLifecycleJobTypeName(name)) continue;
      const id = Number(row.job_type_id);
      const entry = jobTypeMap.get(id) || {
        label: name,
        value: 0,
        color: row.job_colour_code || null
      };
      entry.value += 1;
      jobTypeMap.set(id, entry);
    }
    const jobTypeWise = [...jobTypeMap.values()].sort((a, b) => b.value - a.value);

    const recentActivity = permits.slice(0, 10).map((row) => {
      let statusLabel = "Draft";
      let statusTone = "draft";
      if (isClosedPermit(row)) {
        statusLabel = "Closed";
        statusTone = "closed";
      } else if (isActivePermit(row)) {
        statusLabel = "Active";
        statusTone = "active";
      }
      return {
        permit_id: row.permit_id,
        permit_number: row.permit_number || `PTW-${row.permit_id}`,
        location_name: row.location_name || "—",
        created_at: row.created_at,
        status_label: statusLabel,
        status_tone: statusTone
      };
    });

    res.json({
      period,
      user: {
        location_id: userCtx.locationId,
        location_name: userCtx.locationName
      },
      cards: {
        my_permits: myPermits,
        my_location_permits: myLocationPermits,
        active: activeCount,
        closed: closedCount
      },
      trend,
      location_wise: locationWise,
      job_type_wise: jobTypeWise,
      recent_activity: recentActivity
    });
  } catch (err) {
    next(err);
  }
}
