import { execFileSync } from 'node:child_process';
import { existsSync, mkdirSync, readFileSync, statSync, writeFileSync } from 'node:fs';
import path from 'node:path';

const DATA_DIR = path.join(process.cwd(), 'data');
const DB_PATH = path.join(DATA_DIR, 'app.db');
const FALLBACK_PATH = path.join(DATA_DIR, 'audit-fallback.jsonl');

type SqlValue = string | number | boolean | null | undefined;

export type AuditEventInput = {
  action: string;
  sessionId?: string | null;
  targetType?: string | null;
  targetId?: string | null;
  status?: 'ok' | 'started' | 'queued' | 'running' | 'error' | 'blocked';
  provider?: string | null;
  message?: string | null;
  metadata?: unknown;
};

export type AuditEventRow = {
  id: number;
  ts: number;
  action: string;
  session_id: string | null;
  target_type: string | null;
  target_id: string | null;
  status: string;
  provider: string | null;
  message: string | null;
  metadata: string | null;
};

export type ImageAssetInput = {
  filename: string;
  url: string;
  bucket?: string;
  sessionId?: string | null;
  packId?: string | null;
  targetId?: string | null;
  kind?: string | null;
  templateId?: string | null;
  title?: string | null;
  aspectRatio?: string | null;
  width?: number | null;
  height?: number | null;
  sizeBytes?: number | null;
  current?: boolean;
  origin?: 'current' | 'archived' | 'generated' | 'selected' | 'upload' | 'anchor' | 'ref' | 'pack';
  status?: string | null;
};

export type ImageAssetRow = {
  filename: string;
  url: string;
  bucket: string;
  session_id: string | null;
  pack_id: string | null;
  target_id: string | null;
  kind: string | null;
  template_id: string | null;
  title: string | null;
  aspect_ratio: string | null;
  width: number | null;
  height: number | null;
  size_bytes: number | null;
  current: number;
  origin: string | null;
  status: string | null;
  created_at: number;
  updated_at: number;
};

function ensureDataDir() {
  mkdirSync(DATA_DIR, { recursive: true });
}

function q(value: SqlValue): string {
  if (value === null || value === undefined) return 'NULL';
  if (typeof value === 'number') return Number.isFinite(value) ? String(value) : 'NULL';
  if (typeof value === 'boolean') return value ? '1' : '0';
  return `'${value.replace(/'/g, "''")}'`;
}

function executeSql(sql: string, readonly = false): string {
  ensureDataDir();
  try {
    const args = readonly ? ['-readonly', DB_PATH] : [DB_PATH];
    return execFileSync('sqlite3', args, {
      input: `${sql}\n`,
      encoding: 'utf-8',
      stdio: ['pipe', 'pipe', 'pipe'],
    });
  } catch (error) {
    const line = JSON.stringify({
      ts: Date.now(),
      action: 'sqlite_unavailable',
      message: String(error),
    });
    try {
      writeFileSync(FALLBACK_PATH, `${line}\n`, { flag: 'a' });
    } catch {
      // Ignore audit fallback failures; product flow should not fail because logging failed.
    }
    throw error;
  }
}

function columnExists(table: string, column: string): boolean {
  try {
    const output = execFileSync('sqlite3', ['-readonly', '-json', DB_PATH, `PRAGMA table_info(${table});`], {
      encoding: 'utf-8',
      stdio: ['ignore', 'pipe', 'pipe'],
    }).trim();
    const rows = output ? JSON.parse(output) as Array<{ name?: string }> : [];
    return rows.some(row => row.name === column);
  } catch {
    return false;
  }
}

function ensureDb() {
  executeSql(`
    CREATE TABLE IF NOT EXISTS audit_events (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      ts INTEGER NOT NULL,
      action TEXT NOT NULL,
      session_id TEXT,
      target_type TEXT,
      target_id TEXT,
      status TEXT NOT NULL DEFAULT 'ok',
      provider TEXT,
      message TEXT,
      metadata TEXT
    );
    CREATE INDEX IF NOT EXISTS idx_audit_events_session_ts ON audit_events(session_id, ts DESC);
    CREATE INDEX IF NOT EXISTS idx_audit_events_action_ts ON audit_events(action, ts DESC);

    CREATE TABLE IF NOT EXISTS image_assets (
      filename TEXT PRIMARY KEY,
      url TEXT NOT NULL,
      bucket TEXT NOT NULL DEFAULT 'packs',
      session_id TEXT,
      pack_id TEXT,
      target_id TEXT,
      kind TEXT,
      template_id TEXT,
      title TEXT,
      aspect_ratio TEXT,
      width INTEGER,
      height INTEGER,
      size_bytes INTEGER,
      current INTEGER NOT NULL DEFAULT 0,
      origin TEXT,
      status TEXT,
      created_at INTEGER NOT NULL,
      updated_at INTEGER NOT NULL
    );
    CREATE INDEX IF NOT EXISTS idx_image_assets_session ON image_assets(session_id, current, kind);
    CREATE INDEX IF NOT EXISTS idx_image_assets_pack ON image_assets(pack_id);
  `);
  if (!columnExists('image_assets', 'target_id')) {
    executeSql('ALTER TABLE image_assets ADD COLUMN target_id TEXT;');
  }
}

export function recordEvent(input: AuditEventInput) {
  try {
    ensureDb();
    executeSql(`
      INSERT INTO audit_events (ts, action, session_id, target_type, target_id, status, provider, message, metadata)
      VALUES (
        ${Date.now()},
        ${q(input.action)},
        ${q(input.sessionId)},
        ${q(input.targetType)},
        ${q(input.targetId)},
        ${q(input.status ?? 'ok')},
        ${q(input.provider)},
        ${q(input.message)},
        ${q(input.metadata === undefined ? null : JSON.stringify(input.metadata))}
      );
    `);
  } catch {
    try {
      ensureDataDir();
      writeFileSync(FALLBACK_PATH, `${JSON.stringify({ ts: Date.now(), ...input })}\n`, { flag: 'a' });
    } catch {
      // Logging must never break product actions.
    }
  }
}

