import initSqlJs from 'sql.js';
import path from 'path';
import { fileURLToPath } from 'url';
import fs from 'fs';

const __dirname = path.dirname(fileURLToPath(import.meta.url));
const dataDir = path.join(__dirname, '..', 'data');
const dbPath = path.join(dataDir, 'signalpro.db');

let _db = null;

function saveDb() {
  if (!_db) return;
  if (!fs.existsSync(dataDir)) fs.mkdirSync(dataDir, { recursive: true });
  fs.writeFileSync(dbPath, Buffer.from(_db.export()));
}

function stmtAll(stmt) {
  const rows = [];
  while (stmt.step()) rows.push(stmt.getAsObject());
  stmt.free();
  return rows;
}

export const db = {
  prepare(sql) {
    return {
      get(...params) {
        const stmt = _db.prepare(sql);
        if (params.length) stmt.bind(params);
        const row = stmt.step() ? stmt.getAsObject() : undefined;
        stmt.free();
        return row;
      },
      all(...params) {
        const stmt = _db.prepare(sql);
        if (params.length) stmt.bind(params);
        return stmtAll(stmt);
      },
      run(...params) {
        if (params.length) _db.run(sql, params);
        else _db.run(sql);
        const idResult = _db.exec('SELECT last_insert_rowid()');
        const lastInsertRowid = idResult[0]?.values[0]?.[0] ?? 0;
        const changes = _db.getRowsModified();
        saveDb();
        return { lastInsertRowid, changes };
      },
    };
  },
  exec(sql) {
    _db.run(sql);
    saveDb();
  },
};

export async function initDatabase() {
  const wasmPath = path.join(__dirname, '..', 'node_modules', 'sql.js', 'dist', 'sql-wasm.wasm');
  const SQL = await initSqlJs({
    locateFile: () => wasmPath,
  });

  if (!fs.existsSync(dataDir)) fs.mkdirSync(dataDir, { recursive: true });

  if (fs.existsSync(dbPath)) {
    _db = new SQL.Database(fs.readFileSync(dbPath));
  } else {
    _db = new SQL.Database();
  }

  _db.run('PRAGMA foreign_keys = ON');
  _db.run(`
    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      full_name TEXT NOT NULL,
      email TEXT UNIQUE NOT NULL,
      phone TEXT,
      password_hash TEXT NOT NULL,
      role TEXT DEFAULT 'user',
      plan_months INTEGER DEFAULT 1,
      subscription_expires_at TEXT,
      affiliate_code TEXT,
      created_at TEXT DEFAULT (datetime('now'))
    );
    CREATE TABLE IF NOT EXISTS otp_codes (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      email TEXT NOT NULL,
      code TEXT NOT NULL,
      expires_at TEXT NOT NULL,
      used INTEGER DEFAULT 0,
      created_at TEXT DEFAULT (datetime('now'))
    );
    CREATE TABLE IF NOT EXISTS signals (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      pair TEXT NOT NULL,
      pair_name TEXT,
      type TEXT NOT NULL,
      status TEXT NOT NULL DEFAULT 'active',
      result TEXT,
      sent_at TEXT NOT NULL,
      closed_at TEXT,
      last_update_at TEXT,
      created_at TEXT DEFAULT (datetime('now'))
    );
    CREATE TABLE IF NOT EXISTS payments (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id INTEGER NOT NULL,
      plan_months INTEGER NOT NULL,
      original_price INTEGER NOT NULL,
      discount_percent INTEGER DEFAULT 0,
      final_price INTEGER NOT NULL,
      receipt_path TEXT,
      status TEXT DEFAULT 'pending',
      created_at TEXT DEFAULT (datetime('now'))
    );
    CREATE TABLE IF NOT EXISTS cooperation_requests (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      full_name TEXT NOT NULL,
      phone TEXT NOT NULL,
      email TEXT NOT NULL,
      current_job TEXT,
      business_name TEXT,
      message TEXT NOT NULL,
      created_at TEXT DEFAULT (datetime('now'))
    );
    CREATE TABLE IF NOT EXISTS support_tickets (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id INTEGER NOT NULL,
      subject TEXT NOT NULL,
      message TEXT NOT NULL,
      status TEXT DEFAULT 'open',
      created_at TEXT DEFAULT (datetime('now'))
    );
  `);
  saveDb();
}

export const PLAN_PRICES = {
  1: { months: 1, price: 500000, discount: 0 },
  3: { months: 3, price: 1350000, discount: 10 },
  6: { months: 6, price: 2400000, discount: 12 },
  12: { months: 12, price: 2000000, discount: 15 },
};
