import fs from 'node:fs';
import path from 'node:path';
import { DatabaseSync } from 'node:sqlite';
import { config } from './config.js';

fs.mkdirSync(path.dirname(config.dbPath), { recursive: true });
export const db = new DatabaseSync(config.dbPath);
db.exec('PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON; PRAGMA busy_timeout=5000;');
db.exec(`
CREATE TABLE IF NOT EXISTS orders (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  paypal_order_id TEXT UNIQUE NOT NULL,
  paypal_capture_id TEXT UNIQUE,
  invoice_id TEXT UNIQUE NOT NULL,
  status TEXT NOT NULL,
  payer_email TEXT,
  amount TEXT NOT NULL,
  currency TEXT NOT NULL,
  product_sku TEXT NOT NULL,
  download_token_hash TEXT UNIQUE,
  download_expires_at TEXT,
  download_used_at TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS webhook_events (
  event_id TEXT PRIMARY KEY,
  event_type TEXT NOT NULL,
  received_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_orders_download_token_hash ON orders(download_token_hash);
CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status);
`);

export const statements = {
  insertOrder: db.prepare(`INSERT INTO orders (paypal_order_id, invoice_id, status, amount, currency, product_sku, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`),
  getOrder: db.prepare('SELECT * FROM orders WHERE paypal_order_id = ?'),
  getOrderByToken: db.prepare('SELECT * FROM orders WHERE download_token_hash = ?'),
  completeOrder: db.prepare(`UPDATE orders SET paypal_capture_id=?, status='COMPLETED', payer_email=?, amount=?, currency=?, download_token_hash=?, download_expires_at=?, download_used_at=NULL, updated_at=? WHERE paypal_order_id=?`),
  refreshUnusedToken: db.prepare(`UPDATE orders SET download_token_hash=?, download_expires_at=?, updated_at=? WHERE paypal_order_id=? AND status='COMPLETED' AND download_used_at IS NULL`),
  consumeToken: db.prepare(`UPDATE orders SET download_used_at=?, download_token_hash=NULL, updated_at=? WHERE id=? AND status='COMPLETED' AND download_used_at IS NULL AND download_expires_at>? AND download_token_hash=?`),
  updateStatus: db.prepare(`UPDATE orders SET status=?, updated_at=?, download_token_hash=CASE WHEN ? IN ('REFUNDED','DENIED','REVERSED') THEN NULL ELSE download_token_hash END WHERE paypal_order_id=?`),
  insertWebhookEvent: db.prepare('INSERT OR IGNORE INTO webhook_events (event_id, event_type, received_at) VALUES (?, ?, ?)'),
  getOrderForAdmin: db.prepare(`SELECT paypal_order_id, paypal_capture_id, invoice_id, status, payer_email, amount, currency, download_expires_at, download_used_at, created_at, updated_at FROM orders WHERE paypal_order_id=? OR paypal_capture_id=? OR invoice_id=?`)
};

export function transaction(fn) {
  db.exec('BEGIN IMMEDIATE');
  try {
    const result = fn();
    db.exec('COMMIT');
    return result;
  } catch (error) {
    try { db.exec('ROLLBACK'); } catch {}
    throw error;
  }
}
