import type { Database } from 'bun:sqlite';

export type Migration = {
  version: number;
  name: string;
  checksum: string; // estático para trazabilidad básica
  up: (db: Database) => void | Promise<void>;
};

function tableHasColumn(db: Database, table: string, column: string): boolean {
  const cols = db.query(`PRAGMA table_info(${table})`).all() as Array<{ name?: string }>;
  return Array.isArray(cols) && cols.some((c: any) => c.name === column);
}

export const migrations: Migration[] = [
  {
    version: 1,
    name: 'initial-schema',
    checksum: 'v1-initial-schema-2025-09-06',
    up: (db: Database) => {
      // Esquema inicial (equivalente al initializeDatabase actual)
      db.exec(`PRAGMA foreign_keys = ON;`);

      db.exec(`
        CREATE TABLE IF NOT EXISTS users (
          id TEXT PRIMARY KEY,
          first_seen TEXT DEFAULT (strftime('%Y-%m-%d %H:%M:%f', 'now')),
          last_seen TEXT DEFAULT (strftime('%Y-%m-%d %H:%M:%f', 'now'))
        );
      `);

      db.exec(`
        CREATE TABLE IF NOT EXISTS groups (
          id TEXT PRIMARY KEY,
          community_id TEXT NOT NULL,
          name TEXT,
          last_verified TEXT DEFAULT (strftime('%Y-%m-%d %H:%M:%f', 'now')),
          active BOOLEAN DEFAULT TRUE
        );
      `);

      db.exec(`
        CREATE TABLE IF NOT EXISTS tasks (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          description TEXT NOT NULL,
          created_at TEXT DEFAULT (strftime('%Y-%m-%d %H:%M:%f', 'now')),
          due_date TEXT NULL,
          completed BOOLEAN DEFAULT FALSE,
          completed_at TEXT NULL,
          group_id TEXT NULL,
          created_by TEXT NOT NULL,
          completed_by TEXT NULL,
          FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE,
          FOREIGN KEY (completed_by) REFERENCES users(id) ON DELETE SET NULL,
          FOREIGN KEY (group_id) REFERENCES groups(id) ON DELETE SET NULL
        );
      `);

      db.exec(`
        CREATE TABLE IF NOT EXISTS task_assignments (
          task_id INTEGER NOT NULL,
          user_id TEXT NOT NULL,
          assigned_by TEXT NOT NULL,
          assigned_at TEXT DEFAULT (strftime('%Y-%m-%d %H:%M:%f', 'now')),
          PRIMARY KEY (task_id, user_id),
          FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
          FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
          FOREIGN KEY (assigned_by) REFERENCES users(id) ON DELETE CASCADE
        );
      `);

      db.exec(`
        CREATE TABLE IF NOT EXISTS response_queue (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          recipient TEXT NOT NULL,
          message TEXT NOT NULL,
          status TEXT NOT NULL DEFAULT 'queued' CHECK (status IN ('queued','processing','sent','failed')),
          attempts INTEGER NOT NULL DEFAULT 0,
          last_error TEXT NULL,
          metadata TEXT NULL,
          created_at TEXT DEFAULT (strftime('%Y-%m-%d %H:%M:%f', 'now')),
          updated_at TEXT DEFAULT (strftime('%Y-%m-%d %H:%M:%f', 'now'))
        );
      `);

      db.exec(`
        CREATE INDEX IF NOT EXISTS idx_response_queue_status_created_at
        ON response_queue (status, created_at);
      `);
    }
  },
  {
    version: 2,
    name: 'response-queue-reliability',
    checksum: 'v2-rq-reliability-2025-09-06',
    up: (db: Database) => {
      // Añadir columnas necesarias si no existen (idempotente)
      if (!tableHasColumn(db, 'response_queue', 'next_attempt_at')) {
        db.exec(`ALTER TABLE response_queue ADD COLUMN next_attempt_at TEXT NULL;`);
      }
      if (!tableHasColumn(db, 'response_queue', 'lease_until')) {
        db.exec(`ALTER TABLE response_queue ADD COLUMN lease_until TEXT NULL;`);
      }
      if (!tableHasColumn(db, 'response_queue', 'last_status_code')) {
        db.exec(`ALTER TABLE response_queue ADD COLUMN last_status_code INTEGER NULL;`);
      }

      // Índices complementarios
      db.exec(`
        CREATE INDEX IF NOT EXISTS idx_response_queue_status_next_attempt
        ON response_queue (status, next_attempt_at);
      `);
      db.exec(`
        CREATE INDEX IF NOT EXISTS idx_response_queue_status_lease_until
        ON response_queue (status, lease_until);
      `);
    }
  },
  {
    version: 3,
    name: 'response-queue-retention-index',
    checksum: 'v3-rq-retention-index-2025-09-06',
    up: (db: Database) => {
      db.exec(`
        CREATE INDEX IF NOT EXISTS idx_response_queue_status_updated_at
        ON response_queue (status, updated_at);
      `);
    }
  },
  {
    version: 4,
    name: 'user-preferences-reminders',
    checksum: 'v4-user-preferences-2025-09-07',
    up: (db: Database) => {
      db.exec(`
        CREATE TABLE IF NOT EXISTS user_preferences (
          user_id TEXT PRIMARY KEY,
          reminder_freq TEXT NOT NULL DEFAULT 'off' CHECK (reminder_freq IN ('off','daily','weekly')),
          reminder_time TEXT NOT NULL DEFAULT '08:30',
          last_reminded_on TEXT NULL,
          updated_at TEXT DEFAULT (strftime('%Y-%m-%d %H:%M:%f', 'now')),
          FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        );
      `);
      db.exec(`
        CREATE INDEX IF NOT EXISTS idx_user_prefs_freq_time
        ON user_preferences (reminder_freq, reminder_time);
      `);
    }
  },
  {
    version: 5,
    name: 'group-membership',
    checksum: 'v5-group-membership-2025-09-07',
    up: (db: Database) => {
      db.exec(`PRAGMA foreign_keys = ON;`);
      db.exec(`
        CREATE TABLE IF NOT EXISTS group_members (
          group_id TEXT NOT NULL,
          user_id TEXT NOT NULL,
          is_admin BOOLEAN NOT NULL DEFAULT 0,
          is_active BOOLEAN NOT NULL DEFAULT 1,
          first_seen_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%f', 'now')),
          last_seen_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%f', 'now')),
          last_role_change_at TEXT NULL,
          PRIMARY KEY (group_id, user_id),
          FOREIGN KEY (group_id) REFERENCES groups(id) ON DELETE CASCADE,
          FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        );
      `);
      db.exec(`
        CREATE INDEX IF NOT EXISTS idx_group_members_group_active
        ON group_members (group_id, is_active);
      `);
      db.exec(`
        CREATE INDEX IF NOT EXISTS idx_group_members_user_active
        ON group_members (user_id, is_active);
      `);
    }
  },
  {
    version: 6,
    name: 'user-aliases',
    checksum: 'v6-user-aliases-2025-09-20',
    up: (db: Database) => {
      db.exec(`
        CREATE TABLE IF NOT EXISTS user_aliases (
          alias TEXT PRIMARY KEY,
          user_id TEXT NOT NULL,
          source TEXT NULL,
          created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%f', 'now')),
          updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%f', 'now')),
          FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        );
      `);
      db.exec(`
        CREATE INDEX IF NOT EXISTS idx_user_aliases_user_id
        ON user_aliases (user_id);
      `);
    }
  },
  {
    version: 7,
    name: 'user-preferences-weekdays',
    checksum: 'v7-user-preferences-weekdays-2025-09-20',
    up: (db: Database) => {
      // Re-crear tabla user_preferences para ampliar CHECK con 'weekdays'
      try { db.exec(`PRAGMA foreign_keys = OFF;`); } catch {}

      db.exec(`
        CREATE TABLE IF NOT EXISTS user_preferences_new (
          user_id TEXT PRIMARY KEY,
          reminder_freq TEXT NOT NULL CHECK (reminder_freq IN ('off','daily','weekly','weekdays')),
          reminder_time TEXT NOT NULL DEFAULT '08:30',
          last_reminded_on TEXT NULL,
          updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%f', 'now')),
          FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        );
      `);

      // Copiar datos existentes si la tabla anterior existe
      try {
        db.exec(`
          INSERT INTO user_preferences_new (user_id, reminder_freq, reminder_time, last_reminded_on, updated_at)
          SELECT user_id,
                 CASE WHEN reminder_freq IN ('off','daily','weekly') THEN reminder_freq ELSE 'off' END,
                 COALESCE(reminder_time, '08:30'),
                 last_reminded_on,
                 COALESCE(updated_at, strftime('%Y-%m-%d %H:%M:%f', 'now'))
          FROM user_preferences;
        `);
        db.exec(`DROP TABLE user_preferences;`);
      } catch {
        // nada que copiar
      }

      db.exec(`ALTER TABLE user_preferences_new RENAME TO user_preferences;`);
      db.exec(`
        CREATE INDEX IF NOT EXISTS idx_user_prefs_freq_time
        ON user_preferences (reminder_freq, reminder_time);
      `);

      try { db.exec(`PRAGMA foreign_keys = ON;`); } catch {}
    }
  },
  {
    version: 8,
    name: 'tasks-display-code-global',
    checksum: 'v8-tasks-display-code-2025-09-21',
    up: (db: Database) => {
      // Añadir columna display_code si no existe
      try {
        const cols = db.query(`PRAGMA table_info(tasks)`).all() as Array<{ name?: string }>;
        const hasDisplay = Array.isArray(cols) && cols.some((c: any) => String(c.name) === 'display_code');
        if (!hasDisplay) {
          db.exec(`ALTER TABLE tasks ADD COLUMN display_code INTEGER NULL;`);
        }
      } catch {}

      // Índice único parcial: códigos únicos solo en tareas activas (no completadas)
      try {
        db.exec(`
          CREATE UNIQUE INDEX IF NOT EXISTS uq_tasks_display_code_active
          ON tasks (display_code)
          WHERE display_code IS NOT NULL AND COALESCE(completed, 0) = 0;
        `);
      } catch {}
    }
  }
  ,
  {
    version: 9,
    name: 'allowed-groups',
    checksum: 'v9-allowed-groups-2025-09-29',
    up: (db: Database) => {
      db.exec(`
        CREATE TABLE IF NOT EXISTS allowed_groups (
          group_id TEXT PRIMARY KEY,
          label TEXT NULL,
          status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','allowed','blocked')),
          discovered_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%f','now')),
          updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%f','now')),
          discovered_by TEXT NULL
        );
      `);
      db.exec(`
        CREATE INDEX IF NOT EXISTS idx_allowed_groups_status
        ON allowed_groups (status);
      `);
    }
  },
  {
    version: 10,
    name: 'web-auth-tables',
    checksum: 'v10-web-auth-2025-10-12',
    up: (db: Database) => {
      db.exec(`PRAGMA foreign_keys = ON;`);

      // Tokens de login web (válidos 10 min, de un solo uso)
      db.exec(`
        CREATE TABLE IF NOT EXISTS web_tokens (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          user_id TEXT NOT NULL,
          token_hash TEXT NOT NULL UNIQUE,
          created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%f','now')),
          expires_at TEXT NOT NULL,
          used_at TEXT NULL,
          metadata TEXT NULL,
          FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        );
      `);
      db.exec(`CREATE INDEX IF NOT EXISTS idx_web_tokens_user ON web_tokens (user_id);`);
      db.exec(`CREATE INDEX IF NOT EXISTS idx_web_tokens_expires ON web_tokens (expires_at);`);

      // Sesiones de la web (idle timeout gestionado por la app web)
      db.exec(`
        CREATE TABLE IF NOT EXISTS web_sessions (
          id TEXT PRIMARY KEY,
          user_id TEXT NOT NULL,
          session_hash TEXT NOT NULL UNIQUE,
          created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%f','now')),
          last_seen_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%f','now')),
          expires_at TEXT NOT NULL,
          user_agent TEXT NULL,
          ip TEXT NULL,
          FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        );
      `);
      db.exec(`CREATE INDEX IF NOT EXISTS idx_web_sessions_user ON web_sessions (user_id);`);
      db.exec(`CREATE INDEX IF NOT EXISTS idx_web_sessions_expires ON web_sessions (expires_at);`);
    }
  },
  {
    version: 11,
    name: 'calendar-tokens',
    checksum: 'v11-calendar-tokens-2025-10-14',
    up: (db: Database) => {
      db.exec(`PRAGMA foreign_keys = ON;`);
      db.exec(`
        CREATE TABLE IF NOT EXISTS calendar_tokens (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          type TEXT NOT NULL CHECK (type IN ('personal','group','aggregate')),
          user_id TEXT NOT NULL,
          group_id TEXT NULL,
          token_hash TEXT NOT NULL UNIQUE,
          created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%f','now')),
          revoked_at TEXT NULL,
          last_used_at TEXT NULL,
          FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
          FOREIGN KEY (group_id) REFERENCES groups(id) ON DELETE CASCADE
        );
      `);
      db.exec(`
        CREATE UNIQUE INDEX IF NOT EXISTS uq_calendar_tokens_active
        ON calendar_tokens (type, user_id, group_id)
        WHERE revoked_at IS NULL;
      `);
      db.exec(`CREATE INDEX IF NOT EXISTS idx_calendar_tokens_user ON calendar_tokens (user_id);`);
      db.exec(`CREATE INDEX IF NOT EXISTS idx_calendar_tokens_group ON calendar_tokens (group_id);`);
      db.exec(`CREATE INDEX IF NOT EXISTS idx_calendar_tokens_type ON calendar_tokens (type);`);
    }
  },
  {
    version: 12,
    name: 'calendar-tokens-plain',
    checksum: 'v12-calendar-tokens-plain-2025-10-14',
    up: (db: Database) => {
      // Añadir columna para poder mostrar siempre la URL (guardando el token en claro).
      // Nota: mantenemos token_hash para validación; token_plain se usa solo para construir la URL en UI.
      try {
        const cols = db.query(`PRAGMA table_info(calendar_tokens)`).all() as Array<{ name?: string }>;
        const hasPlain = Array.isArray(cols) && cols.some((c: any) => String(c.name) === 'token_plain');
        if (!hasPlain) {
          db.exec(`ALTER TABLE calendar_tokens ADD COLUMN token_plain TEXT NULL;`);
        }
      } catch {}
    }
  },
  {
    version: 13,
    name: 'groups-onboarding-prompted-at',
    checksum: 'v13-groups-onboarding-2025-10-17',
    up: (db: Database) => {
      try {
        const cols = db.query(`PRAGMA table_info(groups)`).all() as Array<{ name?: string }>;
        const hasCol = Array.isArray(cols) && cols.some((c: any) => String(c.name) === 'onboarding_prompted_at');
        if (!hasCol) {
          db.exec(`ALTER TABLE groups ADD COLUMN onboarding_prompted_at TEXT NULL;`);
        }
      } catch {}
    }
  },
  {
    version: 14,
    name: 'groups-archived-flag',
    checksum: 'v14-groups-archived-2025-10-19',
    up: (db: Database) => {
      try {
        const cols = db.query(`PRAGMA table_info(groups)`).all() as Array<{ name?: string }>;
        const hasArchived = Array.isArray(cols) && cols.some((c: any) => String(c.name) === 'archived');
        if (!hasArchived) {
          db.exec(`ALTER TABLE groups ADD COLUMN archived BOOLEAN NOT NULL DEFAULT 0;`);
        }
      } catch {}
    }
  }
  ,
  {
    version: 15,
    name: 'tasks-personal-unassign-guard',
    checksum: 'v15-personal-unassign-2025-10-19',
    up: (db: Database) => {
      db.exec(`PRAGMA foreign_keys = ON;`);

      // Reparar: reasignar tareas personales abiertas sin asignatarios a created_by
      db.exec(`
        INSERT INTO task_assignments (task_id, user_id, assigned_by, assigned_at)
        SELECT t.id, t.created_by, t.created_by, strftime('%Y-%m-%d %H:%M:%f','now')
        FROM tasks t
        WHERE t.group_id IS NULL
          AND COALESCE(t.completed,0) = 0
          AND NOT EXISTS (SELECT 1 FROM task_assignments a WHERE a.task_id = t.id);
      `);

      // Trigger: impedir borrar el último asignatario en tareas personales
      db.exec(`
        CREATE TRIGGER IF NOT EXISTS trg_block_unassign_last_personal
        BEFORE DELETE ON task_assignments
        FOR EACH ROW
        WHEN EXISTS (SELECT 1 FROM tasks WHERE id = OLD.task_id)
             AND EXISTS (SELECT 1 FROM users WHERE id = OLD.user_id)
             AND (SELECT group_id FROM tasks WHERE id = OLD.task_id) IS NULL
             AND (SELECT COUNT(*) FROM task_assignments WHERE task_id = OLD.task_id) = 1
        BEGIN
          SELECT RAISE(ABORT, 'PERSONAL_UNASSIGN_FORBIDDEN');
        END;
      `);
    }
  },
  {
    version: 16,
    name: 'groups-is-community',
    checksum: 'v16-groups-is-community-2025-10-19',
    up: (db: Database) => {
      try {
        const cols = db.query(`PRAGMA table_info(groups)`).all() as Array<{ name?: string }>;
        const hasCol = Array.isArray(cols) && cols.some((c: any) => String(c.name) === 'is_community');
        if (!hasCol) {
          db.exec(`ALTER TABLE groups ADD COLUMN is_community BOOLEAN NOT NULL DEFAULT 0;`);
        }
      } catch {}
      try {
        db.exec(`CREATE INDEX IF NOT EXISTS idx_groups_is_community ON groups (is_community);`);
      } catch {}
    }
  },
  {
    version: 17,
    name: 'task-origins',
    checksum: 'v17-task-origins-2025-10-20',
    up: (db: Database) => {
      db.exec(`PRAGMA foreign_keys = ON;`);
      db.exec(`
        CREATE TABLE IF NOT EXISTS task_origins (
          task_id INTEGER PRIMARY KEY,
          chat_id TEXT NOT NULL,
          message_id TEXT NOT NULL,
          created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%f','now')),
          FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE
        );
      `);
      db.exec(`CREATE INDEX IF NOT EXISTS idx_task_origins_task ON task_origins (task_id);`);
      db.exec(`CREATE INDEX IF NOT EXISTS idx_task_origins_chat_msg ON task_origins (chat_id, message_id);`);
    }
  },
  {
    version: 18,
    name: 'task-origins-participant-fromme',
    checksum: 'v18-task-origins-participant-fromme-2025-10-21',
    up: (db: Database) => {
      try {
        const cols = db.query(`PRAGMA table_info(task_origins)`).all() as Array<{ name?: string }>;
        const hasParticipant = Array.isArray(cols) && cols.some((c: any) => String(c.name) === 'participant');
        if (!hasParticipant) {
          db.exec(`ALTER TABLE task_origins ADD COLUMN participant TEXT NULL;`);
        }
        const hasFromMe = Array.isArray(cols) && cols.some((c: any) => String(c.name) === 'from_me');
        if (!hasFromMe) {
          db.exec(`ALTER TABLE task_origins ADD COLUMN from_me INTEGER NULL;`);
        }
      } catch {}
    }
  },
  {
    version: 19,
    name: 'users-last-command-at',
    checksum: 'v19-users-last-command-at-2025-10-25',
    up: (db: Database) => {
      try {
        const cols = db.query(`PRAGMA table_info(users)`).all() as Array<{ name?: string }>;
        const hasCol = Array.isArray(cols) && cols.some((c: any) => String(c.name) === 'last_command_at');
        if (!hasCol) {
          db.exec(`ALTER TABLE users ADD COLUMN last_command_at TEXT NULL;`);
        }
      } catch {}
    }
  }
];
