import fs from 'fs';
import path from 'path';
import bcrypt from 'bcryptjs';
import { RowDataPacket, ResultSetHeader } from 'mysql2';
import { pool, pingDb } from './db';
import { Database, User, PdfDocument, RemediationJob, Order, OutboxEmail, PlanId, Plan } from './types';
import { SEED_PLANS, setPlans, getPlan } from './plans';

// ---------------------------------------------------------------------------
// File storage (uploads / remediated PDFs / rendered emails stay on disk;
// only the relational data moves to MySQL).
// ---------------------------------------------------------------------------
const DATA_DIR = path.join(__dirname, '..', 'data');
const DB_FILE = path.join(DATA_DIR, 'db.json'); // legacy JSON — used once for migration

export const UPLOADS_DIR = path.join(DATA_DIR, 'uploads');
export const REMEDIATED_DIR = path.join(DATA_DIR, 'remediated');
export const OUTBOX_DIR = path.join(DATA_DIR, 'outbox');

for (const dir of [DATA_DIR, UPLOADS_DIR, REMEDIATED_DIR, OUTBOX_DIR]) {
  if (!fs.existsSync(dir)) fs.mkdirSync(dir, { recursive: true });
}

// ---------------------------------------------------------------------------
// In-memory working set. Routes read/mutate this synchronously exactly as
// before; save() mirrors the mutable slices back to MySQL (debounced).
// ---------------------------------------------------------------------------
export const db: Database = { users: [], documents: [], jobs: [], orders: [], emails: [] };

export function uid(): string {
  return Date.now().toString(36) + Math.random().toString(36).slice(2, 10);
}

/** ISO 8601 (what the app stores) -> MySQL DATETIME literal for the Laravel `users` table. */
function toMysqlDate(iso: string): string {
  return iso.slice(0, 19).replace('T', ' ');
}

