import { query } from "./db";

type VisitorRow = {
  id: number;
  pass_no: string;
  aadhaar_last4: string;
  name: string;
  age: number | null;
  gender: string | null;
  mobile: string;
  emergency_contact: string;
  address_line1?: string | null;
  address_line2?: string | null;
  state: string;
  city?: string | null;
  district?: string | null;
  pincode?: string | null;
  post_office?: string | null;
  arrival_date: string | Date;
  transport_mode: string;
  stay_type: string;
  status: string;
  channel: string;
  registration_time: Date | string;
};

export type AdminVisitor = {
  id: number;
  pass_no: string;
  name: string;
  age: number | null;
  gender: string | null;
  mobile: string;
  emergency_contact: string;
  address_line1: string;
  address_line2: string;
  state: string;
  city: string;
  district: string;
  pincode: string;
  post_office: string;
  home_address: string;
  arrival_date: string;
  transport_mode: string;
  stay_type: string;
  status: string;
  channel: string;
  aadhaar_last4: string;
  registration_time: string;
};

export type ListFilters = {
  state?: string;
  arrivalFrom?: string;
  arrivalTo?: string;
  q?: string;
  page?: number;
  pageSize?: number;
};

function asDateString(value: string | Date | null | undefined): string {
  if (!value) return "";
  if (typeof value === "string") {
    return value.slice(0, 10);
  }
  const y = value.getFullYear();
  const m = String(value.getMonth() + 1).padStart(2, "0");
  const d = String(value.getDate()).padStart(2, "0");
  return `${y}-${m}-${d}`;
}

function asDateTimeString(value: string | Date | null | undefined): string {
  if (!value) return "";
  if (typeof value === "string") return value;
  return value.toISOString();
}

function formatHome(row: VisitorRow) {
  return [
    row.address_line1,
    row.address_line2,
    row.city,
    row.district,
    row.state,
    row.pincode,
  ]
    .map((x) => (typeof x === "string" ? x.trim() : ""))
    .filter(Boolean)
    .join(", ");
}

function mapVisitor(row: VisitorRow): AdminVisitor {
  return {
    id: row.id,
    pass_no: row.pass_no,
    name: row.name,
    age: row.age,
    gender: row.gender,
    mobile: row.mobile,
    emergency_contact: row.emergency_contact,
    address_line1: row.address_line1 || "",
    address_line2: row.address_line2 || "",
    state: row.state,
    city: row.city || "",
    district: row.district || "",
    pincode: row.pincode || "",
    post_office: row.post_office || "",
    home_address: formatHome(row),
    arrival_date: asDateString(row.arrival_date),
    transport_mode: row.transport_mode,
    stay_type: row.stay_type,
    status: row.status,
    channel: row.channel,
    aadhaar_last4: row.aadhaar_last4,
    registration_time: asDateTimeString(row.registration_time),
  };
}

export async function listVisitors(filters: ListFilters = {}) {
  const page = Math.max(1, filters.page || 1);
  const pageSize = Math.min(100, Math.max(1, filters.pageSize || 25));
  const offset = (page - 1) * pageSize;

  const where: string[] = [];
  const params: Record<string, unknown> = {};

  if (filters.state) {
    where.push("state = :state");
    params.state = filters.state;
  }
  if (filters.arrivalFrom) {
    where.push("arrival_date >= :arrival_from");
    params.arrival_from = filters.arrivalFrom;
  }
  if (filters.arrivalTo) {
    where.push("arrival_date <= :arrival_to");
    params.arrival_to = filters.arrivalTo;
  }
  if (filters.q?.trim()) {
    where.push(
      `(pass_no LIKE :q OR name LIKE :q OR mobile LIKE :q OR aadhaar_last4 LIKE :q
        OR city LIKE :q OR district LIKE :q OR pincode LIKE :q
        OR address_line1 LIKE :q OR address_line2 LIKE :q)`,
    );
    params.q = `%${filters.q.trim()}%`;
  }

  const whereSql = where.length ? `WHERE ${where.join(" AND ")}` : "";

  const countRows = await query<{ total: number }[]>(
    `SELECT COUNT(*) AS total FROM visitors ${whereSql}`,
    params,
  );
  const total = Number(
    (countRows as unknown as { total: number }[])[0]?.total || 0,
  );

  const rows = await query<VisitorRow[]>(
    `SELECT id, pass_no, aadhaar_last4, name, age, gender,
            mobile, emergency_contact,
            address_line1, address_line2, state, city, district, pincode, post_office,
            arrival_date, transport_mode, stay_type, status, channel, registration_time
     FROM visitors
     ${whereSql}
     ORDER BY registration_time DESC
     LIMIT ${pageSize} OFFSET ${offset}`,
    params,
  );

  return {
    total,
    page,
    pageSize,
    visitors: (rows as VisitorRow[]).map(mapVisitor),
  };
}

export async function reportByState() {
  const rows = await query<{ state: string; count: number }[]>(
    `SELECT state, COUNT(*) AS count
     FROM visitors
     GROUP BY state
     ORDER BY count DESC, state ASC`,
  );
  return (rows as unknown as { state: string; count: number }[]).map((r) => ({
    state: r.state,
    count: Number(r.count),
  }));
}

export async function reportByArrivalDate() {
  const rows = await query<{ arrival_date: string | Date; count: number }[]>(
    `SELECT arrival_date, COUNT(*) AS count
     FROM visitors
     GROUP BY arrival_date
     ORDER BY arrival_date ASC`,
  );
  return (
    rows as unknown as { arrival_date: string | Date; count: number }[]
  ).map((r) => ({
    arrival_date: asDateString(r.arrival_date),
    count: Number(r.count),
  }));
}

export async function registrationSummary() {
  const rows = await query<
    {
      total: number;
      states: number;
      earliest: string | Date | null;
      latest: string | Date | null;
    }[]
  >(
    `SELECT
       COUNT(*) AS total,
       COUNT(DISTINCT state) AS states,
       MIN(arrival_date) AS earliest,
       MAX(arrival_date) AS latest
     FROM visitors`,
  );
  const row = (
    rows as unknown as {
      total: number;
      states: number;
      earliest: string | Date | null;
      latest: string | Date | null;
    }[]
  )[0];
  return {
    total: Number(row?.total || 0),
    states: Number(row?.states || 0),
    earliest_arrival: asDateString(row?.earliest),
    latest_arrival: asDateString(row?.latest),
  };
}