// Native storage implementation backed by expo-sqlite (SDK 56 async API).
//
// Mirrors the web/Dexie repo's contract exactly. The full transcript body is
// stored as JSON in the 'json' column; metadata lives in dedicated columns so
// list()/search() can be answered from columns alone without parsing every
// body. Search uses a LIKE over the lowercased 'searchText' column.
//
// Schema evolution is handled by a real PRAGMA user_version migration runner
// (see MIGRATIONS below), so devices created by the old single-table code
// migrate forward in place rather than being wiped.
//
// API reference (read the EXACT versioned docs before touching this file):
//   https://docs.expo.dev/versions/v56.0.0/sdk/sqlite/
//   https://docs.expo.dev/versions/v56.0.0/sdk/filesystem/
// We use openDatabaseAsync + the async methods (execAsync/runAsync/getAllAsync)
// and the new object-oriented expo-file-system API (File/Directory/Paths).

import * as SQLite from 'expo-sqlite';
import { Directory, File, Paths } from 'expo-file-system';
import type { Segment } from '../types';
import { newId } from '../ids';
import {
  parseDraft,
  parseCourseDraft,
  zSegment,
  type TranscriptDraft,
  type TranscriptMeta,
  type Transcript,
  type TranscriptPatch,
  type Course,
  type CourseDraft,
} from './schema';
import type { MediaInput, StorageRepo } from './repo';

// ---------------------------------------------------------------------------
// Row shapes (as returned by getAllAsync). SQLite has no boolean/undefined:
// optional text columns come back as string | null, booleans as 0/1 integers.
// ---------------------------------------------------------------------------

interface MetaRow {
  id: string;
  title: string;
  createdAt: number;
  updatedAt: number;
  durationSec: number;
  segmentCount: number;
  modelId: string;
  language: string | null;
  // v2 columns
  courseId: string | null;
  lectureDate: number | null;
  instructor: string | null;
  location: string | null;
  /** JSON-encoded string[] or null. */
  tags: string | null;
  lectureNumber: number | null;
  /** 0/1 integer. */
  hasMedia: number | null;
}

// A meta row plus the JSON body, for get()/update().
interface FullRow extends MetaRow {
  json: string;
}

interface CourseRow {
  id: string;
  name: string;
  code: string | null;
  instructor: string | null;
  term: string | null;
  color: string | null;
  createdAt: number;
  updatedAt: number;
}

interface MediaRow {
  transcriptId: string;
  path: string;
  mime: string;
}

// ---------------------------------------------------------------------------
// Pure derivation helpers
// ---------------------------------------------------------------------------

/** Lowercased haystack of title + every segment's text, for LIKE search. */
function deriveSearchText(title: string, segments: Segment[]): string {
  return [title, ...segments.map((s) => s.text)].join('\n').toLowerCase();
}

/**
 * Ensure every segment has a stable id, assigning newId('s_') to any that
 * lack one. Returns a new array (does not mutate the input segments).
 */
function withSegmentIds(segments: Segment[]): Segment[] {
  return segments.map((s) => (s.id ? s : { ...s, id: newId('s_') }));
}

/** Map a metadata row to the public TranscriptMeta. */
function rowToMeta(r: MetaRow): TranscriptMeta {
  const meta: TranscriptMeta = {
    id: r.id,
    title: r.title,
    createdAt: r.createdAt,
    updatedAt: r.updatedAt,
    durationSec: r.durationSec,
    modelId: r.modelId as TranscriptMeta['modelId'],
    segmentCount: r.segmentCount,
    // Keep courseId as null (the "Unsorted" sentinel) rather than dropping it.
    courseId: r.courseId,
    hasMedia: r.hasMedia === 1,
  };
  if (r.language !== null) meta.language = r.language;
  if (r.lectureDate !== null) meta.lectureDate = r.lectureDate;
  if (r.instructor !== null) meta.instructor = r.instructor;
  if (r.location !== null) meta.location = r.location;
  if (r.lectureNumber !== null) meta.lectureNumber = r.lectureNumber;
  if (r.tags !== null) {
    try {
      const parsed = JSON.parse(r.tags) as unknown;
      if (Array.isArray(parsed)) meta.tags = parsed as string[];
    } catch {
      // Corrupt tags JSON => treat as absent rather than throwing on read.
    }
  }
  return meta;
}

