import type Database from 'better-sqlite3';
import { buildVisibilityWhere } from '../bridge/visibility.js';

export interface NoteIndexRow {
  owner_id: string;
  folder: string;
  file_name: string;
  title: string | null;
  visibility: 'private' | 'org' | 'public';
  visibility_scope_org_id: string | null;
  mode_hint: 'search' | 'inject' | null;
  tags_json: string;
  content_size: number;
  content_hash: string;
  body: string;
  updated_at: number;
}

export interface NoteSubscriptionRow {
  consumer_user_id: string;
  publisher_user_id: string;
  folder: string;
  mode: 'search' | 'inject';
  enabled: number;
  created_at: number;
}

export interface DiscoverOptions {
  user: Express.User;
  ownerId?: string;
  folder?: string;
  limit?: number;
  offset?: number;
}

export interface SearchOptions {
  user: Express.User;
  query: string;
  folder?: string;
  limit?: number;
  consumerSubscribed?: boolean; // if true, restrict to consumer's search-mode subscriptions
}

export interface InjectableRow extends NoteIndexRow {
  publisher_name: string | null;
}

export class NotesRepository {
  constructor(private readonly db: Database.Database) {}

  upsertNote(row: NoteIndexRow): void {
    this.db.prepare(`
      INSERT INTO note_index (owner_id, folder, file_name, title, visibility, visibility_scope_org_id, mode_hint, tags_json, content_size, content_hash, body, updated_at)
      VALUES (@owner_id, @folder, @file_name, @title, @visibility, @visibility_scope_org_id, @mode_hint, @tags_json, @content_size, @content_hash, @body, @updated_at)
      ON CONFLICT(owner_id, folder, file_name) DO UPDATE SET
        title = excluded.title,
        visibility = excluded.visibility,
        visibility_scope_org_id = excluded.visibility_scope_org_id,
        mode_hint = excluded.mode_hint,
        tags_json = excluded.tags_json,
        content_size = excluded.content_size,
        content_hash = excluded.content_hash,
        body = excluded.body,
        updated_at = excluded.updated_at
    `).run(row);
  }

  getNote(ownerId: string, folder: string, fileName: string): NoteIndexRow | null {
    const row = this.db.prepare(`SELECT * FROM note_index WHERE owner_id=? AND folder=? AND file_name=?`).get(ownerId, folder, fileName);
    return (row as NoteIndexRow | undefined) ?? null;
  }

  deleteNote(ownerId: string, folder: string, fileName: string): void {
    this.db.prepare(`DELETE FROM note_index WHERE owner_id=? AND folder=? AND file_name=?`).run(ownerId, folder, fileName);
  }

  listFolder(ownerId: string, folder: string): NoteIndexRow[] {
    return this.db.prepare(`SELECT * FROM note_index WHERE owner_id=? AND folder=? ORDER BY file_name`).all(ownerId, folder) as NoteIndexRow[];
  }

  /** List all distinct folders for an owner (no visibility check — caller is owner). */
  listFoldersForOwner(ownerId: string): string[] {
    return (this.db.prepare(`SELECT DISTINCT folder FROM note_index WHERE owner_id=? ORDER BY folder`).all(ownerId) as Array<{folder: string}>)
      .map((r) => r.folder);
  }

  discover(opts: DiscoverOptions): NoteIndexRow[] {
    const vis = buildVisibilityWhere(opts.user, 'n');
    const wheres = [vis.clause];
    const params: unknown[] = [...vis.params];
    if (opts.ownerId) { wheres.push('n.owner_id = ?'); params.push(opts.ownerId); }
    if (opts.folder) { wheres.push('n.folder = ?'); params.push(opts.folder); }
    const limit = Math.min(opts.limit ?? 50, 200);
    const offset = opts.offset ?? 0;
    return this.db.prepare(`
      SELECT n.* FROM note_index n
      WHERE ${wheres.join(' AND ')}
      ORDER BY n.updated_at DESC, n.owner_id, n.folder, n.file_name
      LIMIT ? OFFSET ?
    `).all(...params, limit, offset) as NoteIndexRow[];
  }

