import pg from "pg";

const { Pool } = pg;

let pool: pg.Pool | null = null;

export function getPool(): pg.Pool {
  if (pool) return pool;
  const url = process.env.DATABASE_URL?.trim();
  if (!url) {
    throw new Error("DATABASE_URL is required — set your Supabase Postgres connection string in services/api/.env");
  }
  if (url.includes("[YOUR-PASSWORD]") || url.includes("[password]")) {
    throw new Error("Replace [YOUR-PASSWORD] in services/api/.env with your Supabase database password");
  }
  pool = new Pool({
    connectionString: url,
    ssl: url.includes("localhost") || url.includes("127.0.0.1") ? undefined : { rejectUnauthorized: false },
    max: 10,
    connectionTimeoutMillis: 10_000,
    idleTimeoutMillis: 30_000
  });
  return pool;
}

import { runFullCatalogSeed } from "./seed_full_catalog";

export async function verifyDb(): Promise<void> {
  const p = getPool();
  await p.query("SELECT 1");
  await p.query("ALTER TABLE products ADD COLUMN IF NOT EXISTS reorder_level INT NOT NULL DEFAULT 10");

  try {
    // 1. Delete all products that are not fashion-related
    await p.query(`
      DELETE FROM products 
      WHERE category NOT IN ('Apparel', 'Outerwear', 'Accessories', 'Bottomwear', 'Dresses')
    `);

    // 2. Fetch store id
    const storeRes = await p.query("SELECT id FROM stores LIMIT 1");
    if (storeRes.rows.length > 0) {
      const countRes = await p.query("SELECT COUNT(*) FROM products");
      const count = parseInt(countRes.rows[0].count, 10);
      
      const priceRes = await p.query("SELECT MIN(unit_price) as min_p, MAX(unit_price) as max_p FROM products");
      const minP = parseFloat(priceRes.rows[0].min_p || "0");
      const maxP = parseFloat(priceRes.rows[0].max_p || "0");

      if (count < 70 || minP < 350 || maxP > 1450) {
        console.log("Seeding full catalog with 70 unique products (400-1400 price range)...");
        try {
          await runFullCatalogSeed();
        } catch (e) {
          console.error("Failed to run full catalog seed", e);
        }
      }
    }
  } catch (err) {
    console.error("verifyDb error:", err);
  }
}

export function round2(n: number): number {
  return Math.round(n * 100) / 100;
}