// ---------------------------------------------------------------------------
// Schema — app-owned tables (reuses the existing `users` table for identity).
// ---------------------------------------------------------------------------
async function ensureSchema(): Promise<void> {
  // Plans live in the existing `package_details` table; per-user purchase/trial
  // ledger lives in the existing `user_packages` table — both created by the
  // host Laravel app, so we only create our own companion tables here.
  await pool.query(`
    CREATE TABLE IF NOT EXISTS pdf_user_accounts (
      user_id BIGINT UNSIGNED PRIMARY KEY,
      first_name VARCHAR(100) NOT NULL DEFAULT '',
      last_name VARCHAR(100) NOT NULL DEFAULT '',
      country VARCHAR(100) NOT NULL DEFAULT '',
      plan_key VARCHAR(20) NOT NULL DEFAULT 'free',
      pages_remaining INT NOT NULL DEFAULT 0,
      domains TEXT NULL,
      created_at VARCHAR(40) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await pool.query(`
    CREATE TABLE IF NOT EXISTS pdf_documents (
      id VARCHAR(40) PRIMARY KEY,
      user_id BIGINT UNSIGNED NOT NULL,
      name VARCHAR(255) NOT NULL,
      source VARCHAR(10) NOT NULL,
      source_url TEXT NULL,
      status VARCHAR(20) NOT NULL,
      pages INT NOT NULL DEFAULT 0,
      size_bytes BIGINT NOT NULL DEFAULT 0,
      storage_path TEXT NULL,
      remediated_path TEXT NULL,
      remediated_name VARCHAR(255) NULL,
      remediated_pages INT NULL,
      remediated_at VARCHAR(40) NULL,
      created_at VARCHAR(40) NOT NULL,
      INDEX (user_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await pool.query(`
    CREATE TABLE IF NOT EXISTS pdf_jobs (
      id VARCHAR(40) PRIMARY KEY,
      user_id BIGINT UNSIGNED NOT NULL,
      document_ids TEXT NOT NULL,
      total_pages INT NOT NULL DEFAULT 0,
      progress INT NOT NULL DEFAULT 0,
      status VARCHAR(20) NOT NULL,
      partial TINYINT NOT NULL DEFAULT 0,
      created_at VARCHAR(40) NOT NULL,
      completed_at VARCHAR(40) NULL,
      INDEX (user_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await pool.query(`
    CREATE TABLE IF NOT EXISTS pdf_orders (
      id VARCHAR(40) PRIMARY KEY,
      user_id BIGINT UNSIGNED NOT NULL,
      plan_key VARCHAR(20) NOT NULL,
      amount DECIMAL(10,2) NOT NULL DEFAULT 0,
      billing TEXT NULL,
      card_last4 VARCHAR(4) NULL,
      created_at VARCHAR(40) NOT NULL,
      INDEX (user_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  // Which domain each order (user_packages row) was purchased for.
  await pool.query(`
    CREATE TABLE IF NOT EXISTS pdf_order_domains (
      user_package_id INT PRIMARY KEY,
      domain VARCHAR(255) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  // User domains are stored in the existing `websites` table. Add a unique
  // index so concurrent "Add Domain" calls (or a duplicate backfill pass)
  // can't create two rows for the same user+domain — enforced by MySQL
  // rather than a check-then-insert race in application code.
  const [websiteIdx] = await pool.query<RowDataPacket[]>(
    `SELECT 1 FROM information_schema.STATISTICS
      WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'websites' AND INDEX_NAME = 'websites_user_domain_unique'
      LIMIT 1`
  );
  if (websiteIdx.length === 0) {
    await pool.query('ALTER TABLE websites ADD UNIQUE INDEX websites_user_domain_unique (user_id, domain)');
  }

  await pool.query(`
    CREATE TABLE IF NOT EXISTS pdf_emails (
      id VARCHAR(40) PRIMARY KEY,
      user_id BIGINT UNSIGNED NOT NULL,
      to_email VARCHAR(191) NOT NULL,
      subject VARCHAR(255) NOT NULL,
      template VARCHAR(40) NOT NULL,
      file VARCHAR(255) NOT NULL,
      created_at VARCHAR(40) NOT NULL,
      INDEX (user_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  // Which of the user's domains a crawler-discovered document came from (the
  // PDF itself may be hosted elsewhere — a CDN, a gov site, etc. — so this is
  // tracked separately from source_url's own host). Lets the Website Scan tab
  // filter its list to just the currently-selected domain.
  const [docDomainCol] = await pool.query<RowDataPacket[]>(
    `SELECT 1 FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'pdf_documents' AND COLUMN_NAME = 'domain'
      LIMIT 1`
  );
  if (docDomainCol.length === 0) {
    await pool.query('ALTER TABLE pdf_documents ADD COLUMN domain VARCHAR(255) NULL AFTER source_url');
  }

  // Tracks how far a domain's sitemap has been swept so repeated crawls
  // (e.g. clicking "Find PDFs" again on a large site) advance through the
  // whole sitemap over successive calls instead of re-fetching the same
  // first N pages every time.
  await pool.query(`
    CREATE TABLE IF NOT EXISTS pdf_crawl_progress (
      user_id BIGINT UNSIGNED NOT NULL,
      domain VARCHAR(255) NOT NULL,
      sitemap_offset INT UNSIGNED NOT NULL DEFAULT 0,
      updated_at VARCHAR(40) NOT NULL,
      PRIMARY KEY (user_id, domain)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);
}

// ---------------------------------------------------------------------------
// Package catalogue lives in the existing `package_details` table.
//   slug        -> plan key (free/small/medium/large/xlarge)
//   page_views  -> page allowance
//   price       -> plan price      strick_price -> struck-through original
//   per-page display value is derived (price / page_views).
// ---------------------------------------------------------------------------
const PLAN_KEYS: PlanId[] = ['free', 'small', 'medium', 'large', 'xlarge'];

/**
 * `package_details.product_type` for AI PDF Remediation packages.
 *
 * Not 0 and not 1: the dashboard's order list renders 0 as a widget/scanner
 * row and 1 as an add-on row, and only falls through to the PDF row for
 * anything else. A PDF plan on either value shows up as the wrong kind of
 * order, without its page count.
 */
const PDF_PRODUCT_TYPE = 2;

/**
 * Plan ids are namespaced in `package_details.slug`, because bare `small` /
 * `medium` / `large` collide with the widget plans in the shared database.
 * The prefix exists only in SQL — everywhere else a plan is still `small`.
 */
const PLAN_SLUG_PREFIX = 'pdf-';
const planSlug = (id: PlanId): string => `${PLAN_SLUG_PREFIX}${id}`;
const planIdFromSlug = (slug: string): PlanId =>
  slug.startsWith(PLAN_SLUG_PREFIX)
    ? (slug.slice(PLAN_SLUG_PREFIX.length) as PlanId)
    : (slug as PlanId);
const PLAN_SLUGS = PLAN_KEYS.map(planSlug);

/**
 * PDF plans are one-off page bundles that never expire, but
 * `user_packages.end_date` is NOT NULL — so a far-future date stands in for
 * "no expiry". Anything sorting or filtering on end_date will see this value.
 */
const NO_EXPIRY_DATE = '9999-12-31 23:59:59';

async function seedPackages(): Promise<void> {
  // Seed only the plan keys that are not already present as active packages.
  const [rows] = await pool.query<RowDataPacket[]>(
    `SELECT slug FROM package_details WHERE product_type = ? AND deleted_at IS NULL AND slug IN (?)`,
    [PDF_PRODUCT_TYPE, PLAN_SLUGS]
  );
  const existing = new Set(rows.map((r) => planIdFromSlug(r.slug)));
  const missing = SEED_PLANS.filter((p) => !existing.has(p.id));
  if (!missing.length) return;

  const now = toMysqlDate(new Date().toISOString());
  for (const p of missing) {
    await pool.execute(
      `INSERT INTO package_details
        (name, slug, short_description, description, page_views, type, price, monthly_price,
         display_price, strick_price, trail_days, status, is_registrations_plan, product_type,
         is_page_price, is_one_time_purchase, is_default, created_at, updated_at)
       VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,
      [p.name, planSlug(p.id), p.description, p.description, p.pages, 0, p.price, p.price,
       p.price, p.originalPrice, 0, 0 /*active*/, 1 /*registration plan*/, PDF_PRODUCT_TYPE,
       0, 1 /*one-off bundle, never renews*/, p.id === 'free' ? 1 : 0, now, now]
    );
  }
}

async function loadPackages(): Promise<void> {
  const [rows] = await pool.query<RowDataPacket[]>(
    `SELECT id, slug, name, page_views, price, strick_price, short_description
       FROM package_details
      WHERE product_type = ? AND deleted_at IS NULL AND status = 0 AND slug IN (?)
      ORDER BY price`,
    [PDF_PRODUCT_TYPE, PLAN_SLUGS]
  );
  const plans: Plan[] = rows
    .filter((r) => PLAN_KEYS.includes(planIdFromSlug(r.slug)))
    .map((r) => {
      const pages = Number(r.page_views) || 0;
      const price = Number(r.price) || 0;
      return {
        id: planIdFromSlug(r.slug),
        name: r.name,
        pages,
        price,
        originalPrice: Number(r.strick_price) || 0,
        perPage: pages > 0 ? Math.round((price / pages) * 100) / 100 : 0,
        description: r.short_description ?? '',
        packageId: Number(r.id),
      };
    });
  setPlans(plans);
}

// ---------------------------------------------------------------------------
// One-time migration from the legacy data/db.json (preserves the demo data).
// Runs only when no app users exist yet.
// ---------------------------------------------------------------------------
async function migrateFromJson(): Promise<void> {
  const [rows] = await pool.query<RowDataPacket[]>('SELECT COUNT(*) AS n FROM pdf_user_accounts');
  if ((rows[0]?.n ?? 0) > 0) return; // already have app data
  if (!fs.existsSync(DB_FILE)) return;

  let legacy: Database;
  try {
    legacy = JSON.parse(fs.readFileSync(DB_FILE, 'utf-8'));
  } catch {
    return;
  }
  if (!legacy.users?.length) return;

  console.log('[store] Migrating legacy db.json into MySQL…');
  const idMap = new Map<string, number>(); // old string id -> new numeric users.id

  for (const u of legacy.users) {
    const newId = await insertUserRow({
      name: `${u.firstName} ${u.lastName}`.trim(),
      email: u.email,
      passwordHash: u.passwordHash,
      company: u.company,
      website: u.website,
      phone: u.phone,
      createdAt: u.createdAt,
    });
    idMap.set(u.id, newId);
    await pool.execute(
      `INSERT INTO pdf_user_accounts (user_id, first_name, last_name, country, plan_key, pages_remaining, domains, created_at)
       VALUES (?,?,?,?,?,?,?,?)`,
      [newId, u.firstName, u.lastName, u.country, u.planId, u.pagesRemaining, JSON.stringify(u.domains ?? []), u.createdAt]
    );
  }

  const mapUser = (oldId: string) => idMap.get(oldId);

  for (const d of legacy.documents ?? []) {
    const uidNum = mapUser(d.userId);
    if (!uidNum) continue;
    await pool.execute(
      `INSERT INTO pdf_documents (id, user_id, name, source, source_url, status, pages, size_bytes, storage_path, remediated_path, remediated_name, remediated_pages, remediated_at, created_at)
       VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,
      [d.id, uidNum, d.name, d.source, d.sourceUrl ?? null, d.status, d.pages, d.sizeBytes, d.storagePath ?? null,
       d.remediatedPath ?? null, d.remediatedName ?? null, d.remediatedPages ?? null, d.remediatedAt ?? null, d.createdAt]
    );
  }
  for (const j of legacy.jobs ?? []) {
    const uidNum = mapUser(j.userId);
    if (!uidNum) continue;
    await pool.execute(
      `INSERT INTO pdf_jobs (id, user_id, document_ids, total_pages, progress, status, partial, created_at, completed_at)
       VALUES (?,?,?,?,?,?,?,?,?)`,
      [j.id, uidNum, JSON.stringify(j.documentIds ?? []), j.totalPages, j.progress, j.status, j.partial ? 1 : 0, j.createdAt, j.completedAt ?? null]
    );
  }
  for (const o of legacy.orders ?? []) {
    const uidNum = mapUser(o.userId);
    if (!uidNum) continue;
    await pool.execute(
      `INSERT INTO pdf_orders (id, user_id, plan_key, amount, billing, card_last4, created_at) VALUES (?,?,?,?,?,?,?)`,
      [o.id, uidNum, o.planId, o.amount, JSON.stringify(o.billing ?? {}), o.cardLast4 ?? null, o.createdAt]
    );
    // Mirror each historical purchase into user_packages.
    const plan = getPlan(o.planId);
    const owner = legacy.users.find((u) => u.id === o.userId);
    if (plan) {
      await recordUserPackage({
        userId: uidNum, plan, kind: 'purchase', amount: o.amount, billing: o.billing,
        cardLast4: o.cardLast4, when: o.createdAt,
        company: owner?.company, email: owner?.email, phone: owner?.phone,
      });
    }
  }
  for (const e of legacy.emails ?? []) {
    const uidNum = mapUser(e.userId);
    if (!uidNum) continue;
    await pool.execute(
      `INSERT INTO pdf_emails (id, user_id, to_email, subject, template, file, created_at) VALUES (?,?,?,?,?,?,?)`,
      [e.id, uidNum, e.to, e.subject, e.template, e.file, e.createdAt]
    );
  }
  console.log(`[store] Migrated ${legacy.users.length} users and ${legacy.documents?.length ?? 0} documents.`);
}

/** Insert an identity row into the shared Laravel `users` table; returns the new numeric id. */
async function insertUserRow(u: {
  name: string; email: string; passwordHash: string; company: string; website?: string; phone: string; createdAt: string;
}): Promise<number> {
  const d = toMysqlDate(u.createdAt);
  const [res] = await pool.execute<ResultSetHeader>(
    `INSERT INTO users (name, email, password, company_name, website, phone, status, department, is_news_show, subscription_canceled, created_at, updated_at)
     VALUES (?,?,?,?,?,?,?,?,?,?,?,?)`,
    [u.name, u.email, u.passwordHash, u.company, u.website ?? null, u.phone, 1, 2, 0, 0, d, d]
  );
  return res.insertId;
}

/**
 * Add a domain to the existing `websites` table (one row per domain).
 * This is the single source of truth for user domains. Uses the unique
 * index on (user_id, domain) so concurrent calls for the same domain are
 * resolved atomically by MySQL instead of an app-level check-then-insert
 * race — safe to call from registration, "Add New Domain", and the
 * one-time backfill below.
 */
export async function ensureWebsiteRow(userId: string, domain: string): Promise<void> {
  const now = toMysqlDate(new Date().toISOString());
  await pool.execute(
    `INSERT INTO websites
      (url, domain, user_id, status, scan_request_to_admin, crawl_automatically, crawl_day,
       scraping_progress_video, video_widget_lang_enable, created_at, updated_at)
     VALUES (?,?,?,?,?,?,?,?,?,?,?)
     ON DUPLICATE KEY UPDATE updated_at = VALUES(updated_at)`,
    [`https://${domain}`, domain, Number(userId), 1, 0, 0, 0, 0, 0, now, now]
  );
}

/**
 * Domain used for the placeholder website row.
 *
 * The dashboard reads `currentWebsite` almost everywhere — plan status, the
 * domain switcher, scripts, reports — so a user with no site at all breaks it.
 * PDF customers who only upload files have no domain, so they get one row
 * flagged `is_placeholder` that the dashboard hides. `.invalid` is reserved by
 * RFC 2606 and can never resolve, so it cannot collide with a real customer
 * domain.
 */
function placeholderDomain(name: string, userId: string): string {
  const slug = String(name || '')
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, '')
    .slice(0, 40);
  return `${slug || `user${userId}`}.invalid`;
}

/**
 * Gives a user a hidden placeholder site when they have no real one, so the
 * dashboard always has a `currentWebsite` to work with.
 */
export async function ensurePlaceholderWebsite(userId: string, name: string): Promise<void> {
  const [rows] = await pool.query<RowDataPacket[]>(
    'SELECT id FROM websites WHERE user_id = ? AND (is_deleted IS NULL OR is_deleted = 0) LIMIT 1',
    [Number(userId)]
  );
  if (rows.length) return; // already has a site — real or placeholder

  const now = toMysqlDate(new Date().toISOString());
  const domain = placeholderDomain(name, userId);
  await pool.execute(
    `INSERT INTO websites
      (url, domain, user_id, status, is_placeholder, scan_request_to_admin, crawl_automatically,
       crawl_day, scraping_progress_video, video_widget_lang_enable, created_at, updated_at)
     VALUES (?,?,?,?,?,?,?,?,?,?,?,?)
     ON DUPLICATE KEY UPDATE updated_at = VALUES(updated_at)`,
    [`https://${domain}`, domain, Number(userId), 1, 1, 0, 0, 0, 0, 0, now, now]
  );
}

/**
 * The `websites.id` an order belongs to. Prefers the domain the purchase was
 * made for, falling back to the user's first site so `website_id` is never 0.
 */
async function resolveWebsiteId(userId: number | string, domain?: string): Promise<number | null> {
  if (domain) {
    const [rows] = await pool.query<RowDataPacket[]>(
      'SELECT id FROM websites WHERE user_id = ? AND domain = ? LIMIT 1',
      [Number(userId), domain]
    );
    if (rows.length) return Number(rows[0].id);
  }
  const [any] = await pool.query<RowDataPacket[]>(
    'SELECT id FROM websites WHERE user_id = ? ORDER BY id LIMIT 1',
    [Number(userId)]
  );
  return any.length ? Number(any[0].id) : null;
}

/**
 * Whether this user has ever taken the free plan. It is once per user, for
 * life — checked against the orders table rather than the current plan, so
 * moving onto a paid plan and back does not hand out a second trial.
 */
export async function hasUsedFreePlan(userId: number | string): Promise<boolean> {
  const [rows] = await pool.query<RowDataPacket[]>(
    `SELECT up.id
       FROM user_packages up
       JOIN package_details pd ON pd.id = up.package_id
      WHERE up.user_id = ? AND pd.product_type = ? AND up.is_trial_period = 1
      LIMIT 1`,
    [Number(userId), PDF_PRODUCT_TYPE]
  );
  return rows.length > 0;
}

export interface SavedCard {
  id: number;
  brand: string;
  last4: string;
  expMonth: number;
  expYear: number;
  isDefault: boolean;
  /** Stripe PaymentMethod id — what the charge is made against. */
  cardId: string;
  funding?: string;
}

/**
 * Cards the dashboard has saved for this user.
 *
 * Read from `user_cards` rather than from Stripe directly, so both applications
 * agree on which cards exist and which is default. Only rows carrying a Stripe
 * PaymentMethod (`pm_…`) are offered: older rows hold legacy token/card ids
 * that cannot be charged the same way.
 */
export async function listSavedCards(userId: number | string): Promise<SavedCard[]> {
  const [rows] = await pool.query<RowDataPacket[]>(
    `SELECT id, name, last_digits, exp_date, card_type, funding_type, card_id, is_default
       FROM user_cards
      WHERE user_id = ? AND deleted_at IS NULL AND card_id LIKE 'pm\\_%'
      ORDER BY is_default DESC, id DESC`,
    [Number(userId)]
  );
  return rows.map((r) => {
    const exp = r.exp_date ? new Date(r.exp_date) : null;
    return {
      id: Number(r.id),
      brand: r.card_type || 'card',
      last4: String(r.last_digits ?? ''),
      expMonth: exp ? exp.getMonth() + 1 : 0,
      expYear: exp ? exp.getFullYear() : 0,
      isDefault: Number(r.is_default) === 1,
      cardId: String(r.card_id),
      funding: r.funding_type ?? undefined,
    };
  });
}

/** Saves a card the user chose to keep, in the table the dashboard reads. */
export async function saveCard(
  userId: number | string,
  card: {
    name?: string;
    last4: string;
    expMonth: number;
    expYear: number;
    brand: string;
    funding?: string;
    paymentMethodId: string;
    customerId?: string;
  }
): Promise<number | null> {
  const [existing] = await pool.query<RowDataPacket[]>(
    'SELECT id FROM user_cards WHERE user_id = ? AND card_id = ? AND deleted_at IS NULL LIMIT 1',
    [Number(userId), card.paymentMethodId]
  );
  if (existing.length) return Number(existing[0].id);

  const now = toMysqlDate(new Date().toISOString());
  // Last day of the expiry month, matching how the dashboard stores exp_date.
  const expDate = new Date(Date.UTC(card.expYear, card.expMonth, 0));
  const [res] = await pool.execute<ResultSetHeader>(
    `INSERT INTO user_cards
      (user_id, name, last_digits, exp_date, card_type, funding_type, card_id, customer_id,
       is_default, created_at, updated_at)
     VALUES (?,?,?,?,?,?,?,?,?,?,?)`,
    [
      Number(userId), card.name ?? null, card.last4,
      toMysqlDate(expDate.toISOString()).slice(0, 10),
      card.brand, card.funding ?? null, card.paymentMethodId, card.customerId ?? null,
      0, now, now,
    ]
  );
  return res.insertId;
}

export interface AppliedCoupon {
  id: number;
  code: string;
  /** Amount taken off the plan price, already capped and floored at zero. */
  discount: number;
}

/**
 * Validates a coupon against the dashboard's `coupons` table.
 *
 * Only fixed-amount (1) and percentage (2) coupons apply to PDF plans. Free-days
 * (0) extends a trial, which a one-off page bundle does not have, and free-plan
 * (3) belongs to subscription plans — both are rejected rather than silently
 * discounting nothing.
 *
 * Returns a reason instead of throwing so the caller can show it to the user.
 */
export async function validateCoupon(
  code: string,
  plan: Plan,
  userId: number | string
): Promise<{ ok: true; coupon: AppliedCoupon } | { ok: false; reason: string }> {
  const trimmed = String(code || '').trim();
  if (!trimmed) return { ok: false, reason: 'Enter a coupon code.' };

  const [rows] = await pool.query<RowDataPacket[]>(
    `SELECT id, coupon_code, coupon_type, coupon_value, max_coupon_value, used_limit,
            plan_ids, start_date, end_date
       FROM coupons
      WHERE coupon_code = ? AND status = 0 AND deleted_at IS NULL
      LIMIT 1`,
    [trimmed]
  );
  if (!rows.length) return { ok: false, reason: 'This coupon code is not valid.' };
  const c = rows[0];

  const now = new Date();
  if (c.start_date && new Date(c.start_date) > now) {
    return { ok: false, reason: 'This coupon is not active yet.' };
  }
  if (c.end_date && new Date(c.end_date) < now) {
    return { ok: false, reason: 'This coupon has expired.' };
  }

  const type = Number(c.coupon_type);
  if (type !== 1 && type !== 2) {
    return { ok: false, reason: 'This coupon does not apply to PDF plans.' };
  }

  // plan_ids restricts a coupon to specific packages; empty means "any".
  const planIds = String(c.plan_ids || '')
    .split(',')
    .map((s) => s.trim())
    .filter(Boolean);
  if (planIds.length && plan.packageId && !planIds.includes(String(plan.packageId))) {
    return { ok: false, reason: 'This coupon does not apply to the selected plan.' };
  }

  // used_limit is per customer; 0 means unlimited.
  const limit = Number(c.used_limit) || 0;
  if (limit > 0) {
    const [used] = await pool.query<RowDataPacket[]>(
      `SELECT COUNT(*) AS n FROM coupon_histories
        WHERE user_id = ? AND coupon_id = ? AND deleted_at IS NULL`,
      [Number(userId), Number(c.id)]
    );
    if (Number(used[0]?.n ?? 0) >= limit) {
      return { ok: false, reason: 'You have already used this coupon.' };
    }
  }

  const value = Number(c.coupon_value) || 0;
  const cap = Number(c.max_coupon_value) || 0; // 0 = uncapped
  let discount = type === 1 ? value : (plan.price * value) / 100;
  if (cap > 0) discount = Math.min(discount, cap);
  discount = Math.max(0, Math.min(Math.round(discount * 100) / 100, plan.price));

  return { ok: true, coupon: { id: Number(c.id), code: c.coupon_code, discount } };
}

/** Records a redemption so per-customer usage limits stay accurate. */
export async function recordCouponUse(
  userId: number | string,
  coupon: AppliedCoupon,
  websiteId: number | null
): Promise<void> {
  const now = toMysqlDate(new Date().toISOString());
  await pool.execute(
    `INSERT INTO coupon_histories
      (user_id, website_id, coupon_code, coupon_id, start_time, status, created_at, updated_at)
     VALUES (?,?,?,?,?,?,?,?)`,
    [Number(userId), websiteId, coupon.code, coupon.id, now, 0, now, now]
  );
}

/** How far into a domain's sitemap the last crawl got to (0 if never crawled). */
export async function getCrawlOffset(userId: string, domain: string): Promise<number> {
  const [rows] = await pool.query<RowDataPacket[]>(
    `SELECT sitemap_offset FROM pdf_crawl_progress WHERE user_id = ? AND domain = ? LIMIT 1`,
    [Number(userId), domain]
  );
  return rows.length ? Number(rows[0].sitemap_offset) || 0 : 0;
}

/** Persist how far the sitemap sweep reached, so the next crawl continues from there. */
export async function saveCrawlOffset(userId: string, domain: string, offset: number): Promise<void> {
  const now = toMysqlDate(new Date().toISOString());
  await pool.execute(
    `INSERT INTO pdf_crawl_progress (user_id, domain, sitemap_offset, updated_at)
     VALUES (?,?,?,?)
     ON DUPLICATE KEY UPDATE sitemap_offset = VALUES(sitemap_offset), updated_at = VALUES(updated_at)`,
    [Number(userId), domain, offset, now]
  );
}

/**
 * One-time backfill: domains that only exist in the legacy
 * `pdf_user_accounts.domains` JSON column (from before `websites` was used)
 * get copied into `websites`. Idempotent (ensureWebsiteRow dedups), so it's
 * cheap to run on every boot.
 */
async function migrateDomainsToWebsites(): Promise<void> {
  const [rows] = await pool.query<RowDataPacket[]>('SELECT user_id, domains FROM pdf_user_accounts');
  for (const r of rows) {
    const domains = safeJsonArray(r.domains);
    for (const domain of domains) {
      await ensureWebsiteRow(String(r.user_id), domain);
    }
  }
}

// ---------------------------------------------------------------------------
// Load the in-memory working set from MySQL.
// ---------------------------------------------------------------------------
async function loadAll(): Promise<void> {
  const [userRows] = await pool.query<RowDataPacket[]>(`
    SELECT u.id, u.email, u.password, u.company_name, u.website, u.phone, u.stripe_customer_id,
           a.first_name, a.last_name, a.country, a.plan_key, a.pages_remaining, a.created_at
    FROM users u JOIN pdf_user_accounts a ON a.user_id = u.id
    ORDER BY u.id
  `);

  // Domains are sourced from the `websites` table (one row per domain),
  // not from pdf_user_accounts — grouped here into a per-user list.
  const [websiteRows] = await pool.query<RowDataPacket[]>(
    `SELECT user_id, domain FROM websites WHERE deleted_at IS NULL AND domain IS NOT NULL AND domain <> '' ORDER BY id`
  );
  const domainsByUser = new Map<number, string[]>();
  for (const w of websiteRows) {
    const list = domainsByUser.get(w.user_id) ?? [];
    list.push(w.domain);
    domainsByUser.set(w.user_id, list);
  }

  db.users = userRows.map<User>((r) => ({
    id: String(r.id),
    firstName: r.first_name ?? '',
    lastName: r.last_name ?? '',
    email: r.email,
    country: r.country ?? '',
    phone: r.phone ?? '',
    company: r.company_name ?? '',
    website: r.website ?? undefined,
    passwordHash: r.password,
    stripeCustomerId: r.stripe_customer_id ?? undefined,
    planId: (r.plan_key ?? 'free') as PlanId,
    pagesRemaining: Number(r.pages_remaining ?? 0),
    domains: domainsByUser.get(r.id) ?? [],
    createdAt: r.created_at,
  }));

  const [docRows] = await pool.query<RowDataPacket[]>('SELECT * FROM pdf_documents ORDER BY created_at');
  db.documents = docRows.map<PdfDocument>((r) => ({
    id: r.id,
    userId: String(r.user_id),
    name: r.name,
    source: r.source,
    sourceUrl: r.source_url ?? undefined,
    domain: r.domain ?? undefined,
    status: r.status,
    pages: Number(r.pages),
    sizeBytes: Number(r.size_bytes),
    storagePath: r.storage_path ?? undefined,
    remediatedPath: r.remediated_path ?? undefined,
    remediatedName: r.remediated_name ?? undefined,
    remediatedPages: r.remediated_pages == null ? undefined : Number(r.remediated_pages),
    remediatedAt: r.remediated_at ?? undefined,
    createdAt: r.created_at,
  }));

  const [jobRows] = await pool.query<RowDataPacket[]>('SELECT * FROM pdf_jobs ORDER BY created_at');
  db.jobs = jobRows.map<RemediationJob>((r) => ({
    id: r.id,
    userId: String(r.user_id),
    documentIds: safeJsonArray(r.document_ids),
    totalPages: Number(r.total_pages),
    progress: Number(r.progress),
    status: r.status,
    partial: Boolean(r.partial),
    createdAt: r.created_at,
    completedAt: r.completed_at ?? undefined,
  }));

  const [orderRows] = await pool.query<RowDataPacket[]>('SELECT * FROM pdf_orders ORDER BY created_at');
  db.orders = orderRows.map<Order>((r) => ({
    id: r.id,
    userId: String(r.user_id),
    planId: r.plan_key as PlanId,
    amount: Number(r.amount),
    billing: safeJsonObject(r.billing),
    cardLast4: r.card_last4 ?? '',
    createdAt: r.created_at,
  }));

  const [emailRows] = await pool.query<RowDataPacket[]>('SELECT * FROM pdf_emails ORDER BY created_at');
  db.emails = emailRows.map<OutboxEmail>((r) => ({
    id: r.id,
    userId: String(r.user_id),
    to: r.to_email,
    subject: r.subject,
    template: r.template,
    file: r.file,
    createdAt: r.created_at,
  }));
}

function safeJsonArray(v: unknown): string[] {
  try { const a = JSON.parse(String(v ?? '[]')); return Array.isArray(a) ? a : []; } catch { return []; }
}
function safeJsonObject(v: unknown): Record<string, string> {
  try { const o = JSON.parse(String(v ?? '{}')); return o && typeof o === 'object' ? o : {}; } catch { return {}; }
}

// ---------------------------------------------------------------------------
// Public API used by routes.
// ---------------------------------------------------------------------------

/** Create a new account: identity row in `users` + app state in `pdf_user_accounts`. */
export async function createUser(input: {
  firstName: string; lastName: string; email: string; country: string;
  phone: string; company: string; website?: string; passwordHash: string; domains: string[];
}): Promise<User> {
  const createdAt = new Date().toISOString();
  const id = await insertUserRow({
    name: `${input.firstName} ${input.lastName}`.trim(),
    email: input.email,
    passwordHash: input.passwordHash,
    company: input.company,
    website: input.website,
    phone: input.phone,
    createdAt,
  });
  await pool.execute(
    `INSERT INTO pdf_user_accounts (user_id, first_name, last_name, country, plan_key, pages_remaining, domains, created_at)
     VALUES (?,?,?,?,?,?,?,?)`,
    [id, input.firstName, input.lastName, input.country, 'free', 5, JSON.stringify(input.domains), createdAt]
  );
  // Domains live in `websites` — the JSON column above is only kept in sync
  // as a harmless legacy mirror.
  for (const domain of input.domains) {
    await ensureWebsiteRow(String(id), domain);
  }
  const user: User = {
    id: String(id),
    firstName: input.firstName,
    lastName: input.lastName,
    email: input.email,
    country: input.country,
    phone: input.phone,
    company: input.company,
    website: input.website,
    passwordHash: input.passwordHash,
    planId: 'free',
    pagesRemaining: 5,
    domains: input.domains,
    createdAt,
  };
  db.users.push(user);
  return user;
}

/** Persist a user's Stripe Customer id (users.stripe_customer_id) and update memory. */
export async function saveStripeCustomerId(userId: string, customerId: string): Promise<void> {
  await pool.execute('UPDATE users SET stripe_customer_id = ? WHERE id = ?', [customerId, Number(userId)]);
  const user = db.users.find((u) => u.id === userId);
  if (user) user.stripeCustomerId = customerId;
}

/** Update a user's password hash (forgot/reset-password flow) — direct, targeted write. */
export async function updateUserPassword(userId: string, passwordHash: string): Promise<void> {
  await pool.execute('UPDATE users SET password = ? WHERE id = ?', [passwordHash, Number(userId)]);
  const user = db.users.find((u) => u.id === userId);
  if (user) user.passwordHash = passwordHash;
}

/**
 * Record a purchase / trial-start entry in the existing `user_packages` table.
 * Called when a user starts the free trial (registration) and on every checkout.
 */
export async function recordUserPackage(opts: {
  userId: number | string;
  plan: Plan;
  kind: 'trial' | 'purchase';
  amount?: number;
  firstName?: string;
  lastName?: string;
  company?: string;
  email?: string;
  phone?: string;
  billing?: Record<string, string>;
  cardLast4?: string;
  coupon?: string;
  when?: string; // ISO timestamp; defaults to now
  /** Domain this order applies to (stored in pdf_order_domains). */
  domain?: string;
  /** Stripe PaymentIntent id (stored in user_packages.stripe_payment_intent_id). */
  stripePaymentIntentId?: string;
  /** Saved card this was charged to (user_cards.id → user_packages.card_used_id). */
  cardUsedId?: number;
  /** Card details snapshotted onto the order, as the dashboard's own flow does. */
  cardType?: string;
  cardFunding?: string;
  cardExpiry?: string; // YYYY-MM-DD
  /** Applied coupon, for user_packages.coupon_id. */
  couponId?: number;
}): Promise<number | null> {
  const packageId = opts.plan.packageId;
  if (!packageId) {
    console.warn(`[store] Skipped user_packages entry — plan "${opts.plan.id}" has no package_details id.`);
    return null;
  }
  const start = opts.when ? new Date(opts.when) : new Date();
  const startSql = toMysqlDate(start.toISOString());
  // Page bundles are consumed, not rented — they have no expiry date.
  const endSql = NO_EXPIRY_DATE;

  // The order is scoped to a website row so it shows against the right domain
  // in the dashboard. Falls back to the user's first site.
  const websiteId = await resolveWebsiteId(opts.userId, opts.domain);

  const isTrial = opts.kind === 'trial';
  const trialEndSql = isTrial
    ? toMysqlDate(new Date(start.getTime() + 14 * 24 * 60 * 60 * 1000).toISOString())
    : null;
  const amount = opts.amount ?? 0;
  const b = opts.billing ?? {};
  const last4 = opts.cardLast4 && /^\d+$/.test(opts.cardLast4) ? Number(opts.cardLast4) : null;

  const [res] = await pool.execute<ResultSetHeader>(
    `INSERT INTO user_packages
      (user_id, created_user_id, package_id, plan_id, website_id, pdf_plan_pages,
       final_price, price, start_date, end_date,
       trial_end_date, scan_status, status, payment_status, is_trial_period, currency_code, invoice_type,
       bill_first_name, bill_last_name, company_name, bill_address, email, phone,
       last_digits, exp_date, card_type, funding_type, card_used_id,
       coupon_code, coupon_id,
       stripe_payment_intent_id, payment_source, created_at, updated_at)
     VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,
    [
      Number(opts.userId), Number(opts.userId), packageId, 0, websiteId, opts.plan.pages,
      amount, amount, startSql, endSql,
      trialEndSql, 0, 1, isTrial ? 'trial' : 'completed', isTrial ? 1 : 0, 'USD', 0,
      b.firstName ?? opts.firstName ?? null, b.lastName ?? opts.lastName ?? null,
      opts.company ?? null, b.street ?? null, opts.email ?? null, opts.phone ?? null,
      last4, opts.cardExpiry ?? null, opts.cardType ?? null, opts.cardFunding ?? null,
      opts.cardUsedId ?? null,
      opts.coupon ?? null, opts.couponId ?? null,
      opts.stripePaymentIntentId ?? null, opts.stripePaymentIntentId ? 'stripe' : (isTrial ? null : 'mock'),
      startSql, startSql,
    ]
  );
  if (opts.domain) {
    await pool.execute(
      `INSERT INTO pdf_order_domains (user_package_id, domain) VALUES (?, ?)
       ON DUPLICATE KEY UPDATE domain = VALUES(domain)`,
      [res.insertId, opts.domain]
    );
  }
  return res.insertId;
}

// ---- save(): debounced mirror of the mutable working set to MySQL ----------
let saveTimer: NodeJS.Timeout | null = null;
let flushing = false;
let dirtyAgain = false;

export function save(): void {
  if (saveTimer) return;
  saveTimer = setTimeout(() => {
    saveTimer = null;
    void flush();
  }, 150);
}

async function flush(): Promise<void> {
  if (flushing) { dirtyAgain = true; return; }
  flushing = true;
  try {
    await mirrorToDb();
  } catch (err) {
    console.error('[store] MySQL flush failed:', err);
  } finally {
    flushing = false;
    if (dirtyAgain) { dirtyAgain = false; save(); }
  }
}

async function mirrorToDb(): Promise<void> {
  const conn = await pool.getConnection();
  try {
    await conn.beginTransaction();

    // Per-user app state (identity rows in `users` are written on register only).
    await conn.query('DELETE FROM pdf_user_accounts');
    if (db.users.length) {
      await conn.query(
        `INSERT INTO pdf_user_accounts (user_id, first_name, last_name, country, plan_key, pages_remaining, domains, created_at) VALUES ?`,
        [db.users.map((u) => [Number(u.id), u.firstName, u.lastName, u.country, u.planId, u.pagesRemaining, JSON.stringify(u.domains ?? []), u.createdAt])]
      );
    }

    await conn.query('DELETE FROM pdf_documents');
    if (db.documents.length) {
      await conn.query(
        `INSERT INTO pdf_documents (id, user_id, name, source, source_url, domain, status, pages, size_bytes, storage_path, remediated_path, remediated_name, remediated_pages, remediated_at, created_at) VALUES ?`,
        [db.documents.map((d) => [d.id, Number(d.userId), d.name, d.source, d.sourceUrl ?? null, d.domain ?? null, d.status, d.pages, d.sizeBytes, d.storagePath ?? null, d.remediatedPath ?? null, d.remediatedName ?? null, d.remediatedPages ?? null, d.remediatedAt ?? null, d.createdAt])]
      );
    }

    await conn.query('DELETE FROM pdf_jobs');
    if (db.jobs.length) {
      await conn.query(
        `INSERT INTO pdf_jobs (id, user_id, document_ids, total_pages, progress, status, partial, created_at, completed_at) VALUES ?`,
        [db.jobs.map((j) => [j.id, Number(j.userId), JSON.stringify(j.documentIds ?? []), j.totalPages, j.progress, j.status, j.partial ? 1 : 0, j.createdAt, j.completedAt ?? null])]
      );
    }

    await conn.query('DELETE FROM pdf_orders');
    if (db.orders.length) {
      await conn.query(
        `INSERT INTO pdf_orders (id, user_id, plan_key, amount, billing, card_last4, created_at) VALUES ?`,
        [db.orders.map((o) => [o.id, Number(o.userId), o.planId, o.amount, JSON.stringify(o.billing ?? {}), o.cardLast4 ?? null, o.createdAt])]
      );
    }

    await conn.query('DELETE FROM pdf_emails');
    if (db.emails.length) {
      await conn.query(
        `INSERT INTO pdf_emails (id, user_id, to_email, subject, template, file, created_at) VALUES ?`,
        [db.emails.map((e) => [e.id, Number(e.userId), e.to, e.subject, e.template, e.file, e.createdAt])]
      );
    }

    await conn.commit();
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release();
  }
}

/** Seed the demo login when it isn't present (e.g. a fresh DB with no db.json). */
async function ensureDemoUser(): Promise<void> {
  const email = 'demo@skynettechnologies.com';
  if (db.users.some((u) => u.email.toLowerCase() === email)) return;
  const user = await createUser({
    firstName: 'Sunil', lastName: 'Tester', email, country: 'United States',
    phone: '074104 10123', company: 'Skynet Technologies', website: 'https://www.skynettechnologies.com',
    passwordHash: await bcrypt.hash('demo123456', 10), domains: ['www.skynettechnologies.com'],
  });
  user.planId = 'small';
  user.pagesRemaining = 32;
  save();
  console.log(`[store] Seeded demo account: ${email} / demo123456`);
}

/** Boot the store: connect, ensure schema, seed packages, migrate, load. */
export async function initStore(): Promise<void> {
  await pingDb();
  await ensureSchema();
  await seedPackages();
  await loadPackages();
  await migrateFromJson();
  await migrateDomainsToWebsites();
  await loadAll();
  await ensureDemoUser();
  console.log(`[store] MySQL ready — ${db.users.length} users, ${db.documents.length} documents loaded.`);
}