/**
 * Database connection and queries using Turso/LibSQL
 *
 * Works in Deno/edge environments via HTTP
 */

import { createClient, type Client } from "@libsql/client/web"

// Secrets from at-run
declare const Deno: {
  env: { get(key: string): string | undefined }
}

let client: Client | null = null

function getDb(): Client {
  if (!client) {
    const url = Deno.env.get("TURSO_URL")
    const authToken = Deno.env.get("TURSO_AUTH_TOKEN")

    if (!url) {
      throw new Error("TURSO_URL secret not configured")
    }

    client = createClient({
      url,
      authToken,
    })
  }
  return client
}

/**
 * Initialize database schema (run once on setup)
 */
export async function initSchema(): Promise<void> {
  const db = getDb()

  await db.batch([
    `CREATE TABLE IF NOT EXISTS creators (
      did TEXT PRIMARY KEY,
      added_at TEXT DEFAULT (datetime('now')),
      last_synced_at TEXT
    )`,
    `CREATE TABLE IF NOT EXISTS videos (
      uri TEXT PRIMARY KEY,
      creator_did TEXT NOT NULL,
      title TEXT NOT NULL,
      duration INTEGER NOT NULL,
      created_at TEXT NOT NULL,
      indexed_at TEXT DEFAULT (datetime('now'))
    )`,
    `CREATE INDEX IF NOT EXISTS idx_videos_creator ON videos(creator_did)`,
    `CREATE INDEX IF NOT EXISTS idx_videos_created_at ON videos(created_at DESC)`,
  ])
}

// ============================================================================
// Creator queries
// ============================================================================

export interface Creator {
  did: string
  addedAt: Date
  lastSyncedAt: Date | null
}

export async function getCreator(did: string): Promise<Creator | null> {
  const db = getDb()
  const result = await db.execute({
    sql: "SELECT did, added_at, last_synced_at FROM creators WHERE did = ?",
    args: [did],
  })

  if (result.rows.length === 0) return null
  const row = result.rows[0]
  return {
    did: row.did as string,
    addedAt: new Date(row.added_at as string),
    lastSyncedAt: row.last_synced_at ? new Date(row.last_synced_at as string) : null,
  }
}

export async function listCreators(): Promise<Creator[]> {
  const db = getDb()
  const result = await db.execute(
    "SELECT did, added_at, last_synced_at FROM creators ORDER BY added_at DESC"
  )

  return result.rows.map((row) => ({
    did: row.did as string,
    addedAt: new Date(row.added_at as string),
    lastSyncedAt: row.last_synced_at ? new Date(row.last_synced_at as string) : null,
  }))
}

export async function addCreator(did: string): Promise<Creator> {
  const db = getDb()
  await db.execute({
    sql: `INSERT INTO creators (did) VALUES (?)
          ON CONFLICT (did) DO UPDATE SET did = excluded.did`,
    args: [did],
  })

  const creator = await getCreator(did)
  if (!creator) throw new Error("Failed to add creator")
  return creator
}

export async function updateCreatorSyncTime(did: string): Promise<void> {
  const db = getDb()
  await db.execute({
    sql: "UPDATE creators SET last_synced_at = datetime('now') WHERE did = ?",
    args: [did],
  })
}

export async function removeCreator(did: string): Promise<void> {
  const db = getDb()
  await db.batch([
    { sql: "DELETE FROM videos WHERE creator_did = ?", args: [did] },
    { sql: "DELETE FROM creators WHERE did = ?", args: [did] },
  ])
}

// ============================================================================
// Video queries
// ============================================================================

export interface Video {
  uri: string
  creatorDid: string
  title: string
  duration: number
  createdAt: Date
  indexedAt: Date
}

export interface VideoInput {
  uri: string
  creatorDid: string
  title: string
  duration: number
  createdAt: Date
}

export async function upsertVideo(video: VideoInput): Promise<void> {
  const db = getDb()
  await db.execute({
    sql: `INSERT INTO videos (uri, creator_did, title, duration, created_at)
          VALUES (?, ?, ?, ?, ?)
          ON CONFLICT (uri) DO UPDATE SET
            title = excluded.title,
            duration = excluded.duration,
            indexed_at = datetime('now')`,
    args: [video.uri, video.creatorDid, video.title, video.duration, video.createdAt.toISOString()],
  })
}

export async function upsertVideos(videos: VideoInput[]): Promise<void> {
  if (videos.length === 0) return
  const db = getDb()

  // Batch insert for efficiency
  const statements = videos.map((v) => ({
    sql: `INSERT INTO videos (uri, creator_did, title, duration, created_at)
          VALUES (?, ?, ?, ?, ?)
          ON CONFLICT (uri) DO UPDATE SET
            title = excluded.title,
            duration = excluded.duration,
            indexed_at = datetime('now')`,
    args: [v.uri, v.creatorDid, v.title, v.duration, v.createdAt.toISOString()],
  }))

  // LibSQL batch has a limit, chunk if needed
  const BATCH_SIZE = 100
  for (let i = 0; i < statements.length; i += BATCH_SIZE) {
    await db.batch(statements.slice(i, i + BATCH_SIZE))
  }
}

export async function deleteVideosForCreator(creatorDid: string): Promise<void> {
  const db = getDb()
  await db.execute({
    sql: "DELETE FROM videos WHERE creator_did = ?",
    args: [creatorDid],
  })
}

export async function listVideos(options?: {
  limit?: number
  cursor?: string
  creatorDid?: string
}): Promise<{ videos: Video[]; cursor?: string }> {
  const db = getDb()
  const limit = options?.limit ?? 50

  let sql: string
  let args: (string | number)[]

  if (options?.creatorDid && options?.cursor) {
    sql = `SELECT uri, creator_did, title, duration, created_at, indexed_at
           FROM videos
           WHERE creator_did = ? AND created_at < ?
           ORDER BY created_at DESC
           LIMIT ?`
    args = [options.creatorDid, options.cursor, limit + 1]
  } else if (options?.creatorDid) {
    sql = `SELECT uri, creator_did, title, duration, created_at, indexed_at
           FROM videos
           WHERE creator_did = ?
           ORDER BY created_at DESC
           LIMIT ?`
    args = [options.creatorDid, limit + 1]
  } else if (options?.cursor) {
    sql = `SELECT uri, creator_did, title, duration, created_at, indexed_at
           FROM videos
           WHERE created_at < ?
           ORDER BY created_at DESC
           LIMIT ?`
    args = [options.cursor, limit + 1]
  } else {
    sql = `SELECT uri, creator_did, title, duration, created_at, indexed_at
           FROM videos
           ORDER BY created_at DESC
           LIMIT ?`
    args = [limit + 1]
  }

  const result = await db.execute({ sql, args })

  const hasMore = result.rows.length > limit
  const rows = result.rows.slice(0, limit)

  const videos = rows.map((row) => ({
    uri: row.uri as string,
    creatorDid: row.creator_did as string,
    title: row.title as string,
    duration: Number(row.duration),
    createdAt: new Date(row.created_at as string),
    indexedAt: new Date(row.indexed_at as string),
  }))

  return {
    videos,
    cursor: hasMore ? videos[videos.length - 1]?.createdAt.toISOString() : undefined,
  }
}

export async function getVideoCount(): Promise<number> {
  const db = getDb()
  const result = await db.execute("SELECT COUNT(*) as count FROM videos")
  return Number(result.rows[0].count)
}

export async function getCreatorCount(): Promise<number> {
  const db = getDb()
  const result = await db.execute("SELECT COUNT(*) as count FROM creators")
  return Number(result.rows[0].count)
}