CREATE TABLE managed_instances (
    id TEXT PRIMARY KEY,
    display_name TEXT NOT NULL CHECK (length(trim(display_name)) BETWEEN 1 AND 80),
    aws_region TEXT NOT NULL CHECK (length(trim(aws_region)) BETWEEN 3 AND 32),
    lightsail_instance_name TEXT NOT NULL
        CHECK (length(trim(lightsail_instance_name)) BETWEEN 1 AND 255),
    cloudflare_zone_name TEXT NOT NULL
        CHECK (length(trim(cloudflare_zone_name)) BETWEEN 3 AND 253),
    cloudflare_zone_id TEXT NOT NULL DEFAULT '' CHECK (length(cloudflare_zone_id) <= 64),
    cloudflare_record_name TEXT NOT NULL
        CHECK (length(trim(cloudflare_record_name)) BETWEEN 3 AND 253),
    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),
    release_grace_seconds INTEGER NOT NULL DEFAULT 75
        CHECK (release_grace_seconds BETWEEN 60 AND 600),
    enabled INTEGER NOT NULL DEFAULT 1 CHECK (enabled IN (0, 1)),
    config_version INTEGER NOT NULL DEFAULT 1 CHECK (config_version > 0),
    last_known_ip TEXT,
    last_checked_at TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    archived_at TEXT
);

CREATE UNIQUE INDEX uq_managed_instances_display_name_active
ON managed_instances(display_name COLLATE NOCASE)
WHERE archived_at IS NULL;

CREATE UNIQUE INDEX uq_managed_instances_aws_target_active
ON managed_instances(aws_region, lightsail_instance_name)
WHERE archived_at IS NULL;

CREATE UNIQUE INDEX uq_managed_instances_dns_record_active
ON managed_instances(lower(cloudflare_record_name))
WHERE archived_at IS NULL;

CREATE INDEX idx_managed_instances_active
ON managed_instances(archived_at, enabled, display_name);

CREATE TABLE instance_groups (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL CHECK (length(trim(name)) BETWEEN 1 AND 80),
    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,
    config_version INTEGER NOT NULL DEFAULT 1 CHECK (config_version > 0),
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    archived_at TEXT
);

CREATE UNIQUE INDEX uq_instance_groups_name_active
ON instance_groups(name COLLATE NOCASE)
WHERE archived_at IS NULL;

CREATE INDEX idx_instance_groups_due
ON instance_groups(enabled, next_run_at)
WHERE archived_at IS NULL;

CREATE TABLE instance_group_members (
    group_id TEXT NOT NULL REFERENCES instance_groups(id) ON DELETE CASCADE,
    instance_id TEXT NOT NULL REFERENCES managed_instances(id) ON DELETE CASCADE,
    position INTEGER NOT NULL CHECK (position >= 0),
    PRIMARY KEY (group_id, instance_id),
    UNIQUE (instance_id)
);

CREATE INDEX idx_instance_group_members_order
ON instance_group_members(group_id, position, instance_id);

CREATE TABLE fleet_runs (
    id TEXT PRIMARY KEY,
    target_type TEXT NOT NULL CHECK (target_type IN ('instance', 'group')),
    target_id TEXT NOT NULL,
    target_name TEXT NOT NULL,
    trigger TEXT NOT NULL CHECK (trigger IN ('manual', 'scheduled', 'recovery')),
    status TEXT NOT NULL CHECK (
        status IN (
            'queued', 'running', 'needs_attention', 'cleanup_pending',
            'succeeded', 'failed', 'cancelled'
        )
    ),
    active_slot INTEGER UNIQUE CHECK (active_slot IS NULL OR active_slot = 1),
    current_item_id TEXT,
    lease_owner TEXT,
    lease_until TEXT,
    error_code TEXT,
    error_message TEXT,
    total_items INTEGER NOT NULL DEFAULT 0 CHECK (total_items >= 0),
    succeeded_items INTEGER NOT NULL DEFAULT 0
        CHECK (succeeded_items >= 0 AND succeeded_items <= total_items),
    started_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    finished_at TEXT
);

CREATE INDEX idx_fleet_runs_started_at ON fleet_runs(started_at DESC);
CREATE INDEX idx_fleet_runs_target ON fleet_runs(target_type, target_id, started_at DESC);
CREATE INDEX idx_fleet_runs_status ON fleet_runs(status, started_at DESC);

