import { drizzle } from "drizzle-orm/better-sqlite3";
import Database from "better-sqlite3";
import * as schema from "@shared/schema";

const sqlite = new Database("sqlite.db");

// Create/migrate tables on startup
sqlite.exec(`
  CREATE TABLE IF NOT EXISTS settlements (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    week_start_date TEXT NOT NULL,
    week_end_date TEXT NOT NULL,
    gross_income TEXT NOT NULL,
    paypal_fees TEXT NOT NULL DEFAULT '0',
    fee_percentage TEXT DEFAULT '0',
    total_expenses TEXT NOT NULL DEFAULT '0',
    direct_payments_total TEXT NOT NULL DEFAULT '0',
    net_income TEXT NOT NULL,
    party_a_share TEXT NOT NULL,
    party_b_share TEXT NOT NULL,
    party_c_share TEXT NOT NULL,
    party_d_share TEXT DEFAULT '0',
    party_a_payout TEXT NOT NULL,
    party_b_payout TEXT NOT NULL,
    party_c_payout TEXT NOT NULL,
    party_d_payout TEXT DEFAULT '0',
    notes TEXT,
    created_at INTEGER DEFAULT (strftime('%s', 'now'))
  );

  CREATE TABLE IF NOT EXISTS expenses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    settlement_id INTEGER NOT NULL REFERENCES settlements(id) ON DELETE CASCADE,
    description TEXT NOT NULL,
    amount TEXT NOT NULL,
    payee_email TEXT,
    notes TEXT,
    created_at INTEGER DEFAULT (strftime('%s', 'now'))
  );

  CREATE TABLE IF NOT EXISTS direct_payments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    settlement_id INTEGER NOT NULL REFERENCES settlements(id) ON DELETE CASCADE,
    amount TEXT NOT NULL,
    currency TEXT NOT NULL DEFAULT 'USD',
    payment_method TEXT NOT NULL,
    received_by TEXT NOT NULL,
    reference TEXT,
    notes TEXT,
    created_at INTEGER DEFAULT (strftime('%s', 'now'))
  );

  CREATE TABLE IF NOT EXISTS app_settings (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL,
    updated_at INTEGER DEFAULT (strftime('%s', 'now'))
  );

  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    role TEXT NOT NULL DEFAULT 'manager',
    created_at INTEGER DEFAULT (strftime('%s', 'now'))
  );
`);

// Add new columns to existing tables if they don't already exist (safe migration)
const addColumnIfMissing = (table: string, column: string, definition: string) => {
  const cols = sqlite.pragma(`table_info(${table})`);
  const exists = (cols as any[]).some((c: any) => c.name === column);
  if (!exists) {
    sqlite.exec(`ALTER TABLE ${table} ADD COLUMN ${column} ${definition}`);
  }
};

addColumnIfMissing('settlements', 'party_d_share', "TEXT DEFAULT '0'");
addColumnIfMissing('settlements', 'party_d_payout', "TEXT DEFAULT '0'");

export const db = drizzle(sqlite, { schema });