import initSqlJs, { Database } from 'sql.js';
import fs from 'fs';
import path from 'path';

export interface RawLeadRecord {
  id: number;
  event_id: string;
  created_at: string;
  first_name: string;
  last_name: string;
  email: string;
  phone: string;
  goal: string;
  blocker: string;
  current_level: string;
  fbclid: string;
  fbc: string;
  fbp: string;
  utm_source: string;
  utm_medium: string;
  utm_campaign: string;
  utm_content: string;
  utm_term: string;
  ip_address: string;
  user_agent: string;
  page_url: string;
  emq_score: number;
  raw_json: string;
}

const DB_PATH = path.resolve(process.cwd(), 'leads.sqlite');

let dbInstance: Database | null = null;
let sqlJsModule: any = null;

export async function getDb(): Promise<Database> {
  if (dbInstance) return dbInstance;

  if (!sqlJsModule) {
    sqlJsModule = await initSqlJs();
  }

  if (fs.existsSync(DB_PATH)) {
    try {
      const fileBuffer = fs.readFileSync(DB_PATH);
      dbInstance = new sqlJsModule.Database(fileBuffer);
    } catch (e) {
      console.warn('Could not read existing SQLite file, creating new one:', e);
      dbInstance = new sqlJsModule.Database();
    }
  } else {
    dbInstance = new sqlJsModule.Database();
  }

  // Create table if not exists
  dbInstance!.run(`
    CREATE TABLE IF NOT EXISTS leads (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      event_id TEXT,
      created_at TEXT,
      first_name TEXT,
      last_name TEXT,
      email TEXT,
      phone TEXT,
      goal TEXT,
      blocker TEXT,
      current_level TEXT,
      fbclid TEXT,
      fbc TEXT,
      fbp TEXT,
      utm_source TEXT,
      utm_medium TEXT,
      utm_campaign TEXT,
      utm_content TEXT,
      utm_term TEXT,
      ip_address TEXT,
      user_agent TEXT,
      page_url TEXT,
      emq_score REAL,
      raw_json TEXT
    );
  `);

  saveDbToDisk();
  return dbInstance!;
}

function saveDbToDisk() {
  if (!dbInstance) return;
  try {
    const data = dbInstance.export();
    fs.writeFileSync(DB_PATH, Buffer.from(data));
  } catch (err) {
    console.error('Failed to save SQLite file to disk:', err);
  }
}

export async function insertRawLead(lead: Omit<RawLeadRecord, 'id'>): Promise<number> {
  const db = await getDb();
  
  db.run(
    `INSERT INTO leads (
      event_id, created_at, first_name, last_name, email, phone,
      goal, blocker, current_level,
      fbclid, fbc, fbp, utm_source, utm_medium, utm_campaign, utm_content, utm_term,
      ip_address, user_agent, page_url, emq_score, raw_json
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    [
      lead.event_id || '',
      lead.created_at || new Date().toISOString(),
      lead.first_name || '',
      lead.last_name || '',
      lead.email || '',
      lead.phone || '',
      lead.goal || '',
      lead.blocker || '',
      lead.current_level || '',
      lead.fbclid || '',
      lead.fbc || '',
      lead.fbp || '',
      lead.utm_source || '',
      lead.utm_medium || '',
      lead.utm_campaign || '',
      lead.utm_content || '',
      lead.utm_term || '',
      lead.ip_address || '',
      lead.user_agent || '',
      lead.page_url || '',
      lead.emq_score || 0,
      lead.raw_json || '{}',
    ]
  );

  saveDbToDisk();

  // Get inserted ID
  const res = db.exec('SELECT last_insert_rowid() as id');
  if (res.length > 0 && res[0].values.length > 0) {
    return Number(res[0].values[0][0]);
  }
  return 1;
}

export async function getAllRawLeads(): Promise<RawLeadRecord[]> {
  const db = await getDb();
  const res = db.exec('SELECT * FROM leads ORDER BY id DESC');
  if (res.length === 0) return [];

  const columns = res[0].columns;
  const values = res[0].values;

  return values.map((row) => {
    const obj: any = {};
    columns.forEach((col, idx) => {
      obj[col] = row[idx];
    });
    return obj as RawLeadRecord;
  });
}

export async function clearAllLeads(): Promise<void> {
  const db = await getDb();
  db.run('DELETE FROM leads;');
  saveDbToDisk();
}

export function getSqliteBinaryPath(): string {
  return DB_PATH;
}