import { DatabaseSync } from 'node:sqlite';
import { mkdirSync, chmodSync } from 'node:fs';
import path from 'node:path';

export const PLANS = Object.freeze({ solo: 1, team: 10, business: 100 });
export class CommerceStore {
  constructor(filename) {
    if (!filename) throw new Error('Commerce database path required');
    if (filename !== ':memory:') mkdirSync(path.dirname(filename), { recursive: true, mode: 0o700 });
    this.db = new DatabaseSync(filename);
    if (filename !== ':memory:') chmodSync(filename, 0o600);
    this.db.exec(`PRAGMA foreign_keys=ON; PRAGMA journal_mode=DELETE; PRAGMA busy_timeout=5000;
      CREATE TABLE IF NOT EXISTS accounts(id TEXT PRIMARY KEY,email TEXT NOT NULL UNIQUE,password TEXT NOT NULL,customer TEXT UNIQUE,created INTEGER NOT NULL,role TEXT NOT NULL DEFAULT 'user' CHECK(role IN ('user','admin')));
      CREATE TABLE IF NOT EXISTS sessions(hash TEXT PRIMARY KEY,account TEXT NOT NULL REFERENCES accounts(id),csrf TEXT NOT NULL,expires INTEGER NOT NULL);
      CREATE TABLE IF NOT EXISTS subscriptions(account TEXT PRIMARY KEY REFERENCES accounts(id),subscription TEXT UNIQUE,plan TEXT,status TEXT NOT NULL DEFAULT 'inactive',paid_until INTEGER NOT NULL DEFAULT 0,cancel_end INTEGER NOT NULL DEFAULT 0);
      CREATE TABLE IF NOT EXISTS licenses(id TEXT PRIMARY KEY,account TEXT NOT NULL REFERENCES accounts(id),ordinal INTEGER NOT NULL,name TEXT NOT NULL DEFAULT '',service TEXT NOT NULL DEFAULT '',state TEXT NOT NULL DEFAULT 'enabled',generation INTEGER NOT NULL DEFAULT 0,UNIQUE(account,ordinal));
      CREATE TABLE IF NOT EXISTS devices(id TEXT PRIMARY KEY,license TEXT NOT NULL UNIQUE REFERENCES licenses(id),public_key TEXT NOT NULL,key_hash TEXT NOT NULL UNIQUE,machine_name TEXT NOT NULL,fingerprint TEXT NOT NULL,created INTEGER NOT NULL);
      CREATE TABLE IF NOT EXISTS activation_tokens(hash TEXT PRIMARY KEY,license TEXT NOT NULL REFERENCES licenses(id),expires INTEGER NOT NULL);
      CREATE TABLE IF NOT EXISTS stripe_events(id TEXT PRIMARY KEY,created INTEGER NOT NULL);
      CREATE TABLE IF NOT EXISTS relay_usage(account TEXT NOT NULL REFERENCES accounts(id),month TEXT NOT NULL,bytes INTEGER NOT NULL DEFAULT 0,PRIMARY KEY(account,month));
      CREATE TABLE IF NOT EXISTS account_branding(account TEXT PRIMARY KEY REFERENCES accounts(id),company_name TEXT NOT NULL DEFAULT '',message TEXT NOT NULL DEFAULT '',accent TEXT NOT NULL DEFAULT '#5265e8');
      CREATE TABLE IF NOT EXISTS free_devices(fingerprint TEXT PRIMARY KEY,account TEXT NOT NULL REFERENCES accounts(id));
      CREATE TABLE IF NOT EXISTS registration_usage(ip_hash TEXT NOT NULL,day TEXT NOT NULL,count INTEGER NOT NULL DEFAULT 0,PRIMARY KEY(ip_hash,day));
      CREATE TABLE IF NOT EXISTS monthly_usage(account TEXT NOT NULL REFERENCES accounts(id),month TEXT NOT NULL,shares INTEGER NOT NULL DEFAULT 0,PRIMARY KEY(account,month));
      CREATE TABLE IF NOT EXISTS checkouts(account TEXT PRIMARY KEY REFERENCES accounts(id),plan TEXT NOT NULL,key TEXT NOT NULL,url TEXT,expires INTEGER NOT NULL);`);
    if (!this.db.prepare('PRAGMA table_info(accounts)').all().some(column => column.name === 'role')) this.db.exec("ALTER TABLE accounts ADD COLUMN role TEXT NOT NULL DEFAULT 'user' CHECK(role IN ('user','admin'))");
  }
  get(sql, ...args) { return this.db.prepare(sql).get(...args); }
  all(sql, ...args) { return this.db.prepare(sql).all(...args); }
  run(sql, ...args) { return this.db.prepare(sql).run(...args); }
  transaction(fn) { this.db.exec('BEGIN IMMEDIATE'); try { const result = fn(); this.db.exec('COMMIT'); return result; } catch (error) { this.db.exec('ROLLBACK'); throw error; } }
  close() { this.db.close(); }
}
