db

var Migrations = []Migration{

	{
		Version:     2,
		Description: "Add action_log table for undo support",
		SQL: `
CREATE TABLE IF NOT EXISTS action_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id TEXT NOT NULL,
    action_type TEXT NOT NULL,
    entity_type TEXT NOT NULL,
    entity_id TEXT NOT NULL,
    previous_data TEXT DEFAULT '',
    new_data TEXT DEFAULT '',
    timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    undone INTEGER DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_action_log_session ON action_log(session_id);
CREATE INDEX IF NOT EXISTS idx_action_log_timestamp ON action_log(timestamp);
`,
	},
	{
		Version:     3,
		Description: "Allow work session logs without issue_id",
		SQL: `
-- SQLite doesn't support ALTER COLUMN, so we need to recreate the table
CREATE TABLE logs_new (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    issue_id TEXT DEFAULT '',
    session_id TEXT NOT NULL,
    work_session_id TEXT DEFAULT '',
    message TEXT NOT NULL,
    type TEXT NOT NULL DEFAULT 'progress',
    timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO logs_new SELECT * FROM logs;
DROP TABLE logs;
ALTER TABLE logs_new RENAME TO logs;
CREATE INDEX IF NOT EXISTS idx_logs_issue ON logs(issue_id);
CREATE INDEX IF NOT EXISTS idx_logs_work_session ON logs(work_session_id);
`,
	},
	{
		Version:     4,
		Description: "Add minor flag to issues for self-reviewable tasks",
		SQL:         `ALTER TABLE issues ADD COLUMN minor INTEGER DEFAULT 0;`,
	},
	{
		Version:     5,
		Description: "Add created_branch to issues",
		SQL:         `ALTER TABLE issues ADD COLUMN created_branch TEXT DEFAULT '';`,
	},
	{
		Version:     6,
		Description: "Add creator_session for review enforcement",
		SQL:         `ALTER TABLE issues ADD COLUMN creator_session TEXT DEFAULT '';`,
	},
	{
		Version:     7,
		Description: "Add session history for review enforcement",
		SQL: `CREATE TABLE IF NOT EXISTS issue_session_history (
    id TEXT PRIMARY KEY,
    issue_id TEXT NOT NULL,
    session_id TEXT NOT NULL,
    action TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (issue_id) REFERENCES issues(id)
);
CREATE INDEX IF NOT EXISTS idx_ish_issue ON issue_session_history(issue_id);
CREATE INDEX IF NOT EXISTS idx_ish_session ON issue_session_history(session_id);`,
	},
	{
		Version:     8,
		Description: "Add timestamp indexes for activity queries",
		SQL: `CREATE INDEX IF NOT EXISTS idx_handoffs_timestamp ON handoffs(timestamp);
CREATE INDEX IF NOT EXISTS idx_issues_deleted_status ON issues(deleted_at, status);`,
	},
	{
		Version:     9,
		Description: "Add boards and board_issues tables",
		SQL: `
-- Boards table
CREATE TABLE IF NOT EXISTS boards (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL COLLATE NOCASE UNIQUE,
    last_viewed_at DATETIME,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Board-Issue membership with ordering
CREATE TABLE IF NOT EXISTS board_issues (
    board_id TEXT NOT NULL,
    issue_id TEXT NOT NULL,
    position INTEGER NOT NULL,
    added_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (board_id, issue_id),
    FOREIGN KEY (board_id) REFERENCES boards(id) ON DELETE CASCADE,
    FOREIGN KEY (issue_id) REFERENCES issues(id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_board_issues_position ON board_issues(board_id, position);
`,
	},
	{
		Version:     10,
		Description: "Query-based boards with sparse ordering and sprint field",
		SQL: `
-- Add query and is_builtin columns to boards
ALTER TABLE boards ADD COLUMN query TEXT NOT NULL DEFAULT '';
ALTER TABLE boards ADD COLUMN is_builtin INTEGER NOT NULL DEFAULT 0;

-- Rename board_issues to board_issue_positions for semantic clarity
DROP INDEX IF EXISTS idx_board_issues_position;
ALTER TABLE board_issues RENAME TO board_issue_positions;

-- Recreate index on positions
CREATE UNIQUE INDEX IF NOT EXISTS idx_board_positions_position
    ON board_issue_positions(board_id, position);

-- Add sprint field to issues
ALTER TABLE issues ADD COLUMN sprint TEXT DEFAULT '';

-- Create built-in "All Issues" board (empty query = all issues)
INSERT INTO boards (id, name, query, is_builtin, created_at, updated_at)
VALUES ('bd-all-issues', 'All Issues', '', 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
ON CONFLICT(name) DO UPDATE SET
    query = excluded.query,
    is_builtin = 1,
    updated_at = CURRENT_TIMESTAMP;
`,
	},
	{
		Version:     11,
		Description: "Add view_mode to boards for swimlanes/backlog toggle",
		SQL:         `ALTER TABLE boards ADD COLUMN view_mode TEXT NOT NULL DEFAULT 'swimlanes';`,
	},
	{
		Version:     12,
		Description: "Add missing indices for monitor query performance",
		SQL: `CREATE INDEX IF NOT EXISTS idx_action_log_entity_type ON action_log(entity_id, action_type);
CREATE INDEX IF NOT EXISTS idx_logs_timestamp ON logs(timestamp);
CREATE INDEX IF NOT EXISTS idx_comments_created_at ON comments(created_at);`,
	},
	{
		Version:     13,
		Description: "Extend sessions table for full DB-backed session storage",

		SQL: "",
	},
	{
		Version:     14,
		Description: "Repair sessions table for DBs where v13 did not apply correctly",

		SQL: "",
	},
	{
		Version:     15,
		Description: "Migrate integer PK tables to text IDs for sync compatibility",

		SQL: "",
	},
	{
		Version:     16,
		Description: "Add sync_state table and sync columns to action_log",
		SQL: `
CREATE TABLE IF NOT EXISTS sync_state (
    project_id TEXT PRIMARY KEY,
    last_pushed_action_id INTEGER DEFAULT 0,
    last_pulled_server_seq INTEGER DEFAULT 0,
    last_sync_at DATETIME,
    sync_disabled INTEGER DEFAULT 0
);
ALTER TABLE action_log ADD COLUMN synced_at DATETIME;
ALTER TABLE action_log ADD COLUMN server_seq INTEGER;
`,
	},
	{
		Version:     17,
		Description: "Add sync_conflicts table for overwrite tracking",
		SQL: `
CREATE TABLE IF NOT EXISTS sync_conflicts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    entity_type TEXT NOT NULL,
    entity_id TEXT NOT NULL,
    server_seq INTEGER NOT NULL,
    local_data JSON,
    remote_data JSON,
    overwritten_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_sync_conflicts_entity ON sync_conflicts(entity_type, entity_id);
CREATE INDEX IF NOT EXISTS idx_sync_conflicts_time ON sync_conflicts(overwritten_at);
CREATE INDEX IF NOT EXISTS idx_sync_conflicts_seq ON sync_conflicts(server_seq);
`,
	},
	{
		Version:     18,
		Description: "Add deterministic ID columns to composite-key tables for sync",

		SQL: "",
	},
	{
		Version:     19,
		Description: "Convert absolute file paths to repo-relative in issue_files",

		SQL: "",
	},
	{
		Version:     20,
		Description: "Normalize legacy action_log entries for composite-key entities",

		SQL: "",
	},
	{
		Version:     21,
		Description: "Add sync_history table for tracking sync operations",
		SQL: `
CREATE TABLE IF NOT EXISTS sync_history (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    direction TEXT NOT NULL,
    action_type TEXT NOT NULL,
    entity_type TEXT NOT NULL,
    entity_id TEXT NOT NULL,
    server_seq INTEGER,
    device_id TEXT DEFAULT '',
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_sync_history_ts ON sync_history(timestamp);
`,
	},
	{
		Version:     22,
		Description: "Sparse positioning: drop unique position index, re-space with gaps",
		SQL: `
DROP INDEX IF EXISTS idx_board_positions_position;
UPDATE board_issue_positions SET position = position * 65536;
`,
	},
	{
		Version:     23,
		Description: "Drop UNIQUE(name) on boards to prevent sync data loss",
		SQL: `
CREATE TABLE boards_new (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL COLLATE NOCASE,
    last_viewed_at DATETIME,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    query TEXT NOT NULL DEFAULT '',
    is_builtin INTEGER NOT NULL DEFAULT 0,
    view_mode TEXT NOT NULL DEFAULT 'swimlanes'
);
INSERT INTO boards_new SELECT * FROM boards;
DROP TABLE boards;
ALTER TABLE boards_new RENAME TO boards;
`,
	},
	{
		Version:     24,
		Description: "Add deterministic id column to work_session_issues for sync",

		SQL: "",
	},
	{
		Version:     25,
		Description: "Add deleted_at to board_issue_positions for soft delete sync",

		SQL: "",
	},
	{
		Version:     26,
		Description: "Enforce NOT NULL on action_log.id by fixing NULL values and recreating table",

		SQL: "",
	},
	{
		Version:     27,
		Description: "Normalize NULL session fields in issues",
		SQL: `
UPDATE issues SET implementer_session = '' WHERE implementer_session IS NULL;
UPDATE issues SET reviewer_session = '' WHERE reviewer_session IS NULL;
UPDATE issues SET creator_session = '' WHERE creator_session IS NULL;
`,
	},
	{
		Version:     28,
		Description: "Add notes table for sidecar notes sync",
		SQL: `
CREATE TABLE IF NOT EXISTS notes (
    id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    pinned INTEGER DEFAULT 0,
    archived INTEGER DEFAULT 0,
    deleted_at TEXT
);
CREATE INDEX IF NOT EXISTS idx_notes_updated ON notes(updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_notes_deleted ON notes(deleted_at);
`,
	},
}

var QueryValidator func(queryStr string) error