CREATE TABLE fleet_run_items (
    id TEXT PRIMARY KEY,
    run_id TEXT NOT NULL REFERENCES fleet_runs(id) ON DELETE CASCADE,
    instance_id TEXT NOT NULL REFERENCES managed_instances(id) ON DELETE RESTRICT,
    position INTEGER NOT NULL CHECK (position >= 0),
    status TEXT NOT NULL CHECK (
        status IN (
            'queued', 'running', 'needs_attention', 'cleanup_pending',
            'succeeded', 'failed', 'cancelled'
        )
    ),
    stage TEXT NOT NULL,
    stage_started_at TEXT NOT NULL,
    attempt_count INTEGER NOT NULL DEFAULT 0 CHECK (attempt_count >= 0),
    config_version INTEGER NOT NULL CHECK (config_version > 0),
    instance_display_name TEXT NOT NULL,
    aws_region TEXT NOT NULL,
    lightsail_instance_name TEXT NOT NULL,
    cloudflare_zone_name TEXT NOT NULL,
    cloudflare_zone_id TEXT NOT NULL DEFAULT '',
    cloudflare_record_name TEXT NOT NULL,
    socks_port INTEGER NOT NULL CHECK (socks_port BETWEEN 1 AND 65535),
    proxy_health_check INTEGER NOT NULL CHECK (proxy_health_check IN (0, 1)),
    health_timeout_seconds INTEGER NOT NULL
        CHECK (health_timeout_seconds BETWEEN 10 AND 600),
    release_grace_seconds INTEGER NOT NULL
        CHECK (release_grace_seconds BETWEEN 60 AND 600),
    old_static_ip_name TEXT,
    old_ip TEXT,
    new_static_ip_name TEXT,
    new_ip TEXT,
    dns_ip_before TEXT,
    dns_ip_after TEXT,
    grace_until TEXT,
    aws_operation_id TEXT,
    error_code TEXT,
    error_message TEXT,
    started_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    finished_at TEXT,
    UNIQUE (run_id, instance_id)
);

CREATE INDEX idx_fleet_run_items_run_order
ON fleet_run_items(run_id, position, id);
CREATE INDEX idx_fleet_run_items_instance
ON fleet_run_items(instance_id, started_at DESC);
CREATE INDEX idx_fleet_run_items_status
ON fleet_run_items(status, updated_at);

CREATE TABLE fleet_events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    run_id TEXT NOT NULL REFERENCES fleet_runs(id) ON DELETE CASCADE,
    item_id TEXT REFERENCES fleet_run_items(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_fleet_events_run ON fleet_events(run_id, id);
CREATE INDEX idx_fleet_events_item ON fleet_events(item_id, id);

CREATE TABLE fleet_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
);

INSERT INTO managed_instances (
    id, display_name, aws_region, lightsail_instance_name,
    cloudflare_zone_name, cloudflare_zone_id, cloudflare_record_name,
    socks_port, proxy_health_check, health_timeout_seconds,
    release_grace_seconds, enabled, config_version, created_at, updated_at
)
SELECT
    'legacy-instance', 'Legacy instance', aws_region, lightsail_instance_name,
    cloudflare_zone_name, cloudflare_zone_id, cloudflare_record_name,
    socks_port, proxy_health_check, health_timeout_seconds,
    75, 0, config_version, created_at, updated_at
FROM app_settings
WHERE id = 1
  AND trim(aws_region) <> ''
  AND trim(lightsail_instance_name) <> ''
  AND trim(cloudflare_zone_name) <> ''
  AND trim(cloudflare_record_name) <> '';

INSERT INTO instance_groups (
    id, name, enabled, interval_minutes, next_run_at, last_run_at,
    config_version, created_at, updated_at
)
SELECT
    'legacy-group', 'Legacy group', 0, schedule_state.interval_minutes,
    NULL, schedule_state.last_run_at, 1, schedule_state.created_at, schedule_state.updated_at
FROM schedule_state
WHERE schedule_state.id = 1
  AND EXISTS (SELECT 1 FROM managed_instances WHERE id = 'legacy-instance');

INSERT INTO instance_group_members(group_id, instance_id, position)
SELECT 'legacy-group', 'legacy-instance', 0
WHERE EXISTS (SELECT 1 FROM managed_instances WHERE id = 'legacy-instance')
  AND EXISTS (SELECT 1 FROM instance_groups WHERE id = 'legacy-group');

INSERT INTO rotation_events(
    run_id, occurred_at, stage, level, message, details_json
)
SELECT
    id,
    strftime('%Y-%m-%dT%H:%M:%fZ', 'now'),
    stage,
    'warning',
    '轮换工作流已升级，旧任务不能继续执行',
    '{"code":"WORKFLOW_UPGRADED"}'
FROM rotation_runs
WHERE active_slot = 1;

UPDATE rotation_runs
SET status = 'failed',
    stage = 'failed',
    active_slot = NULL,
    lease_owner = NULL,
    lease_until = NULL,
    error_code = 'WORKFLOW_UPGRADED',
    error_message = '轮换工作流已升级，请使用静态 IP 轮换新建任务',
    updated_at = strftime('%Y-%m-%dT%H:%M:%fZ', 'now'),
    finished_at = strftime('%Y-%m-%dT%H:%M:%fZ', 'now')
WHERE active_slot = 1;

DELETE FROM operation_locks;