// src/db.js
// Uses Node's built-in node:sqlite (Node 22+, run with --experimental-sqlite
// until it graduates out of experimental status). Same approach used on
// MPLINKGROUP's cPanel deployment.
const path = require('path');
const fs = require('fs');
const { DatabaseSync } = require('node:sqlite');
const { hashPassword } = require('./auth');

const DATA_DIR = path.join(__dirname, '..', 'data');
if (!fs.existsSync(DATA_DIR)) fs.mkdirSync(DATA_DIR, { recursive: true });

const DB_PATH = path.join(DATA_DIR, 'yst.db');
const db = new DatabaseSync(DB_PATH);

db.exec('PRAGMA journal_mode = WAL;');
db.exec('PRAGMA foreign_keys = ON;');

db.exec(`
CREATE TABLE IF NOT EXISTS admin_users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  role TEXT NOT NULL DEFAULT 'admin',
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS messages (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT NOT NULL,
  phone TEXT,
  reason TEXT,
  message TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'new',
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS subscribers (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  email TEXT UNIQUE NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS stories (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  category TEXT NOT NULL,        -- education | mentorship | community | welfare | skills | advocacy
  tone TEXT NOT NULL DEFAULT 'tone-sage',
  title TEXT NOT NULL,
  excerpt TEXT NOT NULL,
  body TEXT,
  published INTEGER NOT NULL DEFAULT 1,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS insights (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  tab TEXT NOT NULL,             -- publications | newsletters | opinions
  tone TEXT NOT NULL DEFAULT 'tone-navy',
  badge TEXT NOT NULL DEFAULT 'Update',
  title TEXT NOT NULL,
  excerpt TEXT NOT NULL,
  link TEXT DEFAULT '#',
  published INTEGER NOT NULL DEFAULT 1,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS press (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  tone TEXT NOT NULL DEFAULT 'tone-navy',
  badge TEXT NOT NULL DEFAULT 'Press Release',
  title TEXT NOT NULL,
  excerpt TEXT NOT NULL,
  link TEXT DEFAULT '#',
  published INTEGER NOT NULL DEFAULT 1,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS events (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  event_date TEXT NOT NULL,
  published INTEGER NOT NULL DEFAULT 1,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS chat_logs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  session_id TEXT NOT NULL,
  role TEXT NOT NULL,
  content TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS media (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  media_type TEXT NOT NULL DEFAULT 'image',   -- image | video
  category TEXT NOT NULL DEFAULT 'gallery',   -- gallery | press | event | banner
  title TEXT NOT NULL,
  caption TEXT,
  url TEXT NOT NULL,
  thumbnail_url TEXT,
  published INTEGER NOT NULL DEFAULT 1,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Bank accounts the admin has entered while creating invoices. The most
-- recently used (or explicitly starred) account per currency is offered
-- back as the suggested default next time, but nothing here is hardcoded —
-- an admin can always add another account or edit one in place.
CREATE TABLE IF NOT EXISTS bank_accounts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  currency TEXT NOT NULL,               -- TZS | USD
  bank_name TEXT NOT NULL,
  account_name TEXT NOT NULL,
  account_number TEXT NOT NULL,
  is_default INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Invoices generated from Admin ▸ Invoices. Bank details are snapshotted
-- onto the invoice at generation time (bank_name/account_name/account_number)
-- so a later edit to the saved bank_accounts entry never rewrites history
-- on an invoice that has already gone out.
CREATE TABLE IF NOT EXISTS invoices (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  invoice_number TEXT UNIQUE NOT NULL,
  currency TEXT NOT NULL DEFAULT 'TZS',
  bank_account_id INTEGER,
  bank_name TEXT,
  account_name TEXT,
  account_number TEXT,
  recipient_name TEXT NOT NULL,
  recipient_address TEXT,
  recipient_email TEXT,
  recipient_phone TEXT,
  amount REAL NOT NULL DEFAULT 0,
  purpose TEXT,
  official_purpose TEXT,
  cover_message TEXT,
  status TEXT NOT NULL DEFAULT 'draft',  -- draft | sent | paid
  sent_email_at TEXT,
  sent_whatsapp_at TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (bank_account_id) REFERENCES bank_accounts(id) ON DELETE SET NULL
);

-- Every add / edit / delete / publish action taken by an admin user, kept
-- so the foundation always has an accountable trail of who changed what.
CREATE TABLE IF NOT EXISTS audit_logs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  admin_username TEXT NOT NULL,
  action TEXT NOT NULL,           -- create | update | delete | publish | unpublish | login | login_failed | logout | unlock
  entity TEXT NOT NULL,           -- stories | insights | press | events | media | admin_users | session
  entity_id TEXT,
  details TEXT,
  ip TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Login attempts (success and failure) — the basis for the escalating
-- lockout ("punishment") applied to repeated failed sign-ins.
CREATE TABLE IF NOT EXISTS login_attempts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT NOT NULL,
  ip TEXT,
  success INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Lightweight, privacy-respecting page view log that powers the Analytics
-- overview. IPs are never stored raw — only a salted, truncated hash, just
-- enough to approximate unique visitors.
CREATE TABLE IF NOT EXISTS page_views (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  path TEXT NOT NULL,
  referrer TEXT,
  device TEXT NOT NULL DEFAULT 'desktop',  -- desktop | mobile | tablet
  visitor_hash TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
`);

