import { sql } from 'drizzle-orm';
import { index, integer, sqliteTable, text, uniqueIndex } from 'drizzle-orm/sqlite-core';

// Feeds table - stores RSS/Atom feed information
export const feeds = sqliteTable(
  'feeds',
  {
    id: integer('id').primaryKey({ autoIncrement: true }),
    title: text('title').notNull(),
    titleOverridden: integer('title_overridden', { mode: 'boolean' }).notNull().default(false),
    xmlUrl: text('xml_url').notNull().unique(),
    htmlUrl: text('html_url'),
    description: text('description'),
    category: text('category'),
    lastFetchedAt: integer('last_fetched_at', { mode: 'timestamp' }),
    lastModified: text('last_modified'), // HTTP Last-Modified header
    etag: text('etag'), // HTTP ETag header
    fetchError: text('fetch_error'), // Last fetch error if any
    errorCount: integer('error_count').notNull().default(0),
    // Threshold group for the current consecutive-error streak. Nullable so
    // rows migrated from older databases conservatively start a fresh streak.
    errorCategory: text('error_category', { enum: ['permanent', 'recoverable'] }),
    isActive: integer('is_active', { mode: 'boolean' }).notNull().default(true),
    createdAt: integer('created_at', { mode: 'timestamp' }).$defaultFn(() => new Date()),
    updatedAt: integer('updated_at', { mode: 'timestamp' }).$defaultFn(() => new Date()),
  },
  (table) => ({
    categoryIdx: index('feeds_category_idx').on(table.category),
    activeIdx: index('feeds_active_idx').on(table.isActive),
  }),
);

// Articles table - stores individual feed items
export const articles = sqliteTable(
  'articles',
  {
    id: integer('id').primaryKey({ autoIncrement: true }),
    feedId: integer('feed_id')
      .notNull()
      .references(() => feeds.id, { onDelete: 'cascade' }),
    guid: text('guid'), // Unique identifier from feed
    title: text('title'),
    link: text('link').notNull(),
    description: text('description'),
    content: text('content'), // Full article content supplied by a feed or the extractor
    author: text('author'),
    categories: text('categories'), // JSON array of categories
    publishedAt: integer('published_at', { mode: 'timestamp' }),
    isRead: integer('is_read', { mode: 'boolean' }).notNull().default(false),
    isStarred: integer('is_starred', { mode: 'boolean' }).notNull().default(false),
    isBroken: integer('is_broken', { mode: 'boolean' }).notNull().default(false),
    brokenReason: text('broken_reason'),
    linkFixed: text('link_fixed'), // Fixed URL if original was broken
    // Last extractor attempt. Feed-provided content is valid without this timestamp.
    contentFetchedAt: integer('content_fetched_at', { mode: 'timestamp' }),
    createdAt: integer('created_at', { mode: 'timestamp' }).$defaultFn(() => new Date()),
    updatedAt: integer('updated_at', { mode: 'timestamp' }).$defaultFn(() => new Date()),
  },
  (table) => ({
    feedIdx: index('articles_feed_idx').on(table.feedId),
    guidIdx: index('articles_guid_idx').on(table.guid),
    linkIdx: index('articles_link_idx').on(table.link),
    publishedIdx: index('articles_published_idx').on(table.publishedAt),
    readIdx: index('articles_read_idx').on(table.isRead),
    brokenIdx: index('articles_broken_idx').on(table.isBroken),
    feedReadIdx: index('articles_feed_read_idx').on(table.feedId, table.isRead),
    feedPubIdx: index('articles_feed_pub_idx').on(table.feedId, table.publishedAt),
    authorIdx: index('articles_author_idx').on(table.author),
    starredIdx: index('articles_starred_idx').on(table.isStarred),
    contentFetchedAtIdx: index('articles_content_fetched_at_idx').on(table.contentFetchedAt),
    // Article identity is scoped to a Feed. Empty GUIDs are an RSS convention
    // for "not supplied", so only meaningful GUIDs participate in uniqueness.
    feedGuidUnique: uniqueIndex('articles_feed_guid_unique')
      .on(table.feedId, table.guid)
      .where(sql`${table.guid} IS NOT NULL AND ${table.guid} <> ''`),
    feedLinkUnique: uniqueIndex('articles_feed_link_unique').on(table.feedId, table.link),
  }),
);

