import Database from "better-sqlite3";
import { mkdirSync } from "node:fs";
import path from "node:path";

type RuntimeEnv = {
  APP_USERNAME?: string;
  APP_PASSWORD?: string;
  DEFAULT_OWNER_ID?: string;
  DATABASE_PATH?: string;
  INSTAGRAM_APP_ID?: string;
  INSTAGRAM_APP_SECRET?: string;
  THREADS_APP_ID?: string;
  THREADS_APP_SECRET?: string;
  TOKEN_ENCRYPTION_KEY?: string;
  PUBLIC_APP_URL?: string;
  SCHEDULER_SECRET?: string;
};

export function runtimeEnv(): RuntimeEnv {
  return process.env as unknown as RuntimeEnv;
}

type QueryResult<T> = { results: T[] };
type RunResult = { meta: { changes: number } };

class SqliteStatement {
  constructor(
    private readonly database: Database.Database,
    private readonly sql: string,
    private readonly parameters: unknown[] = [],
  ) {}

  bind(...parameters: unknown[]) {
    return new SqliteStatement(this.database, this.sql, parameters);
  }

  async first<T>(): Promise<T | null> {
    return (this.database.prepare(this.sql).get(...this.parameters) as T | undefined) ?? null;
  }

  async all<T>(): Promise<QueryResult<T>> {
    return { results: this.database.prepare(this.sql).all(...this.parameters) as T[] };
  }

  async run(): Promise<RunResult> {
    return this.runSync();
  }

  runSync(): RunResult {
    const result = this.database.prepare(this.sql).run(...this.parameters);
    return { meta: { changes: result.changes } };
  }
}

class SqliteDatabase {
  constructor(private readonly database: Database.Database) {}

  prepare(sql: string) {
    return new SqliteStatement(this.database, sql);
  }

  async batch(statements: SqliteStatement[]) {
    return this.database.transaction(() => statements.map((statement) => statement.runSync()))();
  }
}

type DatabaseGlobal = typeof globalThis & { __kavenixDatabase?: SqliteDatabase };

function openDatabase() {
  const configuredPath = runtimeEnv().DATABASE_PATH;
  const filename = configuredPath
    ? path.resolve(/* turbopackIgnore: true */ configuredPath)
    : path.join(process.cwd(), "data", "kavenix.sqlite");
  mkdirSync(path.dirname(filename), { recursive: true });
  const sqlite = new Database(filename);
  sqlite.pragma("journal_mode = WAL");
  sqlite.pragma("foreign_keys = ON");
  sqlite.pragma("busy_timeout = 5000");
  return new SqliteDatabase(sqlite);
}

export function rawDb(): SqliteDatabase {
  const shared = globalThis as DatabaseGlobal;
  shared.__kavenixDatabase ??= openDatabase();
  return shared.__kavenixDatabase;
}

let schemaReady: Promise<unknown> | null = null;

export async function ensureSchema() {
  if (schemaReady) return schemaReady;
  const db = rawDb();
  schemaReady = db.batch([
    db.prepare(`CREATE TABLE IF NOT EXISTS brands (
      id TEXT PRIMARY KEY, owner_id TEXT NOT NULL, name TEXT NOT NULL,
      description TEXT NOT NULL DEFAULT '', color TEXT NOT NULL DEFAULT '#7657e8',
      created_at INTEGER NOT NULL, updated_at INTEGER NOT NULL
    )`),
    db.prepare("CREATE INDEX IF NOT EXISTS idx_brands_owner ON brands(owner_id)"),
    db.prepare(`CREATE TABLE IF NOT EXISTS social_accounts (
      id TEXT PRIMARY KEY, owner_id TEXT NOT NULL,
      brand_id TEXT NOT NULL REFERENCES brands(id) ON DELETE CASCADE,
      platform TEXT NOT NULL CHECK(platform IN ('instagram','threads')),
      platform_user_id TEXT NOT NULL, username TEXT NOT NULL, profile_image_url TEXT,
      token_encrypted TEXT NOT NULL, token_iv TEXT NOT NULL, token_expires_at INTEGER,
      status TEXT NOT NULL DEFAULT 'connected', created_at INTEGER NOT NULL, updated_at INTEGER NOT NULL
    )`),
    db.prepare("CREATE INDEX IF NOT EXISTS idx_social_accounts_brand ON social_accounts(owner_id, brand_id)"),
    db.prepare("CREATE UNIQUE INDEX IF NOT EXISTS idx_social_accounts_brand_platform_user ON social_accounts(brand_id, platform, platform_user_id)"),
    db.prepare(`CREATE TABLE IF NOT EXISTS scheduled_posts (
      id TEXT PRIMARY KEY, owner_id TEXT NOT NULL,
      brand_id TEXT NOT NULL REFERENCES brands(id) ON DELETE CASCADE,
      content TEXT NOT NULL, media_url TEXT, media_type TEXT, alt_text TEXT,
      scheduled_for INTEGER NOT NULL, timezone TEXT NOT NULL DEFAULT 'Asia/Seoul',
      status TEXT NOT NULL DEFAULT 'scheduled', published_at INTEGER, error_message TEXT,
      created_at INTEGER NOT NULL, updated_at INTEGER NOT NULL
    )`),
    db.prepare("CREATE INDEX IF NOT EXISTS idx_scheduled_posts_owner_brand ON scheduled_posts(owner_id, brand_id)"),
    db.prepare("CREATE INDEX IF NOT EXISTS idx_scheduled_posts_due ON scheduled_posts(status, scheduled_for)"),
    db.prepare(`CREATE TABLE IF NOT EXISTS scheduled_post_targets (
      id TEXT PRIMARY KEY, post_id TEXT NOT NULL REFERENCES scheduled_posts(id) ON DELETE CASCADE,
      account_id TEXT REFERENCES social_accounts(id) ON DELETE SET NULL,
      platform TEXT NOT NULL CHECK(platform IN ('instagram','threads')),
      status TEXT NOT NULL DEFAULT 'scheduled', external_post_id TEXT, error_message TEXT,
      published_at INTEGER, created_at INTEGER NOT NULL, updated_at INTEGER NOT NULL
    )`),
    db.prepare("CREATE INDEX IF NOT EXISTS idx_post_targets_post ON scheduled_post_targets(post_id)"),
    db.prepare(`CREATE TABLE IF NOT EXISTS oauth_states (
      id TEXT PRIMARY KEY, owner_id TEXT NOT NULL,
      brand_id TEXT NOT NULL REFERENCES brands(id) ON DELETE CASCADE,
      platform TEXT NOT NULL CHECK(platform IN ('instagram','threads')),
      expires_at INTEGER NOT NULL, created_at INTEGER NOT NULL
    )`),
    db.prepare("CREATE INDEX IF NOT EXISTS idx_oauth_states_expiry ON oauth_states(expires_at)"),
  ]).catch((error) => {
    schemaReady = null;
    throw error;
  });
  return schemaReady;
}