// ---- lightweight migrations for databases created before these columns
// existed (SQLite's CREATE TABLE IF NOT EXISTS won't retro-add columns) ----
function ensureColumn(table, column, decl) {
  const cols = db.prepare(`PRAGMA table_info(${table})`).all();
  if (!cols.some((c) => c.name === column)) {
    db.exec(`ALTER TABLE ${table} ADD COLUMN ${column} ${decl}`);
  }
}
ensureColumn('admin_users', 'role', "TEXT NOT NULL DEFAULT 'admin'");
ensureColumn('admin_users', 'failed_attempts', 'INTEGER NOT NULL DEFAULT 0');
ensureColumn('admin_users', 'locked_until', 'TEXT');
ensureColumn('admin_users', 'last_login_at', 'TEXT');
ensureColumn('admin_users', 'last_login_ip', 'TEXT');
ensureColumn('invoices', 'recipient_email', 'TEXT');
ensureColumn('invoices', 'recipient_phone', 'TEXT');
ensureColumn('invoices', 'official_purpose', 'TEXT');
ensureColumn('invoices', 'cover_message', 'TEXT');
ensureColumn('invoices', 'sent_email_at', 'TEXT');
ensureColumn('invoices', 'sent_whatsapp_at', 'TEXT');

db.exec('CREATE INDEX IF NOT EXISTS idx_page_views_created ON page_views (created_at);');
db.exec('CREATE INDEX IF NOT EXISTS idx_page_views_path ON page_views (path);');
db.exec('CREATE INDEX IF NOT EXISTS idx_audit_created ON audit_logs (created_at);');
db.exec('CREATE INDEX IF NOT EXISTS idx_login_attempts_created ON login_attempts (created_at);');
db.exec('CREATE INDEX IF NOT EXISTS idx_bank_accounts_currency ON bank_accounts (currency);');
db.exec('CREATE INDEX IF NOT EXISTS idx_invoices_created ON invoices (created_at);');

// ---- seed a default admin user on first run ----
const adminCount = db.prepare('SELECT COUNT(*) AS n FROM admin_users').get().n;
if (adminCount === 0) {
  const username = process.env.ADMIN_INITIAL_USERNAME || 'admin';
  const password = process.env.ADMIN_INITIAL_PASSWORD || 'ChangeMe123!';
  db.prepare('INSERT INTO admin_users (username, password_hash) VALUES (?, ?)')
    .run(username, hashPassword(password));
  console.log(`[db] Seeded admin user "${username}". Log in and change the password.`);
}

// ---- seed a little starter content so the dashboard / pages aren't empty ----
// NOTE: the "stories" table is repurposed here as Featured Projects (category:
// roads | buildings | government | mining). These are clearly-labelled
// placeholder/sample rows — replace them with real project write-ups from
// Admin ▸ Content before publishing.
const storyCount = db.prepare('SELECT COUNT(*) AS n FROM stories').get().n;
if (storyCount === 0) {
  const seedStories = db.prepare(`INSERT INTO stories (category, tone, title, excerpt, body) VALUES (?, ?, ?, ?, ?)`);
  seedStories.run('roads', 'tone-gold', 'Sample entry: Road Contracting Project',
    'Replace this with a real road project write-up — location, scope of works, and outcome.',
    'Add the full project narrative here from Admin \u25b8 Content.');
  seedStories.run('government', 'tone-navy', 'Sample entry: Government Building Project',
    'Replace this with a completed school, dispensary, or other government project.',
    'Add the full project narrative here from Admin \u25b8 Content.');
  seedStories.run('mining', 'tone-sage', 'Sample entry: Mining Equipment Supply Engagement',
    'Replace this with a mining equipment or ore-processing chemical supply case study.',
    'Add the full project narrative here from Admin \u25b8 Content.');
}

const insightCount = db.prepare('SELECT COUNT(*) AS n FROM insights').get().n;
if (insightCount === 0) {
  const seedInsights = db.prepare(`INSERT INTO insights (tab, tone, badge, title, excerpt, link) VALUES (?, ?, ?, ?, ?, ?)`);
  seedInsights.run('publications', 'tone-gold', 'Guide',
    'Sample entry: What to prepare before requesting a road-contracting quote',
    'Replace with a real guide once written — this placeholder demonstrates the Insights layout.', '#');
  seedInsights.run('newsletters', 'tone-navy', 'Update',
    'Sample entry: Company update template',
    'Use this slot for periodic company updates, new contracts, or equipment arrivals.', '#');
}

const pressCount = db.prepare('SELECT COUNT(*) AS n FROM press').get().n;
if (pressCount === 0) {
  const seedPress = db.prepare(`INSERT INTO press (tone, badge, title, excerpt, link) VALUES (?, ?, ?, ?, ?)`);
  seedPress.run('tone-gold', 'Press Release', 'Sample entry: Press release template',
    'Replace with a real press release or media mention once available.', '#');
}

const mediaCount = db.prepare('SELECT COUNT(*) AS n FROM media').get().n;
if (mediaCount === 0) {
  const seedMedia = db.prepare(`INSERT INTO media (media_type, category, title, caption, url, published) VALUES (?, ?, ?, ?, ?, 1)`);
  seedMedia.run('image', 'gallery', 'Sample entry: Site photo', 'Replace with real site or project photos from Admin \u25b8 Media.', '');
  seedMedia.run('image', 'gallery', 'Sample entry: Equipment photo', 'Replace with real equipment or supply photos.', '');
}

const eventCount = db.prepare('SELECT COUNT(*) AS n FROM events').get().n;
if (eventCount === 0) {
  const seedEvents = db.prepare(`INSERT INTO events (title, description, event_date) VALUES (?, ?, ?)`);
  seedEvents.run('Sample entry: Site visit / tender opening',
    'Replace with a real upcoming site visit, tender opening, or project milestone date.', '2026-09-12');
}

module.exports = db;
