-- ATCR AppView Database Schema
-- This file contains the complete base schema for fresh database installations.
-- Migrations (in migrations/*.yaml) handle changes to existing databases.

CREATE TABLE IF NOT EXISTS schema_migrations (
    version INTEGER PRIMARY KEY,
    applied_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS users (
    did TEXT PRIMARY KEY,
    handle TEXT NOT NULL,
    pds_endpoint TEXT NOT NULL,
    avatar TEXT,
    last_seen TIMESTAMP NOT NULL,
    UNIQUE(handle)
);
CREATE INDEX IF NOT EXISTS idx_users_handle ON users(handle);

CREATE TABLE IF NOT EXISTS manifests (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    did TEXT NOT NULL,
    repository TEXT NOT NULL,
    digest TEXT NOT NULL,
    hold_endpoint TEXT NOT NULL,  -- Stored as DID (e.g., did:web:hold.example.com)
    schema_version INTEGER NOT NULL,
    media_type TEXT NOT NULL,
    config_digest TEXT,
    config_size INTEGER,
    artifact_type TEXT NOT NULL DEFAULT 'container-image',  -- container-image, helm-chart, unknown
    created_at TIMESTAMP NOT NULL,
    UNIQUE(did, repository, digest),
    FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_manifests_did_repo ON manifests(did, repository);
CREATE INDEX IF NOT EXISTS idx_manifests_created_at ON manifests(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_manifests_digest ON manifests(digest);
CREATE INDEX IF NOT EXISTS idx_manifests_artifact_type ON manifests(artifact_type);

CREATE TABLE IF NOT EXISTS repository_annotations (
    did TEXT NOT NULL,
    repository TEXT NOT NULL,
    key TEXT NOT NULL,
    value TEXT NOT NULL,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(did, repository, key),
    FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_repository_annotations_did_repo ON repository_annotations(did, repository);
CREATE INDEX IF NOT EXISTS idx_repository_annotations_key ON repository_annotations(key);

CREATE TABLE IF NOT EXISTS layers (
    manifest_id INTEGER NOT NULL,
    digest TEXT NOT NULL,
    size INTEGER NOT NULL,
    media_type TEXT NOT NULL,
    layer_index INTEGER NOT NULL,
    PRIMARY KEY(manifest_id, layer_index),
    FOREIGN KEY(manifest_id) REFERENCES manifests(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_layers_digest ON layers(digest);

CREATE TABLE IF NOT EXISTS manifest_references (
    manifest_id INTEGER NOT NULL,
    digest TEXT NOT NULL,
    media_type TEXT NOT NULL,
    size INTEGER NOT NULL,
    platform_architecture TEXT,
    platform_os TEXT,
    platform_variant TEXT,
    platform_os_version TEXT,
    is_attestation BOOLEAN DEFAULT FALSE,
    reference_index INTEGER NOT NULL,
    PRIMARY KEY(manifest_id, reference_index),
    FOREIGN KEY(manifest_id) REFERENCES manifests(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_manifest_references_digest ON manifest_references(digest);

CREATE TABLE IF NOT EXISTS tags (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    did TEXT NOT NULL,
    repository TEXT NOT NULL,
    tag TEXT NOT NULL,
    digest TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL,
    UNIQUE(did, repository, tag),
    FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_tags_did_repo ON tags(did, repository);

CREATE TABLE IF NOT EXISTS oauth_sessions (
    session_key TEXT PRIMARY KEY,
    account_did TEXT NOT NULL,
    session_id TEXT NOT NULL,
    session_data TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(account_did, session_id)
);
CREATE INDEX IF NOT EXISTS idx_oauth_sessions_did ON oauth_sessions(account_did);
CREATE INDEX IF NOT EXISTS idx_oauth_sessions_updated ON oauth_sessions(updated_at DESC);

CREATE TABLE IF NOT EXISTS oauth_auth_requests (
    state TEXT PRIMARY KEY,
    request_data TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_oauth_auth_requests_created ON oauth_auth_requests(created_at);

CREATE TABLE IF NOT EXISTS ui_sessions (
    id TEXT PRIMARY KEY,
    did TEXT NOT NULL,
    handle TEXT NOT NULL,
    pds_endpoint TEXT NOT NULL,
    oauth_session_id TEXT,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_ui_sessions_did ON ui_sessions(did);
CREATE INDEX IF NOT EXISTS idx_ui_sessions_expires ON ui_sessions(expires_at);

CREATE TABLE IF NOT EXISTS devices (
    id TEXT PRIMARY KEY,
    did TEXT NOT NULL,
    handle TEXT NOT NULL,
    name TEXT NOT NULL,
    secret_hash TEXT NOT NULL UNIQUE,
    ip_address TEXT,
    location TEXT,
    user_agent TEXT,
    created_at TIMESTAMP NOT NULL,
    last_used TIMESTAMP,
    FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_devices_did ON devices(did);
CREATE INDEX IF NOT EXISTS idx_devices_hash ON devices(secret_hash);

CREATE TABLE IF NOT EXISTS pending_device_auth (
    device_code TEXT PRIMARY KEY,
    user_code TEXT NOT NULL UNIQUE,
    device_name TEXT NOT NULL,
    ip_address TEXT,
    user_agent TEXT,
    expires_at TIMESTAMP NOT NULL,
    approved_did TEXT,
    approved_at TIMESTAMP,
    device_secret TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_pending_device_auth_user_code ON pending_device_auth(user_code);
CREATE INDEX IF NOT EXISTS idx_pending_device_auth_expires ON pending_device_auth(expires_at);

CREATE TABLE IF NOT EXISTS repository_stats (
    did TEXT NOT NULL,
    repository TEXT NOT NULL,
    pull_count INTEGER NOT NULL DEFAULT 0,
    last_pull TIMESTAMP,
    push_count INTEGER NOT NULL DEFAULT 0,
    last_push TIMESTAMP,
    PRIMARY KEY(did, repository),
    FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_repository_stats_did ON repository_stats(did);
CREATE INDEX IF NOT EXISTS idx_repository_stats_pull_count ON repository_stats(pull_count DESC);

CREATE TABLE IF NOT EXISTS stars (
    starrer_did TEXT NOT NULL,
    owner_did TEXT NOT NULL,
    repository TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL,
    PRIMARY KEY(starrer_did, owner_did, repository),
    FOREIGN KEY(starrer_did) REFERENCES users(did) ON DELETE CASCADE,
    FOREIGN KEY(owner_did) REFERENCES users(did) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_stars_owner_repo ON stars(owner_did, repository);
CREATE INDEX IF NOT EXISTS idx_stars_starrer ON stars(starrer_did);

CREATE TABLE IF NOT EXISTS hold_captain_records (
    hold_did TEXT PRIMARY KEY,
    owner_did TEXT NOT NULL,
    public BOOLEAN NOT NULL,
    allow_all_crew BOOLEAN NOT NULL,
    deployed_at TEXT,
    region TEXT,
    provider TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_hold_captain_updated ON hold_captain_records(updated_at);

CREATE TABLE IF NOT EXISTS hold_crew_approvals (
    hold_did TEXT NOT NULL,
    user_did TEXT NOT NULL,
    approved_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL,
    PRIMARY KEY(hold_did, user_did)
);
CREATE INDEX IF NOT EXISTS idx_crew_approvals_expires ON hold_crew_approvals(expires_at);

CREATE TABLE IF NOT EXISTS hold_crew_denials (
    hold_did TEXT NOT NULL,
    user_did TEXT NOT NULL,
    denial_count INTEGER NOT NULL DEFAULT 1,
    next_retry_at TIMESTAMP NOT NULL,
    last_denied_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(hold_did, user_did)
);
CREATE INDEX IF NOT EXISTS idx_crew_denials_retry ON hold_crew_denials(next_retry_at);

CREATE TABLE IF NOT EXISTS repo_pages (
    did TEXT NOT NULL,
    repository TEXT NOT NULL,
    description TEXT,
    avatar_cid TEXT,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    PRIMARY KEY(did, repository),
    FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_repo_pages_did ON repo_pages(did);