function rowToCourse(r: CourseRow): Course {
  const course: Course = {
    id: r.id,
    name: r.name,
    createdAt: r.createdAt,
    updatedAt: r.updatedAt,
  };
  if (r.code !== null) course.code = r.code;
  if (r.instructor !== null) course.instructor = r.instructor;
  if (r.term !== null) course.term = r.term;
  if (r.color !== null) course.color = r.color;
  return course;
}

// ---------------------------------------------------------------------------
// Migration runner
// ---------------------------------------------------------------------------
//
// Each entry migrates the DB from version (index) to (index + 1). On first
// getDb() we read PRAGMA user_version and apply pending steps in order, each
// wrapped in a transaction, then bump user_version. Steps are written to be
// idempotent (IF NOT EXISTS / pragma_table_info guards) so they also no-op on
// DBs the original single-table code already created at version 0.

const TARGET_VERSION = 2;

type Migration = (db: SQLite.SQLiteDatabase) => Promise<void>;

const MIGRATIONS: Migration[] = [
  // ---- v0 -> v1: the original transcripts table -------------------------
  async (db) => {
    await db.execAsync(`
      CREATE TABLE IF NOT EXISTS transcripts (
        id TEXT PRIMARY KEY NOT NULL,
        json TEXT NOT NULL,
        searchText TEXT NOT NULL,
        createdAt INTEGER NOT NULL,
        updatedAt INTEGER NOT NULL,
        durationSec REAL NOT NULL,
        segmentCount INTEGER NOT NULL,
        modelId TEXT NOT NULL,
        language TEXT
      );
      CREATE INDEX IF NOT EXISTS idx_transcripts_createdAt
        ON transcripts (createdAt);
    `);
  },

  // ---- v1 -> v2: course/lecture metadata, courses + media tables --------
  async (db) => {
    // Add the new transcript metadata columns. We guard each ADD COLUMN with a
    // pragma_table_info check so this step is safe even if a column already
    // exists (e.g. a DB that the old code happened to create with extras).
    const existing = await db.getAllAsync<{ name: string }>(
      `SELECT name FROM pragma_table_info('transcripts')`,
    );
    const have = new Set(existing.map((c) => c.name));
    const addColumn = async (name: string, decl: string): Promise<void> => {
      if (!have.has(name)) {
        await db.execAsync(`ALTER TABLE transcripts ADD COLUMN ${decl}`);
      }
    };
    await addColumn('courseId', 'courseId TEXT');
    await addColumn('lectureDate', 'lectureDate INTEGER');
    await addColumn('instructor', 'instructor TEXT');
    await addColumn('location', 'location TEXT');
    await addColumn('tags', 'tags TEXT');
    await addColumn('lectureNumber', 'lectureNumber REAL');
    await addColumn('hasMedia', 'hasMedia INTEGER DEFAULT 0');

    // Courses table + name index for alphabetical listing.
    await db.execAsync(`
      CREATE TABLE IF NOT EXISTS courses (
        id TEXT PRIMARY KEY NOT NULL,
        name TEXT NOT NULL,
        code TEXT,
        instructor TEXT,
        term TEXT,
        color TEXT,
        createdAt INTEGER,
        updatedAt INTEGER
      );
      CREATE INDEX IF NOT EXISTS courses_name ON courses (name);
    `);

    // Media table: one persisted source-audio file per transcript.
    await db.execAsync(`
      CREATE TABLE IF NOT EXISTS media (
        transcriptId TEXT PRIMARY KEY NOT NULL,
        path TEXT NOT NULL,
        mime TEXT NOT NULL
      );
    `);

    // Backfill: assign stable ids to any segments in existing bodies that lack
    // one, so older transcripts gain the durable anchors the new code expects.
    const rows = await db.getAllAsync<{ id: string; json: string }>(
      `SELECT id, json FROM transcripts`,
    );
    for (const row of rows) {
      let transcript: Transcript;
      try {
        transcript = JSON.parse(row.json) as Transcript;
      } catch {
        continue; // Skip unparseable bodies rather than aborting the migration.
      }
      const segments = transcript.segments ?? [];
      const needsIds = segments.some((s) => !s.id);
      if (!needsIds) continue;
      const next: Transcript = {
        ...transcript,
        segments: withSegmentIds(segments),
      };
      await db.runAsync(`UPDATE transcripts SET json = ? WHERE id = ?`, [
        JSON.stringify(next),
        row.id,
      ]);
    }
  },
];