  search(opts: SearchOptions): NoteIndexRow[] {
    const vis = buildVisibilityWhere(opts.user, 'n');
    // Build params in SQL positional order:
    //   1. JOIN ? for consumer_user_id (only when consumerSubscribed)
    //   2. WHERE MATCH ? for query
    //   3. vis.params
    //   4. optional folder ?
    //   5. LIMIT ?
    const params: unknown[] = [];
    let joinSub = '';
    if (opts.consumerSubscribed) {
      joinSub = `INNER JOIN note_subscriptions s ON s.publisher_user_id = n.owner_id AND s.folder = n.folder AND s.consumer_user_id = ? AND s.mode = 'search' AND s.enabled = 1`;
      params.push(opts.user.id);
    }
    params.push(opts.query);
    params.push(...vis.params);
    const folderWhere = opts.folder ? 'AND n.folder = ?' : '';
    if (opts.folder) params.push(opts.folder);
    const limit = Math.min(opts.limit ?? 20, 100);
    return this.db.prepare(`
      SELECT n.* FROM note_index_fts fts
      INNER JOIN note_index n ON n.owner_id=fts.owner_id AND n.folder=fts.folder AND n.file_name=fts.file_name
      ${joinSub}
      WHERE note_index_fts MATCH ?
        AND ${vis.clause}
        ${folderWhere}
      ORDER BY rank
      LIMIT ?
    `).all(...params, limit) as NoteIndexRow[];
  }

  upsertSubscription(row: NoteSubscriptionRow): void {
    this.db.prepare(`
      INSERT INTO note_subscriptions (consumer_user_id, publisher_user_id, folder, mode, enabled, created_at)
      VALUES (@consumer_user_id, @publisher_user_id, @folder, @mode, @enabled, @created_at)
      ON CONFLICT(consumer_user_id, publisher_user_id, folder) DO UPDATE SET
        mode = excluded.mode,
        enabled = excluded.enabled
    `).run(row);
  }

  listSubscriptions(consumerUserId: string): NoteSubscriptionRow[] {
    return this.db.prepare(`SELECT * FROM note_subscriptions WHERE consumer_user_id=? ORDER BY publisher_user_id, folder`).all(consumerUserId) as NoteSubscriptionRow[];
  }

  deleteSubscription(consumerUserId: string, publisherUserId: string, folder: string): void {
    this.db.prepare(`DELETE FROM note_subscriptions WHERE consumer_user_id=? AND publisher_user_id=? AND folder=?`).run(consumerUserId, publisherUserId, folder);
  }

  /** All inject-mode notes for a consumer, visibility-checked, ordered for deterministic budget application. */
  injectableForConsumer(user: Express.User): InjectableRow[] {
    const vis = buildVisibilityWhere(user, 'n');
    return this.db.prepare(`
      SELECT n.*, u.name AS publisher_name
      FROM note_subscriptions s
      INNER JOIN note_index n ON n.owner_id = s.publisher_user_id AND n.folder = s.folder
      LEFT JOIN users u ON u.id = n.owner_id
      WHERE s.consumer_user_id = ?
        AND s.mode = 'inject'
        AND s.enabled = 1
        AND ${vis.clause}
      ORDER BY n.updated_at DESC, n.owner_id, n.folder, n.file_name
    `).all(user.id, ...vis.params) as InjectableRow[];
  }

  /**
   * Visibility-checked single-row existence check.
   * Returns true iff the note_index row exists AND is visible to `user`.
   * Unlike `discover()` this has no LIMIT, so it works for arbitrarily-large note lists.
   */
  isVisible(user: Express.User, ownerId: string, folder: string, fileName: string): boolean {
    const vis = buildVisibilityWhere(user, 'n');
    const row = this.db.prepare(`
      SELECT 1 FROM note_index n
      WHERE n.owner_id=? AND n.folder=? AND n.file_name=?
        AND ${vis.clause}
      LIMIT 1
    `).get(ownerId, folder, fileName, ...vis.params);
    return !!row;
  }

  addPendingReindex(ownerId: string, folder: string, fileName: string, reason: string): void {
    this.db.prepare(`
      INSERT OR REPLACE INTO pending_reindex (owner_id, folder, file_name, reason, created_at)
      VALUES (?, ?, ?, ?, ?)
    `).run(ownerId, folder, fileName, reason, Date.now());
  }
}
