import sqlite3 from "sqlite3";
import { promisify } from "util";

const DB_PATH = `${process.cwd()}/database.sqlite`;
const db = new sqlite3.Database(DB_PATH);

const run = promisify(db.run.bind(db));
const get = promisify(db.get.bind(db));
const all = promisify(db.all.bind(db));

let readyPromise = null;

/**
 * Storage for product groups (a.k.a. linked products).
 *
 * A group links sibling products that belong together (e.g. the same item sold
 * as separate "Shirt Red" / "Shirt Blue" products). When a group is published,
 * the app writes its member list into a product metafield so the theme can
 * render crawlable cross-links for SEO (see metafields.js).
 *
 * - product_groups: one row per group (name, storefront label, style, status).
 * - group_members:  the products in each group + their swatch label/colour.
 */
export async function initDb() {
  if (readyPromise) return readyPromise;
  readyPromise = (async () => {
    await run(`
      CREATE TABLE IF NOT EXISTS product_groups (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        shop        TEXT NOT NULL,
        name        TEXT NOT NULL,
        label       TEXT NOT NULL DEFAULT 'Color',          -- option label shown on storefront
        style       TEXT NOT NULL DEFAULT 'color',          -- color | image | button
        status      TEXT NOT NULL DEFAULT 'published',      -- draft | published (draft = hidden)
        source      TEXT NOT NULL DEFAULT 'manual',         -- manual | auto (how it was created)
        created_at  TEXT NOT NULL DEFAULT (datetime('now'))
      )
    `);

    await run(`
      CREATE TABLE IF NOT EXISTS group_members (
        id            INTEGER PRIMARY KEY AUTOINCREMENT,
        shop          TEXT NOT NULL,
        group_id      INTEGER NOT NULL,
        product_id    TEXT NOT NULL,                         -- Shopify GID
        product_title TEXT NOT NULL DEFAULT '',
        handle        TEXT NOT NULL DEFAULT '',
        image         TEXT NOT NULL DEFAULT '',
        swatch_value  TEXT NOT NULL DEFAULT '',              -- label for this member (e.g. "Red")
        swatch_color  TEXT NOT NULL DEFAULT '',              -- hex for color style
        position      INTEGER NOT NULL DEFAULT 0,
        UNIQUE(shop, group_id, product_id)
      )
    `);
    await run(
      `CREATE INDEX IF NOT EXISTS idx_group_members_product
         ON group_members(shop, product_id)`
    );

    // Lightweight migration: add columns if upgrading from an older schema
    // (CREATE TABLE IF NOT EXISTS won't add them to an existing table).
    await addColumnIfMissing("product_groups", "status", "TEXT NOT NULL DEFAULT 'published'");
    await addColumnIfMissing("product_groups", "source", "TEXT NOT NULL DEFAULT 'manual'");
  })();
  return readyPromise;
}

async function addColumnIfMissing(table, column, definition) {
  const cols = await all(`PRAGMA table_info(${table})`);
  if (!cols.some((c) => c.name === column)) {
    await run(`ALTER TABLE ${table} ADD COLUMN ${column} ${definition}`);
  }
}

export async function listGroups(shop) {
  await initDb();
  const groups = await all(
    `SELECT * FROM product_groups WHERE shop = ? ORDER BY created_at DESC`,
    [shop]
  );
  for (const g of groups) g.members = await getMembers(shop, g.id);
  return groups;
}

export async function getGroup(shop, id) {
  await initDb();
  const g = await get(
    `SELECT * FROM product_groups WHERE shop = ? AND id = ?`,
    [shop, id]
  );
  if (g) g.members = await getMembers(shop, id);
  return g;
}

async function getMembers(shop, groupId) {
  return all(
    `SELECT * FROM group_members WHERE shop = ? AND group_id = ? ORDER BY position, id`,
    [shop, groupId]
  );
}

/** Product ids already in ANY group (so auto-detect won't duplicate them). */
export async function getGroupedProductIds(shop) {
  await initDb();
  const rows = await all(
    `SELECT DISTINCT product_id FROM group_members WHERE shop = ?`,
    [shop]
  );
  return new Set(rows.map((r) => r.product_id));
}

export async function createGroup(
  shop,
  { name, label, style, members, status, source }
) {
  await initDb();
  await run(
    `INSERT INTO product_groups (shop, name, label, style, status, source)
     VALUES (?, ?, ?, ?, ?, ?)`,
    [
      shop,
      name,
      label || "Color",
      style || "color",
      status === "draft" ? "draft" : "published",
      source === "auto" ? "auto" : "manual",
    ]
  );
  const { id } = await get(`SELECT last_insert_rowid() AS id`);
  await replaceMembers(shop, id, members || []);
  return getGroup(shop, id);
}

export async function updateGroup(
  shop,
  id,
  { name, label, style, members, status }
) {
  await initDb();
  // Partial update so callers can flip just the status (publish toggle).
  const sets = [];
  const args = [];
  if (name != null) (sets.push("name = ?"), args.push(name));
  if (label != null) (sets.push("label = ?"), args.push(label || "Color"));
  if (style != null) (sets.push("style = ?"), args.push(style || "color"));
  if (status != null)
    (sets.push("status = ?"), args.push(status === "draft" ? "draft" : "published"));
  if (sets.length) {
    await run(
      `UPDATE product_groups SET ${sets.join(", ")} WHERE shop = ? AND id = ?`,
      [...args, shop, id]
    );
  }
  if (members) await replaceMembers(shop, id, members);
  return getGroup(shop, id);
}

export async function deleteGroup(shop, id) {
  await initDb();
  await run(`DELETE FROM group_members WHERE shop = ? AND group_id = ?`, [shop, id]);
  await run(`DELETE FROM product_groups WHERE shop = ? AND id = ?`, [shop, id]);
}

async function replaceMembers(shop, groupId, members) {
  await run(`DELETE FROM group_members WHERE shop = ? AND group_id = ?`, [
    shop,
    groupId,
  ]);
  for (let i = 0; i < members.length; i++) {
    const m = members[i];
    await run(
      `INSERT INTO group_members
         (shop, group_id, product_id, product_title, handle, image, swatch_value, swatch_color, position)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        shop,
        groupId,
        m.productId,
        m.productTitle || "",
        m.handle || "",
        m.image || "",
        m.swatchValue || "",
        m.swatchColor || "",
        i,
      ]
    );
  }
}

export default db;