async function runMigrations(db: SQLite.SQLiteDatabase): Promise<void> {
  const result = await db.getFirstAsync<{ user_version: number }>(
    `PRAGMA user_version`,
  );
  let current = result?.user_version ?? 0;
  while (current < TARGET_VERSION) {
    const migrate = MIGRATIONS[current];
    if (!migrate) break; // Defensive: no step for this version.
    await db.withTransactionAsync(async () => {
      await migrate(db);
    });
    current += 1;
    // PRAGMA user_version does not accept bound params, so interpolate the
    // integer directly (it is a controlled loop counter, never user input).
    await db.execAsync(`PRAGMA user_version = ${current}`);
  }
}

// ---------------------------------------------------------------------------
// Lazy, memoized database handle + schema bootstrap
// ---------------------------------------------------------------------------

let dbPromise: Promise<SQLite.SQLiteDatabase> | undefined;

function getDb(): Promise<SQLite.SQLiteDatabase> {
  // Open once and reuse; run migrations on first open. The async open returns a
  // promise we cache so concurrent callers share a single connection.
  if (!dbPromise) {
    dbPromise = (async () => {
      const db = await SQLite.openDatabaseAsync('wisp.db');
      await runMigrations(db);
      return db;
    })();
  }
  return dbPromise;
}

// The metadata columns we select for list()/search(), kept in one place.
const META_COLUMNS =
  'id, title, createdAt, updatedAt, durationSec, segmentCount, modelId, language, ' +
  'courseId, lectureDate, instructor, location, tags, lectureNumber, hasMedia';

// ---------------------------------------------------------------------------
// Media filesystem helpers (expo-file-system, SDK 56 object API)
// ---------------------------------------------------------------------------
//
// TODO(native-untested): the media filesystem paths below cannot be exercised
// off-device (expo-file-system has no Node/jsdom backend), so they are
// type-checked and modeled on the SDK 56 docs but NOT runtime-tested here.

/** The <documentDirectory>/media/ directory, created on demand. */
function mediaDir(): Directory {
  return new Directory(Paths.document, 'media');
}

/** The persisted media file for a transcript (named by its id). */
function mediaFile(transcriptId: string): File {
  return new File(mediaDir(), transcriptId);
}

// ---------------------------------------------------------------------------
// Repo implementation
// ---------------------------------------------------------------------------