/**
 * Durable, append-only audit rows written while the 0003 article-identity
 * migration removes legacy duplicates. This intentionally has no foreign keys:
 * a later Feed/Article deletion must not erase forensic recovery data.
 *
 * Application code uses raw SQL for this internal table in migration-repair.ts;
 * it is present here so the Drizzle snapshot accurately records the database.
 */
export const articleIdentityRepairBackups = sqliteTable(
  'article_identity_repair_backups',
  {
    id: integer('id').primaryKey({ autoIncrement: true }),
    articleId: integer('article_id').notNull().unique(),
    survivorArticleId: integer('survivor_article_id').notNull(),
    reason: text('reason').notNull(),
    removedAt: integer('removed_at').notNull(),
    feedId: integer('feed_id').notNull(),
    guid: text('guid'),
    title: text('title'),
    link: text('link').notNull(),
    description: text('description'),
    content: text('content'),
    author: text('author'),
    categories: text('categories'),
    publishedAt: integer('published_at'),
    isRead: integer('is_read', { mode: 'boolean' }).notNull(),
    isStarred: integer('is_starred', { mode: 'boolean' }).notNull(),
    isBroken: integer('is_broken', { mode: 'boolean' }).notNull(),
    brokenReason: text('broken_reason'),
    linkFixed: text('link_fixed'),
    contentFetchedAt: integer('content_fetched_at'),
    createdAt: integer('created_at'),
    updatedAt: integer('updated_at'),
  },
  (table) => ({
    survivorIdx: index('article_identity_repair_backups_survivor_idx').on(table.survivorArticleId),
  }),
);

/**
 * Durable keyset cursors for bounded maintenance work that must make progress
 * across separate CLI processes. Values are internal implementation state,
 * not user-visible reader state.
 */
export const maintenanceCursors = sqliteTable('maintenance_cursors', {
  key: text('key').primaryKey(),
  lastId: integer('last_id').notNull().default(0),
  updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull(),
});

// Fetch cache table - stores HTTP response metadata for smart caching
export const fetchCache = sqliteTable(
  'fetch_cache',
  {
    id: integer('id').primaryKey({ autoIncrement: true }),
    url: text('url').notNull().unique(),
    etag: text('etag'),
    lastModified: text('last_modified'),
    contentHash: text('content_hash'), // Hash of response content
    statusCode: integer('status_code'),
    fetchedAt: integer('fetched_at', { mode: 'timestamp' }).$defaultFn(() => new Date()),
  },
  (_table) => ({}),
);

// Opml import history
export const opmlImports = sqliteTable('opml_imports', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  filePath: text('file_path').notNull(),
  fileName: text('file_name'),
  feedsImported: integer('feeds_imported').notNull().default(0),
  feedsSkipped: integer('feeds_skipped').notNull().default(0),
  importedAt: integer('imported_at', { mode: 'timestamp' }).$defaultFn(() => new Date()),
});

// Types for insert/select operations
type FeedRow = typeof feeds.$inferSelect;
// Keep the newly persisted streak group optional at API construction seams so
// callers and fixtures compiled against the pre-migration Feed shape remain
// source-compatible. Rows selected from SQLite always include it as string or
// null after migrations run.
export type Feed = Omit<FeedRow, 'errorCategory'> & { errorCategory?: FeedRow['errorCategory'] };
export type NewFeed = typeof feeds.$inferInsert;
export type Article = typeof articles.$inferSelect;
export type NewArticle = typeof articles.$inferInsert;
export type ArticleIdentityRepairBackup = typeof articleIdentityRepairBackups.$inferSelect;