-- ============================================================================
-- verify-db-schema.sql
--
-- Purpose: Verify that a TL.db (e.g. a restored backup) has every column the
--          current URLNotesGrabberCORE code expects. The app has NO startup
--          migration: missing columns only get added when specific modes run,
--          and a referenced-but-missing column causes a "no such column" crash.
--
-- How to use (DB Browser for SQLite):
--   1. File > Open Database  ->  pick the restored backup.
--   2. Execute SQL tab. Run SECTION 1 (it is read-only).
--        * Zero rows from every query  = schema is fully aligned, you're done.
--        * Rows in "MISSING COLUMNS"    = copy the run_this_to_fix text.
--   3. If columns are missing: KEEP A COPY OF THE BACKUP FIRST, then go to
--      SECTION 2, uncomment ONLY the ALTER lines that match the report, and run.
--   4. Re-run SECTION 1 to confirm zero rows.
--
-- This script never UPDATEs/DELETEs/DROPs. In particular it deliberately does
-- NOT replicate the likes-reset that the app's -likes migration performs
-- (DataAccess.cs:375), so existing likes high-water marks are preserved.
-- ============================================================================


-- ============================================================================
-- SECTION 1  --  VERIFICATION (read-only)
-- ============================================================================

-- Expected schema for the current code version.
-- alter_stmt is a runnable ALTER for additively-fixable columns; for base
-- columns it is a 'MANUAL REVIEW' note (a missing base column means the backup
-- predates the table's creation or is damaged -- do not blindly auto-add).
WITH expected(tbl, col, alter_stmt) AS (
  VALUES
    -- Posts (base columns: manual review if missing)
    ('Posts','BlogName',              'MANUAL REVIEW - base/PK column missing'),
    ('Posts','PostID',                'MANUAL REVIEW - base/PK column missing'),
    ('Posts','HasNotesGathered',      'MANUAL REVIEW - base column missing'),
    ('Posts','reblogURL',             'MANUAL REVIEW - base column missing'),
    ('Posts','NotFound',              'MANUAL REVIEW - base column missing'),
    ('Posts','PostDate',              'MANUAL REVIEW - base column missing'),
    ('Posts','NotesGatheredDateTime', 'MANUAL REVIEW - base column missing'),
    ('Posts','HasImage',              'MANUAL REVIEW - base column missing'),
    ('Posts','PostURL',               'MANUAL REVIEW - base column missing'),
    ('Posts','Slug',                  'MANUAL REVIEW - base column missing'),
    ('Posts','ReblogKey',             'MANUAL REVIEW - base column missing'),
    ('Posts','ReblogName',            'MANUAL REVIEW - base column missing'),
    ('Posts','Summary',               'MANUAL REVIEW - base column missing'),
    ('Posts','Quote',                 'MANUAL REVIEW - base column missing'),
    ('Posts','Body',                  'MANUAL REVIEW - base column missing'),
    ('Posts','Tags',                  'MANUAL REVIEW - base column missing'),
    ('Posts','Link',                  'MANUAL REVIEW - base column missing'),
    ('Posts','PhotoURL',              'MANUAL REVIEW - base column missing'),
    ('Posts','PhotoCaption',          'MANUAL REVIEW - base column missing'),
    ('Posts','DownloadedFiles',       'MANUAL REVIEW - base column missing'),
    ('Posts','AudioCaption',          'MANUAL REVIEW - base column missing'),
    ('Posts','Question',              'MANUAL REVIEW - base column missing'),
    ('Posts','Answer',                'MANUAL REVIEW - base column missing'),
    ('Posts','Title',                 'MANUAL REVIEW - base column missing'),
    ('Posts','ByLikes',               'MANUAL REVIEW - base column missing'),
    ('Posts','RootBlogName',          'MANUAL REVIEW - base column missing'),
    ('Posts','RootURL',               'MANUAL REVIEW - base column missing'),
    ('Posts','DateModified',          'MANUAL REVIEW - base column missing'),
    ('Posts','DateCreated',           'MANUAL REVIEW - base column missing'),
    -- Posts (additive migration column, auto-fixable)
    ('Posts','PostType',              'ALTER TABLE Posts ADD COLUMN PostType TEXT;'),

    -- Blogs (base columns: manual review if missing)
    ('Blogs','BlogName',              'MANUAL REVIEW - base/PK column missing'),
    ('Blogs','HasBeenOutput',         'MANUAL REVIEW - base column missing'),
    ('Blogs','IsActive',              'MANUAL REVIEW - base column missing'),
    ('Blogs','DateAdded',             'MANUAL REVIEW - base column missing'),
    ('Blogs','ByLikes',               'MANUAL REVIEW - base column missing'),
    ('Blogs','DateModified',          'MANUAL REVIEW - base column missing'),
    ('Blogs','DateCreated',           'MANUAL REVIEW - base column missing'),
    -- Blogs (additive migration columns, auto-fixable)
    ('Blogs','LikesPulled',           'ALTER TABLE Blogs ADD COLUMN LikesPulled INTEGER DEFAULT 0;'),
    ('Blogs','LikesCursor',           'ALTER TABLE Blogs ADD COLUMN LikesCursor INTEGER DEFAULT 0;'),
    ('Blogs','LikesNewestTimestamp',  'ALTER TABLE Blogs ADD COLUMN LikesNewestTimestamp INTEGER DEFAULT 0;'),
    ('Blogs','LikesLastRefreshed',    'ALTER TABLE Blogs ADD COLUMN LikesLastRefreshed INTEGER DEFAULT 0;'),
    ('Blogs','LikesLastNewCount',     'ALTER TABLE Blogs ADD COLUMN LikesLastNewCount INTEGER DEFAULT 0;'),
    ('Blogs','TTFolderPath',          'ALTER TABLE Blogs ADD COLUMN TTFolderPath TEXT;'),
    -- Blogs.BlogId (2026-08-07) is the single-hop join key into Notes. Deliberately NOT
    -- auto-fixable: an added-but-empty BlogId makes every engagement join return zero
    -- rows silently, which is worse than the hard error a missing column gives.
    ('Blogs','BlogId',                'MANUAL REVIEW - see query 1d: run normalize-notes.sql'),

    -- Notes (base columns: manual review if missing)
    -- Integer IDs since 2026-08-07. RootBlogName/NoteBlogName/Type are GONE, not renamed
    -- in place -- a backup that still has them needs normalize-notes.sql, not an ALTER.
    -- Query 1d below reports exactly that case.
    ('Notes','RootBlogId',            'MANUAL REVIEW - see query 1d: pre-2026-08-07 name schema, or damaged'),
    ('Notes','PostID',                'MANUAL REVIEW - base/PK column missing'),
    ('Notes','NoteBlogId',            'MANUAL REVIEW - see query 1d: pre-2026-08-07 name schema, or damaged'),
    ('Notes','TimeStamp',             'MANUAL REVIEW - base/PK column missing'),
    ('Notes','TypeId',                'MANUAL REVIEW - see query 1d: pre-2026-08-07 name schema, or damaged'),
    ('Notes','DatetimeCrawled',       'MANUAL REVIEW - base column missing'),
    ('Notes','DateModified',          'MANUAL REVIEW - base column missing'),
    ('Notes','DateCreated',           'MANUAL REVIEW - base column missing'),
    -- Notes (additive migration column, auto-fixable)
    -- No DEFAULT: the migrated schema dropped it, so new rows get NULL rather than a
    -- placeholder. EnsureReplyTextColumnExists in DataAccess.cs adds it the same way.
    ('Notes','replyText',             'ALTER TABLE Notes ADD COLUMN replyText TEXT;'),

    -- BlogNames / NoteTypes (the lookup tables Notes resolves its IDs through, 2026-08-07).
    -- Not auto-fixable: an empty BlogNames does not mean "add the table", it means the
    -- Notes rows have nothing to resolve against. Rebuild with normalize-notes.sql.
    ('BlogNames','BlogId',            'MANUAL REVIEW - see query 1d: run normalize-notes.sql'),
    ('BlogNames','BlogName',          'MANUAL REVIEW - see query 1d: run normalize-notes.sql'),
    ('NoteTypes','TypeId',            'MANUAL REVIEW - see query 1d: run normalize-notes.sql'),
    ('NoteTypes','Type',              'MANUAL REVIEW - see query 1d: run normalize-notes.sql'),

    -- DailyAPICount (base columns)
    ('DailyAPICount','Date',          'MANUAL REVIEW - base/PK column missing'),
    ('DailyAPICount','APICount',      'MANUAL REVIEW - base column missing'),

    -- ApiKeyPoolState (created at runtime by EnsureApiKeyPoolTables; auto-fixable by re-running app, but safe to add)
    ('ApiKeyPoolState','KeyName',     'MANUAL REVIEW - run app once to auto-create ApiKeyPool tables'),
    ('ApiKeyPoolState','RetryUntil',  'MANUAL REVIEW - run app once to auto-create ApiKeyPool tables'),
    ('ApiKeyPoolMeta','Id',           'MANUAL REVIEW - run app once to auto-create ApiKeyPool tables'),
    ('ApiKeyPoolMeta','LastIndex',    'MANUAL REVIEW - run app once to auto-create ApiKeyPool tables')
),
actual(tbl, col) AS (
  SELECT 'Posts',           name FROM pragma_table_info('Posts')
  UNION ALL SELECT 'Blogs',           name FROM pragma_table_info('Blogs')
  UNION ALL SELECT 'Notes',           name FROM pragma_table_info('Notes')
  UNION ALL SELECT 'BlogNames',       name FROM pragma_table_info('BlogNames')
  UNION ALL SELECT 'NoteTypes',       name FROM pragma_table_info('NoteTypes')
  UNION ALL SELECT 'DailyAPICount',   name FROM pragma_table_info('DailyAPICount')
  UNION ALL SELECT 'ApiKeyPoolState', name FROM pragma_table_info('ApiKeyPoolState')
  UNION ALL SELECT 'ApiKeyPoolMeta',  name FROM pragma_table_info('ApiKeyPoolMeta')
)

-- 1a. MISSING COLUMNS: columns the code needs that the DB does not have.
--     Zero rows = good. Otherwise copy run_this_to_fix into SECTION 2.
SELECT
  e.tbl              AS table_name,
  e.col              AS missing_column,
  e.alter_stmt       AS run_this_to_fix
FROM expected e
LEFT JOIN actual a
  ON a.tbl = e.tbl AND lower(a.col) = lower(e.col)
WHERE a.col IS NULL
ORDER BY (e.alter_stmt LIKE 'ALTER%') DESC, e.tbl, e.col;


-- 1b. MISSING TABLES: expected tables that don't exist at all in this DB.
--     Zero rows = good.
WITH expected_tables(tbl) AS (
  VALUES ('Posts'),('Blogs'),('Notes'),('BlogNames'),('NoteTypes'),('DailyAPICount'),
         ('ApiKeyPoolState'),('ApiKeyPoolMeta')
)
SELECT et.tbl AS missing_table
FROM expected_tables et
WHERE NOT EXISTS (
  SELECT 1 FROM sqlite_master
  WHERE type = 'table' AND lower(name) = lower(et.tbl)
)
ORDER BY et.tbl;


-- 1c. EXTRA / UNEXPECTED COLUMNS: present in the DB but not in the expected
--     list above. Informational only -- e.g. a NEWER backup, or a column this
--     script's expected-list hasn't been updated for. Not an error by itself.
--     Posts.IsActive and Notes.IsActive are listed here and NOT in 1a on
--     purpose: they are written by other tools, the app only reads them when
--     present, and it must not be told to add them. See TL.db.md.
WITH expected(tbl, col) AS (
  VALUES
    ('Posts','BlogName'),('Posts','PostID'),('Posts','HasNotesGathered'),('Posts','reblogURL'),
    ('Posts','NotFound'),('Posts','PostDate'),('Posts','NotesGatheredDateTime'),('Posts','HasImage'),
    ('Posts','PostURL'),('Posts','Slug'),('Posts','ReblogKey'),('Posts','ReblogName'),('Posts','Summary'),
    ('Posts','Quote'),('Posts','Body'),('Posts','Tags'),('Posts','Link'),('Posts','PhotoURL'),
    ('Posts','PhotoCaption'),('Posts','DownloadedFiles'),('Posts','AudioCaption'),('Posts','Question'),
    ('Posts','Answer'),('Posts','Title'),('Posts','ByLikes'),('Posts','RootBlogName'),('Posts','RootURL'),
    ('Posts','DateModified'),('Posts','DateCreated'),('Posts','PostType'),('Posts','IsActive'),
    ('Blogs','BlogName'),('Blogs','HasBeenOutput'),('Blogs','IsActive'),('Blogs','DateAdded'),
    ('Blogs','ByLikes'),('Blogs','DateModified'),('Blogs','DateCreated'),('Blogs','LikesPulled'),
    ('Blogs','LikesCursor'),('Blogs','LikesNewestTimestamp'),('Blogs','LikesLastRefreshed'),
    ('Blogs','LikesLastNewCount'),('Blogs','TTFolderPath'),('Blogs','BlogId'),
    ('Notes','RootBlogId'),('Notes','PostID'),('Notes','NoteBlogId'),('Notes','TimeStamp'),
    ('Notes','TypeId'),('Notes','DatetimeCrawled'),('Notes','DateModified'),('Notes','DateCreated'),
    ('Notes','replyText'),('Notes','IsActive'),
    ('BlogNames','BlogId'),('BlogNames','BlogName'),
    ('NoteTypes','TypeId'),('NoteTypes','Type'),
    ('DailyAPICount','Date'),('DailyAPICount','APICount'),
    ('ApiKeyPoolState','KeyName'),('ApiKeyPoolState','RetryUntil'),
    ('ApiKeyPoolMeta','Id'),('ApiKeyPoolMeta','LastIndex')
),
actual(tbl, col) AS (
  SELECT 'Posts',           name FROM pragma_table_info('Posts')
  UNION ALL SELECT 'Blogs',           name FROM pragma_table_info('Blogs')
  UNION ALL SELECT 'Notes',           name FROM pragma_table_info('Notes')
  UNION ALL SELECT 'BlogNames',       name FROM pragma_table_info('BlogNames')
  UNION ALL SELECT 'NoteTypes',       name FROM pragma_table_info('NoteTypes')
  UNION ALL SELECT 'DailyAPICount',   name FROM pragma_table_info('DailyAPICount')
  UNION ALL SELECT 'ApiKeyPoolState', name FROM pragma_table_info('ApiKeyPoolState')
  UNION ALL SELECT 'ApiKeyPoolMeta',  name FROM pragma_table_info('ApiKeyPoolMeta')
)
SELECT a.tbl AS table_name, a.col AS unexpected_column
FROM actual a
LEFT JOIN expected e
  ON e.tbl = a.tbl AND lower(e.col) = lower(a.col)
WHERE e.col IS NULL
ORDER BY a.tbl, a.col;


-- 1d. PRE-MIGRATION DATABASE: a backup from before 2026-08-07, when Notes still
--     stored names. Zero rows = good.
--
--     This is the one failure SECTION 2 cannot fix. Notes.RootBlogName /
--     NoteBlogName / Type were replaced by RootBlogId / NoteBlogId / TypeId
--     resolving through BlogNames and NoteTypes -- a data migration, not an
--     ADD COLUMN. There is no compatibility view, so the current code fails
--     outright ("no such column: RootBlogId") against such a file.
--
--     Fix: run normalize-notes.sql against a COPY of the backup, then re-run
--     SECTION 1. Do not hand-add the ID columns: they would be empty, and an
--     empty NoteBlogId is indistinguishable from a note by blog #0.
SELECT 'Notes still stores names -- run normalize-notes.sql on a copy' AS pre_migration_schema,
       group_concat(name, ', ')                                        AS legacy_columns_found
FROM pragma_table_info('Notes')
WHERE lower(name) IN ('rootblogname','noteblogname','type')
HAVING COUNT(*) > 0;


-- ============================================================================
-- SECTION 2  --  FIX (opt-in, additive only)
--
-- Run ONLY the lines that query 1a flagged with an ALTER statement.
-- KEEP A COPY OF THE BACKUP FIRST. SQLite has no "ADD COLUMN IF NOT EXISTS",
-- so running an ALTER for a column that already exists throws a harmless
-- "duplicate column name" error and changes nothing -- just run the flagged
-- subset. These are the 8 additive migration columns and nothing else; the
-- likes high-water-mark reset is intentionally NOT included.
--
-- Nothing here addresses query 1d. The Notes integer schema is a data migration
-- (normalize-notes.sql) and cannot be reached by adding columns.
-- ============================================================================

-- ALTER TABLE Posts ADD COLUMN PostType TEXT;
-- ALTER TABLE Blogs ADD COLUMN LikesPulled INTEGER DEFAULT 0;
-- ALTER TABLE Blogs ADD COLUMN LikesCursor INTEGER DEFAULT 0;
-- ALTER TABLE Blogs ADD COLUMN LikesNewestTimestamp INTEGER DEFAULT 0;
-- ALTER TABLE Blogs ADD COLUMN LikesLastRefreshed INTEGER DEFAULT 0;
-- ALTER TABLE Blogs ADD COLUMN LikesLastNewCount INTEGER DEFAULT 0;
-- ALTER TABLE Blogs ADD COLUMN TTFolderPath TEXT;
-- ALTER TABLE Notes ADD COLUMN replyText TEXT;