CREATE TABLE admin_users (
    id INTEGER PRIMARY KEY CHECK (id = 1),
    username TEXT NOT NULL UNIQUE CHECK (length(username) BETWEEN 2 AND 40),
    password_hash TEXT NOT NULL,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    last_login_at TEXT
);

CREATE TABLE auth_sessions (
    token_hash TEXT PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES admin_users(id) ON DELETE CASCADE,
    csrf_hash TEXT NOT NULL,
    expires_at TEXT NOT NULL,
    created_at TEXT NOT NULL,
    last_seen_at TEXT NOT NULL
);
CREATE INDEX idx_auth_sessions_expires_at ON auth_sessions(expires_at);

CREATE TABLE secrets (
    name TEXT PRIMARY KEY,
    key_version INTEGER NOT NULL DEFAULT 1 CHECK (key_version > 0),
    nonce TEXT NOT NULL,
    ciphertext TEXT NOT NULL,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

CREATE TABLE app_settings (
    id INTEGER PRIMARY KEY CHECK (id = 1),
    config_version INTEGER NOT NULL DEFAULT 1 CHECK (config_version > 0),
    aws_region TEXT NOT NULL DEFAULT '',
    lightsail_instance_name TEXT NOT NULL DEFAULT '',
    use_default_aws_credentials INTEGER NOT NULL DEFAULT 0
        CHECK (use_default_aws_credentials IN (0, 1)),
    cloudflare_zone_name TEXT NOT NULL DEFAULT '',
    cloudflare_zone_id TEXT NOT NULL DEFAULT '',
    cloudflare_record_name TEXT NOT NULL DEFAULT '',
    socks_port INTEGER NOT NULL DEFAULT 1080 CHECK (socks_port BETWEEN 1 AND 65535),
    proxy_health_check INTEGER NOT NULL DEFAULT 1 CHECK (proxy_health_check IN (0, 1)),
    health_timeout_seconds INTEGER NOT NULL DEFAULT 120
        CHECK (health_timeout_seconds BETWEEN 10 AND 600),
    external_controller_confirmed INTEGER NOT NULL DEFAULT 0
        CHECK (external_controller_confirmed IN (0, 1)),
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

INSERT INTO app_settings (
    id, created_at, updated_at
) VALUES (
    1, strftime('%Y-%m-%dT%H:%M:%fZ', 'now'), strftime('%Y-%m-%dT%H:%M:%fZ', 'now')
);

CREATE TABLE schedule_state (
    id INTEGER PRIMARY KEY CHECK (id = 1),
    enabled INTEGER NOT NULL DEFAULT 0 CHECK (enabled IN (0, 1)),
    interval_minutes INTEGER NOT NULL DEFAULT 60 CHECK (interval_minutes BETWEEN 5 AND 10080),
    next_run_at TEXT,
    last_run_at TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

INSERT INTO schedule_state (
    id, created_at, updated_at
) VALUES (
    1, strftime('%Y-%m-%dT%H:%M:%fZ', 'now'), strftime('%Y-%m-%dT%H:%M:%fZ', 'now')
);

CREATE TABLE rotation_runs (
    id TEXT PRIMARY KEY,
    trigger TEXT NOT NULL CHECK (trigger IN ('manual', 'scheduled', 'recovery')),
    status TEXT NOT NULL CHECK (
        status IN ('queued', 'running', 'needs_attention', 'succeeded', 'failed')
    ),
    stage TEXT NOT NULL,
    stage_started_at TEXT NOT NULL,
    active_slot INTEGER UNIQUE CHECK (active_slot IS NULL OR active_slot = 1),
    config_version INTEGER NOT NULL,
    old_ip TEXT,
    new_ip TEXT,
    dns_ip_before TEXT,
    dns_ip_after TEXT,
    attempt_count INTEGER NOT NULL DEFAULT 0,
    lease_owner TEXT,
    lease_until TEXT,
    error_code TEXT,
    error_message TEXT,
    started_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    finished_at TEXT
);
CREATE INDEX idx_rotation_runs_started_at ON rotation_runs(started_at DESC);
CREATE INDEX idx_rotation_runs_status ON rotation_runs(status, started_at DESC);

CREATE TABLE rotation_events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    run_id TEXT NOT NULL REFERENCES rotation_runs(id) ON DELETE CASCADE,
    occurred_at TEXT NOT NULL,
    stage TEXT NOT NULL,
    level TEXT NOT NULL CHECK (level IN ('info', 'warning', 'error')),
    message TEXT NOT NULL,
    details_json TEXT NOT NULL DEFAULT '{}'
);
CREATE INDEX idx_rotation_events_run_id ON rotation_events(run_id, id);

CREATE TABLE operation_locks (
    slot INTEGER PRIMARY KEY CHECK (slot = 1),
    kind TEXT NOT NULL CHECK (kind IN ('rotation', 'dns_sync')),
    owner_id TEXT NOT NULL UNIQUE,
    lease_until TEXT,
    created_at TEXT NOT NULL
);