/**
 * Populate the dashboard SQLite database with fake usage data against
 * the first account in the DB so the Usage tab isn't empty.
 *
 * Run with: `npm run seed` (which uses node --env-file=.env).
 *
 * If there's no account yet, we create an anonymous one and also mint
 * two keys so the dashboard has something to show when you first log in.
 */

import Database from 'better-sqlite3';
import { mkdirSync } from 'node:fs';
import { dirname, resolve } from 'node:path';
import { createHash, randomBytes, randomUUID } from 'node:crypto';

const DB_PATH = resolve(process.env.DATABASE_PATH || './data/dashboard.db');
mkdirSync(dirname(DB_PATH), { recursive: true });

const db = new Database(DB_PATH);
db.pragma('journal_mode = WAL');
db.pragma('foreign_keys = ON');

// Make sure the schema exists (same as src/lib/server/db.ts).
db.exec(`
  CREATE TABLE IF NOT EXISTS accounts (
    id TEXT PRIMARY KEY,
    email TEXT UNIQUE,
    plan TEXT NOT NULL DEFAULT 'free',
    created_at INTEGER NOT NULL
  );
  CREATE TABLE IF NOT EXISTS api_keys (
    id TEXT PRIMARY KEY,
    account_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    key_hash TEXT NOT NULL UNIQUE,
    key_prefix TEXT NOT NULL,
    name TEXT NOT NULL,
    scopes TEXT NOT NULL DEFAULT '["*"]',
    active INTEGER NOT NULL DEFAULT 1,
    created_at INTEGER NOT NULL,
    last_used_at INTEGER
  );
  CREATE INDEX IF NOT EXISTS idx_api_keys_account ON api_keys(account_id);
  CREATE TABLE IF NOT EXISTS magic_links (
    id TEXT PRIMARY KEY,
    account_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    token TEXT NOT NULL UNIQUE,
    expires_at INTEGER NOT NULL,
    used INTEGER NOT NULL DEFAULT 0,
    created_at INTEGER NOT NULL
  );
  CREATE INDEX IF NOT EXISTS idx_magic_links_token ON magic_links(token);
  CREATE TABLE IF NOT EXISTS usage_events (
    id TEXT PRIMARY KEY,
    api_key_id TEXT NOT NULL REFERENCES api_keys(id) ON DELETE CASCADE,
    dataset TEXT NOT NULL,
    timestamp INTEGER NOT NULL
  );
  CREATE INDEX IF NOT EXISTS idx_usage_key ON usage_events(api_key_id);
  CREATE INDEX IF NOT EXISTS idx_usage_timestamp ON usage_events(timestamp);
  CREATE INDEX IF NOT EXISTS idx_usage_dataset ON usage_events(dataset);
`);

const now = Date.now();

const ALPHABET =
  'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';

function mintKey() {
  const bytes = randomBytes(32);
  let random = '';
  for (let i = 0; i < 32; i++) {
    random += ALPHABET[bytes[i] % ALPHABET.length];
  }
  const key = `ti_${random}`;
  const hash = createHash('sha256').update(key).digest('hex');
  const prefix = key.slice(0, 8);
  return { key, hash, prefix };
}

// --- find or create an account ---

/** @typedef {{ id: string, plan: string }} AccountRow */
/** @typedef {{ id: string, name: string }} KeyRow */

let account = /** @type {AccountRow | undefined} */ (
  db
    .prepare(`SELECT id, plan FROM accounts ORDER BY created_at ASC LIMIT 1`)
    .get()
);

if (!account) {
  const id = randomUUID();
  db.prepare(
    `INSERT INTO accounts (id, email, plan, created_at) VALUES (?, NULL, 'dev', ?)`
  ).run(id, now - 1000 * 60 * 60 * 24 * 14); // created 2 weeks ago
  account = { id, plan: 'dev' };
  console.log(`created demo account ${id}`);
} else {
  console.log(`seeding against existing account ${account.id}`);
}

// --- make sure there are at least two active keys ---

const existingKeys = /** @type {KeyRow[]} */ (
  db
    .prepare(`SELECT id, name FROM api_keys WHERE account_id = ? AND active = 1`)
    .all(account.id)
);

/** @type {KeyRow[]} */
const keyRows =
  existingKeys.length >= 2
    ? existingKeys
    : (() => {
        /** @type {KeyRow[]} */
        const created = [];
        for (const name of ['production ingest', 'research notebook']) {
          const { hash, prefix } = mintKey();
          const id = randomUUID();
          db.prepare(
            `INSERT INTO api_keys
              (id, account_id, key_hash, key_prefix, name, scopes, active, created_at)
             VALUES (?, ?, ?, ?, ?, '["*"]', 1, ?)`
          ).run(id, account.id, hash, prefix, name, now - 1000 * 60 * 60 * 24 * 10);
          created.push({ id, name });
        }
        return [...existingKeys, ...created];
      })();

// --- wipe usage events we seeded previously and re-generate ---

db.prepare(
  `DELETE FROM usage_events WHERE api_key_id IN
    (SELECT id FROM api_keys WHERE account_id = ?)`
).run(account.id);

// Generate roughly 2,400 events distributed over the past 30 days, heavily
// weighted toward irs-990 and sec-edgar (the flagship datasets).
const weights = {
  'irs-990': 28,
  'sec-edgar': 22,
  'sec-13f': 14,
  'fec-contributions': 10,
  'nih-reporter': 8,
  'pacer': 6,
  'fda-faers': 4,
  'osha': 3,
  'usaspending': 3,
  'cfpb-complaints': 2
};
const weighted = /** @type {string[]} */ ([]);
for (const [slug, w] of Object.entries(weights)) {
  for (let i = 0; i < w; i++) weighted.push(slug);
}

const THIRTY_DAYS = 30 * 24 * 60 * 60 * 1000;
const insertEvent = db.prepare(
  `INSERT INTO usage_events (id, api_key_id, dataset, timestamp) VALUES (?, ?, ?, ?)`
);

const TOTAL = 2400;
const tx = db.transaction(() => {
  for (let i = 0; i < TOTAL; i++) {
    const dataset = weighted[Math.floor(Math.random() * weighted.length)];
    const keyRow = keyRows[Math.floor(Math.random() * keyRows.length)];
    const age = Math.floor(Math.random() * THIRTY_DAYS);
    insertEvent.run(randomUUID(), keyRow.id, dataset, now - age);
  }
});
tx();

// Update last_used_at on each key to the most recent event timestamp.
for (const row of keyRows) {
  const latest = /** @type {{ ts: number | null } | undefined} */ (
    db
      .prepare(
        `SELECT MAX(timestamp) AS ts FROM usage_events WHERE api_key_id = ?`
      )
      .get(row.id)
  );
  if (latest?.ts) {
    db.prepare(`UPDATE api_keys SET last_used_at = ? WHERE id = ?`).run(
      latest.ts,
      row.id
    );
  }
}

console.log(`seeded ${TOTAL} usage events against ${keyRows.length} keys`);
console.log(`db: ${DB_PATH}`);
db.close();