export function upsertImageAsset(input: ImageAssetInput) {
  try {
    ensureDb();
    const now = Date.now();
    executeSql(`
      INSERT INTO image_assets (
      filename, url, bucket, session_id, pack_id, kind, template_id, title, aspect_ratio,
        target_id, width, height, size_bytes, current, origin, status, created_at, updated_at
      )
      VALUES (
        ${q(input.filename)}, ${q(input.url)}, ${q(input.bucket ?? 'packs')}, ${q(input.sessionId)},
        ${q(input.packId)}, ${q(input.kind)}, ${q(input.templateId)}, ${q(input.title)},
        ${q(input.aspectRatio)}, ${q(input.targetId)}, ${q(input.width)}, ${q(input.height)}, ${q(input.sizeBytes)},
        ${input.current ? 1 : 0}, ${q(input.origin)}, ${q(input.status)}, ${now}, ${now}
      )
      ON CONFLICT(filename) DO UPDATE SET
        url = excluded.url,
        bucket = excluded.bucket,
        session_id = COALESCE(excluded.session_id, image_assets.session_id),
        pack_id = COALESCE(excluded.pack_id, image_assets.pack_id),
        target_id = COALESCE(excluded.target_id, image_assets.target_id),
        kind = COALESCE(excluded.kind, image_assets.kind),
        template_id = COALESCE(excluded.template_id, image_assets.template_id),
        title = COALESCE(excluded.title, image_assets.title),
        aspect_ratio = COALESCE(excluded.aspect_ratio, image_assets.aspect_ratio),
        width = COALESCE(excluded.width, image_assets.width),
        height = COALESCE(excluded.height, image_assets.height),
        size_bytes = COALESCE(excluded.size_bytes, image_assets.size_bytes),
        current = excluded.current,
        origin = COALESCE(excluded.origin, image_assets.origin),
        status = COALESCE(excluded.status, image_assets.status),
        updated_at = excluded.updated_at;
    `);
  } catch {
    // Keep gallery and generation usable even if sqlite is temporarily unavailable.
  }
}

export function readJsonRows<T>(sql: string): T[] {
  try {
    ensureDb();
    const output = execFileSync('sqlite3', ['-readonly', '-json', DB_PATH, sql], {
      encoding: 'utf-8',
      stdio: ['ignore', 'pipe', 'pipe'],
    }).trim();
    return output ? JSON.parse(output) as T[] : [];
  } catch {
    return [];
  }
}

export function listAuditEvents(sessionId?: string, limit = 200): AuditEventRow[] {
  const where = sessionId ? `WHERE session_id = ${q(sessionId)}` : '';
  return readJsonRows<AuditEventRow>(`
    SELECT id, ts, action, session_id, target_type, target_id, status, provider, message, metadata
    FROM audit_events
    ${where}
    ORDER BY ts DESC
    LIMIT ${Math.max(1, Math.min(limit, 1000))}
  `);
}

export function listImageAssets(sessionId?: string, limit = 500): ImageAssetRow[] {
  const where = sessionId ? `WHERE session_id = ${q(sessionId)}` : '';
  return readJsonRows<ImageAssetRow>(`
    SELECT filename, url, bucket, session_id, pack_id, target_id, kind, template_id, title, aspect_ratio,
      width, height, size_bytes, current, origin, status, created_at, updated_at
    FROM image_assets
    ${where}
    ORDER BY current DESC, updated_at DESC
    LIMIT ${Math.max(1, Math.min(limit, 2000))}
  `);
}

export function detectImageDimensions(buf: Buffer, filename: string): { width: number | null; height: number | null } {
  if (buf.length >= 24 && buf.toString('ascii', 1, 4) === 'PNG') {
    return { width: buf.readUInt32BE(16), height: buf.readUInt32BE(20) };
  }

  if (buf.length >= 10 && buf.toString('ascii', 0, 4) === 'RIFF' && buf.toString('ascii', 8, 12) === 'WEBP') {
    const chunk = buf.toString('ascii', 12, 16);
    if (chunk === 'VP8X' && buf.length >= 30) {
      const width = 1 + buf.readUIntLE(24, 3);
      const height = 1 + buf.readUIntLE(27, 3);
      return { width, height };
    }
  }

  if (/\.(jpe?g)$/i.test(filename) && buf[0] === 0xff && buf[1] === 0xd8) {
    let offset = 2;
    while (offset < buf.length) {
      if (buf[offset] !== 0xff) break;
      const marker = buf[offset + 1];
      const length = buf.readUInt16BE(offset + 2);
      if (marker >= 0xc0 && marker <= 0xc3 && offset + 8 < buf.length) {
        return { height: buf.readUInt16BE(offset + 5), width: buf.readUInt16BE(offset + 7) };
      }
      offset += 2 + length;
    }
  }

  return { width: null, height: null };
}

export function imageFileInfo(filePath: string): { width: number | null; height: number | null; sizeBytes: number | null } {
  try {
    if (!existsSync(filePath)) return { width: null, height: null, sizeBytes: null };
    const stat = statSync(filePath);
    const buf = readFileSync(filePath);
    return { ...detectImageDimensions(buf, filePath), sizeBytes: stat.size };
  } catch {
    return { width: null, height: null, sizeBytes: null };
  }
}