export const repo: StorageRepo = {
  // --- transcripts ---------------------------------------------------------
  async list(): Promise<TranscriptMeta[]> {
    const db = await getDb();
    const rows = await db.getAllAsync<MetaRow>(
      `SELECT ${META_COLUMNS} FROM transcripts ORDER BY createdAt DESC`,
    );
    return rows.map(rowToMeta);
  },

  async get(id: string): Promise<Transcript | undefined> {
    const db = await getDb();
    const row = await db.getFirstAsync<FullRow>(
      `SELECT ${META_COLUMNS}, json FROM transcripts WHERE id = ?`,
      [id],
    );
    if (!row) return undefined;
    // The JSON body is the authoritative full Transcript (already validated and
    // segment-id-stamped on write), so we return it directly.
    return JSON.parse(row.json) as Transcript;
  },

  async create(draft: TranscriptDraft): Promise<Transcript> {
    const valid = parseDraft(draft);
    const now = Date.now();
    // Assign a stable id to every segment that lacks one before storing.
    const segments = withSegmentIds(valid.segments);
    const courseId = valid.courseId ?? null;

    const transcript: Transcript = {
      id: newId('t_'),
      title: valid.title,
      createdAt: now,
      updatedAt: now,
      durationSec: valid.durationSec,
      modelId: valid.modelId,
      segmentCount: segments.length,
      segments,
      courseId,
      hasMedia: false,
      ...(valid.language !== undefined ? { language: valid.language } : {}),
      ...(valid.lectureDate !== undefined ? { lectureDate: valid.lectureDate } : {}),
      ...(valid.instructor !== undefined ? { instructor: valid.instructor } : {}),
      ...(valid.location !== undefined ? { location: valid.location } : {}),
      ...(valid.tags !== undefined ? { tags: valid.tags } : {}),
      ...(valid.lectureNumber !== undefined
        ? { lectureNumber: valid.lectureNumber }
        : {}),
    };

    const db = await getDb();
    await db.runAsync(
      `INSERT INTO transcripts
         (id, json, searchText, createdAt, updatedAt, durationSec, segmentCount,
          modelId, language, courseId, lectureDate, instructor, location, tags,
          lectureNumber, hasMedia)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        transcript.id,
        JSON.stringify(transcript),
        deriveSearchText(transcript.title, transcript.segments),
        transcript.createdAt,
        transcript.updatedAt,
        transcript.durationSec,
        transcript.segmentCount,
        transcript.modelId,
        // expo-sqlite accepts null but not undefined for params.
        transcript.language ?? null,
        courseId,
        transcript.lectureDate ?? null,
        transcript.instructor ?? null,
        transcript.location ?? null,
        transcript.tags !== undefined ? JSON.stringify(transcript.tags) : null,
        transcript.lectureNumber ?? null,
        transcript.hasMedia ? 1 : 0,
      ],
    );
    return transcript;
  },

  async update(id: string, patch: TranscriptPatch): Promise<Transcript> {
    const db = await getDb();
    const existingRow = await db.getFirstAsync<FullRow>(
      `SELECT ${META_COLUMNS}, json FROM transcripts WHERE id = ?`,
      [id],
    );
    if (!existingRow) {
      throw new Error(`transcript not found: ${id}`);
    }
    const existing = JSON.parse(existingRow.json) as Transcript;

    // Re-validate segments on update (so non-finite times can never slip in)
    // and stamp ids onto any that are missing one.
    const nextSegments =
      patch.segments !== undefined
        ? withSegmentIds(patch.segments.map((s) => zSegment.parse(s)))
        : existing.segments;
    const nextTitle = patch.title !== undefined ? patch.title : existing.title;

    // Apply only provided patch fields; leave everything else as-is.
    const updated: Transcript = {
      ...existing,
      title: nextTitle,
      segments: nextSegments,
      segmentCount: nextSegments.length,
      updatedAt: Date.now(),
      ...(patch.courseId !== undefined ? { courseId: patch.courseId } : {}),
      ...(patch.lectureDate !== undefined ? { lectureDate: patch.lectureDate } : {}),
      ...(patch.instructor !== undefined ? { instructor: patch.instructor } : {}),
      ...(patch.location !== undefined ? { location: patch.location } : {}),
      ...(patch.tags !== undefined ? { tags: patch.tags } : {}),
      ...(patch.lectureNumber !== undefined
        ? { lectureNumber: patch.lectureNumber }
        : {}),
    };

    await db.runAsync(
      `UPDATE transcripts
         SET json = ?, searchText = ?, updatedAt = ?, segmentCount = ?, title = ?,
             courseId = ?, lectureDate = ?, instructor = ?, location = ?, tags = ?,
             lectureNumber = ?
       WHERE id = ?`,
      [
        JSON.stringify(updated),
        deriveSearchText(nextTitle, nextSegments),
        updated.updatedAt,
        updated.segmentCount,
        updated.title,
        updated.courseId ?? null,
        updated.lectureDate ?? null,
        updated.instructor ?? null,
        updated.location ?? null,
        updated.tags !== undefined ? JSON.stringify(updated.tags) : null,
        updated.lectureNumber ?? null,
        id,
      ],
    );
    return updated;
  },

  async remove(id: string): Promise<void> {
    const db = await getDb();
    // Delete persisted media first so we never orphan a file.
    await this.removeMedia(id);
    await db.runAsync(`DELETE FROM transcripts WHERE id = ?`, [id]);
  },

  async search(query: string): Promise<TranscriptMeta[]> {
    const db = await getDb();
    const needle = query.toLowerCase().trim();
    if (!needle) {
      // Empty query => everything, newest first (matches the web repo).
      const rows = await db.getAllAsync<MetaRow>(
        `SELECT ${META_COLUMNS} FROM transcripts ORDER BY createdAt DESC`,
      );
      return rows.map(rowToMeta);
    }
    // Escape LIKE wildcards (% and _) and the escape char itself so user input
    // is matched literally, then use ESCAPE to declare the escape character.
    const escaped = needle.replace(/[\\%_]/g, (ch) => `\\${ch}`);
    const rows = await db.getAllAsync<MetaRow>(
      `SELECT ${META_COLUMNS} FROM transcripts
         WHERE searchText LIKE ? ESCAPE '\\'
       ORDER BY createdAt DESC`,
      [`%${escaped}%`],
    );
    return rows.map(rowToMeta);
  },

  async listByCourse(courseId: string | null): Promise<TranscriptMeta[]> {
    const db = await getDb();
    if (courseId === null) {
      // "Unsorted": courseId IS NULL covers both null and never-set (the column
      // defaults to NULL for rows written before v2).
      const rows = await db.getAllAsync<MetaRow>(
        `SELECT ${META_COLUMNS} FROM transcripts
           WHERE courseId IS NULL
         ORDER BY createdAt DESC`,
      );
      return rows.map(rowToMeta);
    }
    const rows = await db.getAllAsync<MetaRow>(
      `SELECT ${META_COLUMNS} FROM transcripts
         WHERE courseId = ?
       ORDER BY createdAt DESC`,
      [courseId],
    );
    return rows.map(rowToMeta);
  },

  async reassign(transcriptId: string, courseId: string | null): Promise<void> {
    const db = await getDb();
    const row = await db.getFirstAsync<FullRow>(
      `SELECT ${META_COLUMNS}, json FROM transcripts WHERE id = ?`,
      [transcriptId],
    );
    if (!row) {
      throw new Error(`transcript not found: ${transcriptId}`);
    }
    const transcript = JSON.parse(row.json) as Transcript;
    const updatedAt = Date.now();
    const next: Transcript = { ...transcript, courseId, updatedAt };
    await db.runAsync(
      `UPDATE transcripts SET courseId = ?, updatedAt = ?, json = ? WHERE id = ?`,
      [courseId, updatedAt, JSON.stringify(next), transcriptId],
    );
  },

  // --- courses -------------------------------------------------------------
  async listCourses(): Promise<Course[]> {
    const db = await getDb();
    const rows = await db.getAllAsync<CourseRow>(
      `SELECT id, name, code, instructor, term, color, createdAt, updatedAt
         FROM courses ORDER BY name ASC`,
    );
    return rows.map(rowToCourse);
  },

  async createCourse(draft: CourseDraft): Promise<Course> {
    const valid = parseCourseDraft(draft);
    const now = Date.now();
    const course: Course = {
      id: newId('c_'),
      name: valid.name,
      createdAt: now,
      updatedAt: now,
      ...(valid.code !== undefined ? { code: valid.code } : {}),
      ...(valid.instructor !== undefined ? { instructor: valid.instructor } : {}),
      ...(valid.term !== undefined ? { term: valid.term } : {}),
      ...(valid.color !== undefined ? { color: valid.color } : {}),
    };
    const db = await getDb();
    await db.runAsync(
      `INSERT INTO courses
         (id, name, code, instructor, term, color, createdAt, updatedAt)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        course.id,
        course.name,
        course.code ?? null,
        course.instructor ?? null,
        course.term ?? null,
        course.color ?? null,
        course.createdAt,
        course.updatedAt,
      ],
    );
    return course;
  },

  async updateCourse(id: string, patch: Partial<CourseDraft>): Promise<Course> {
    const db = await getDb();
    const existing = await db.getFirstAsync<CourseRow>(
      `SELECT id, name, code, instructor, term, color, createdAt, updatedAt
         FROM courses WHERE id = ?`,
      [id],
    );
    if (!existing) {
      throw new Error(`course not found: ${id}`);
    }
    const current = rowToCourse(existing);
    // Validate only the patched fields by merging onto the existing draft view.
    const merged = parseCourseDraft({
      name: patch.name ?? current.name,
      ...(patch.code !== undefined ? { code: patch.code } : current.code !== undefined ? { code: current.code } : {}),
      ...(patch.instructor !== undefined
        ? { instructor: patch.instructor }
        : current.instructor !== undefined
          ? { instructor: current.instructor }
          : {}),
      ...(patch.term !== undefined ? { term: patch.term } : current.term !== undefined ? { term: current.term } : {}),
      ...(patch.color !== undefined ? { color: patch.color } : current.color !== undefined ? { color: current.color } : {}),
    });
    const updated: Course = {
      id: current.id,
      name: merged.name,
      createdAt: current.createdAt,
      updatedAt: Date.now(),
      ...(merged.code !== undefined ? { code: merged.code } : {}),
      ...(merged.instructor !== undefined ? { instructor: merged.instructor } : {}),
      ...(merged.term !== undefined ? { term: merged.term } : {}),
      ...(merged.color !== undefined ? { color: merged.color } : {}),
    };
    await db.runAsync(
      `UPDATE courses
         SET name = ?, code = ?, instructor = ?, term = ?, color = ?, updatedAt = ?
       WHERE id = ?`,
      [
        updated.name,
        updated.code ?? null,
        updated.instructor ?? null,
        updated.term ?? null,
        updated.color ?? null,
        updated.updatedAt,
        id,
      ],
    );
    return updated;
  },

  async deleteCourse(id: string): Promise<void> {
    const db = await getDb();
    // First reassign this course's transcripts to Unsorted, THEN delete the
    // course row, so we never leave dangling courseId references.
    await db.withTransactionAsync(async () => {
      await db.runAsync(
        `UPDATE transcripts SET courseId = NULL WHERE courseId = ?`,
        [id],
      );
      await db.runAsync(`DELETE FROM courses WHERE id = ?`, [id]);
    });
  },

  // --- media (persisted source audio) -------------------------------------
  // TODO(native-untested): these filesystem paths are type-checked against the
  // SDK 56 expo-file-system API but cannot be run off-device here.
  async putMedia(transcriptId: string, input: MediaInput): Promise<void> {
    const db = await getDb();

    // Ensure <documentDirectory>/media/ exists.
    const dir = mediaDir();
    if (!dir.exists) {
      dir.create({ intermediates: true });
    }

    // Write the bytes into <media>/<transcriptId>, replacing any prior file.
    const dest = mediaFile(transcriptId);
    if (dest.exists) {
      dest.delete();
    }
    if (input.data !== undefined) {
      // Raw bytes: create then write the buffer.
      dest.create();
      dest.write(new Uint8Array(input.data));
    } else if (input.uri !== undefined) {
      // Copy the source file into app storage at the destination path.
      const src = new File(input.uri);
      await src.copy(dest);
    } else {
      throw new Error('putMedia requires either input.data or input.uri');
    }

    // Upsert the media row and flag the transcript as having media.
    await db.runAsync(
      `INSERT INTO media (transcriptId, path, mime) VALUES (?, ?, ?)
         ON CONFLICT(transcriptId) DO UPDATE SET path = excluded.path, mime = excluded.mime`,
      [transcriptId, dest.uri, input.mime],
    );
    await db.runAsync(`UPDATE transcripts SET hasMedia = 1 WHERE id = ?`, [
      transcriptId,
    ]);
  },

  async getMediaUrl(transcriptId: string): Promise<string | undefined> {
    const db = await getDb();
    const row = await db.getFirstAsync<MediaRow>(
      `SELECT transcriptId, path, mime FROM media WHERE transcriptId = ?`,
      [transcriptId],
    );
    return row ? row.path : undefined;
  },

  async removeMedia(transcriptId: string): Promise<void> {
    const db = await getDb();
    const row = await db.getFirstAsync<MediaRow>(
      `SELECT transcriptId, path, mime FROM media WHERE transcriptId = ?`,
      [transcriptId],
    );
    if (!row) return; // No-op if absent.

    // Delete the file on disk (best-effort: tolerate an already-missing file).
    try {
      const file = new File(row.path);
      if (file.exists) file.delete();
    } catch {
      // Ignore filesystem errors so the DB row is still cleaned up below.
    }

    await db.runAsync(`DELETE FROM media WHERE transcriptId = ?`, [transcriptId]);
    await db.runAsync(`UPDATE transcripts SET hasMedia = 0 WHERE id = ?`, [
      transcriptId,
    ]);
  },
};
