-- ==============================================================================
-- THECR3W-DEV — POSTGRESQL INITIAL DATABASE SCHEMA
-- Version: 1.0.0
-- Dialect: PostgreSQL 16+
-- ==============================================================================

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- ------------------------------------------------------------------------------
-- 1. SECURITY, AUDIT & KEY METADATA
-- ------------------------------------------------------------------------------

CREATE TABLE signing_keys_metadata (
    key_id VARCHAR(64) PRIMARY KEY,
    algorithm VARCHAR(32) NOT NULL DEFAULT 'Ed25519',
    public_key_hex VARCHAR(128) NOT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'rotated', 'revoked')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMPTZ,
    revoked_at TIMESTAMPTZ,
    revocation_reason TEXT
);

CREATE TABLE audit_events (
    event_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    actor VARCHAR(255) NOT NULL,
    action VARCHAR(128) NOT NULL,
    resource_type VARCHAR(128) NOT NULL,
    resource_id VARCHAR(255) NOT NULL,
    correlation_id UUID NOT NULL,
    ip_address VARCHAR(64),
    user_agent TEXT,
    success BOOLEAN NOT NULL DEFAULT TRUE,
    error_message TEXT,
    details_json JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_audit_events_resource ON audit_events (resource_type, resource_id);
CREATE INDEX idx_audit_events_created_at ON audit_events (created_at DESC);
CREATE INDEX idx_audit_events_actor ON audit_events (actor);
CREATE INDEX idx_audit_events_correlation ON audit_events (correlation_id);

-- ------------------------------------------------------------------------------
-- 2. NATIVE UPDATES (ANDROID & WINDOWS)
-- ------------------------------------------------------------------------------

CREATE TABLE applications (
    app_id VARCHAR(128) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    icon_url TEXT,
    is_public BOOLEAN NOT NULL DEFAULT TRUE,
    default_channel VARCHAR(32) NOT NULL DEFAULT 'stable' CHECK (default_channel IN ('stable', 'beta', 'nightly')),
    system_requirements TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE application_platforms (
    app_id VARCHAR(128) REFERENCES applications(app_id) ON DELETE CASCADE,
    platform VARCHAR(32) NOT NULL CHECK (platform IN ('android', 'windows')),
    package_identifier VARCHAR(255) NOT NULL, -- e.g. com.thecr3w.app or Windows App ID
    signing_certificate_fingerprint VARCHAR(128), -- SHA256 of APK signing cert or Windows Authenticode Thumbprint
    PRIMARY KEY (app_id, platform)
);

CREATE TABLE releases (
    release_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    app_id VARCHAR(128) NOT NULL REFERENCES applications(app_id) ON DELETE RESTRICT,
    version VARCHAR(64) NOT NULL,
    version_code INTEGER NOT NULL,
    platform VARCHAR(32) NOT NULL CHECK (platform IN ('android', 'windows')),
    architecture VARCHAR(32) NOT NULL CHECK (architecture IN ('arm64-v8a', 'armeabi-v7a', 'x86_64', 'x86', 'arm64', 'universal')),
    channel VARCHAR(32) NOT NULL CHECK (channel IN ('stable', 'beta', 'nightly')),
    status VARCHAR(32) NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'revoked')),
    mandatory BOOLEAN NOT NULL DEFAULT FALSE,
    minimum_supported_version VARCHAR(64),
    release_notes TEXT,
    file_size_bytes BIGINT NOT NULL,
    sha256 VARCHAR(64) NOT NULL,
    r2_object_key VARCHAR(512) NOT NULL,
    signature_ed25519 VARCHAR(128),
    signing_key_id VARCHAR(64) REFERENCES signing_keys_metadata(key_id),
    published_at TIMESTAMPTZ,
    revoked_at TIMESTAMPTZ,
    revocation_reason TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(255) NOT NULL,
    CONSTRAINT uq_app_platform_arch_channel_version UNIQUE (app_id, platform, architecture, channel, version_code)
);

CREATE INDEX idx_releases_check ON releases (app_id, platform, architecture, channel, status, version_code DESC);
CREATE INDEX idx_releases_published_at ON releases (published_at DESC);

CREATE TABLE installation_reports (
    report_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    release_id UUID NOT NULL REFERENCES releases(release_id) ON DELETE CASCADE,
    previous_version VARCHAR(64),
    installed_version VARCHAR(64) NOT NULL,
    status VARCHAR(32) NOT NULL CHECK (status IN ('success', 'failed', 'rolled_back')),
    duration_seconds INTEGER,
    os_version VARCHAR(64),
    device_model VARCHAR(128),
    error_details TEXT,
    reported_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_install_reports_release ON installation_reports (release_id, status);

CREATE TABLE error_reports (
    error_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    release_id UUID REFERENCES releases(release_id) ON DELETE SET NULL,
    app_id VARCHAR(128) NOT NULL REFERENCES applications(app_id) ON DELETE CASCADE,
    error_code VARCHAR(64) NOT NULL,
    error_message TEXT NOT NULL,
    stack_trace TEXT,
    client_platform VARCHAR(32),
    reported_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_error_reports_app ON error_reports (app_id, reported_at DESC);

-- ------------------------------------------------------------------------------
-- 3. DOCKER DEPLOYMENTS
-- ------------------------------------------------------------------------------

CREATE TABLE deployment_projects (
    project_id VARCHAR(64) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    forgejo_repo_url TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE deployment_environments (
    environment_id VARCHAR(32) PRIMARY KEY CHECK (environment_id IN ('development', 'staging', 'production')),
    requires_approval BOOLEAN NOT NULL DEFAULT TRUE,
    min_approvers INTEGER NOT NULL DEFAULT 1
);

INSERT INTO deployment_environments (environment_id, requires_approval, min_approvers)
VALUES 
    ('development', FALSE, 0),
    ('staging', FALSE, 0),
    ('production', TRUE, 1)
ON CONFLICT (environment_id) DO NOTHING;

CREATE TABLE deployment_manifests (
    manifest_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id VARCHAR(64) NOT NULL REFERENCES deployment_projects(project_id) ON DELETE RESTRICT,
    environment_id VARCHAR(32) NOT NULL REFERENCES deployment_environments(environment_id),
    service_name VARCHAR(128) NOT NULL,
    image VARCHAR(255) NOT NULL,
    immutable_digest VARCHAR(128) NOT NULL, -- e.g. sha256:...
    manifest_sha256 VARCHAR(64) NOT NULL,
    signature_ed25519 VARCHAR(128) NOT NULL,
    signing_key_id VARCHAR(64) REFERENCES signing_keys_metadata(key_id),
    config_hash VARCHAR(64) NOT NULL,
    secrets_references JSONB NOT NULL DEFAULT '[]'::jsonb,
    required_migrations JSONB NOT NULL DEFAULT '[]'::jsonb,
    healthcheck_config JSONB NOT NULL,
    rollback_strategy JSONB NOT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected', 'in_progress', 'completed', 'failed', 'rolled_back')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(255) NOT NULL
);

CREATE INDEX idx_deployment_manifests_status ON deployment_manifests (environment_id, status);

CREATE TABLE deployment_approvals (
    approval_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    manifest_id UUID NOT NULL REFERENCES deployment_manifests(manifest_id) ON DELETE CASCADE,
    manifest_sha256_verified VARCHAR(64) NOT NULL,
    approver VARCHAR(255) NOT NULL,
    decision VARCHAR(32) NOT NULL CHECK (decision IN ('approved', 'rejected')),
    comment TEXT,
    decided_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_manifest_approver UNIQUE (manifest_id, approver)
);

CREATE TABLE deployments (
    deployment_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    manifest_id UUID NOT NULL REFERENCES deployment_manifests(manifest_id),
    status VARCHAR(32) NOT NULL DEFAULT 'queued' CHECK (status IN ('queued', 'pulling', 'starting', 'migrating', 'healthcheck', 'healthy', 'failed', 'rolling_back', 'rolled_back')),
    started_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMPTZ,
    deployed_digest VARCHAR(128) NOT NULL,
    previous_digest VARCHAR(128),
    error_summary TEXT
);

CREATE TABLE deployment_steps (
    step_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    deployment_id UUID NOT NULL REFERENCES deployments(deployment_id) ON DELETE CASCADE,
    step_name VARCHAR(64) NOT NULL,
    step_order INTEGER NOT NULL,
    status VARCHAR(32) NOT NULL CHECK (status IN ('pending', 'running', 'success', 'failed')),
    details TEXT,
    started_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    finished_at TIMESTAMPTZ
);

CREATE INDEX idx_deployment_steps_dep ON deployment_steps (deployment_id, step_order);

-- ------------------------------------------------------------------------------
-- 4. EXTERNAL DEPLOYMENTS (SFTP / FTPS)
-- ------------------------------------------------------------------------------

CREATE TABLE external_servers (
    server_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    protocol VARCHAR(16) NOT NULL CHECK (protocol IN ('sftp', 'ftps', 'ftp')),
    host VARCHAR(255) NOT NULL,
    port INTEGER NOT NULL,
    remote_root_path VARCHAR(512) NOT NULL,
    host_key_fingerprint VARCHAR(255), -- Strict pinning for SFTP
    tls_certificate_thumbprint VARCHAR(128), -- Strict pinning for FTPS
    status VARCHAR(32) NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'disabled', 'unreachable')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE sync_operations (
    sync_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    server_id UUID NOT NULL REFERENCES external_servers(server_id) ON DELETE RESTRICT,
    initiated_by VARCHAR(255) NOT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'preview' CHECK (status IN ('preview', 'syncing', 'completed', 'failed', 'rolled_back')),
    files_added INTEGER NOT NULL DEFAULT 0,
    files_updated INTEGER NOT NULL DEFAULT 0,
    files_deleted INTEGER NOT NULL DEFAULT 0,
    bytes_transferred BIGINT NOT NULL DEFAULT 0,
    manifest_hash VARCHAR(64) NOT NULL,
    started_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMPTZ,
    error_message TEXT
);

-- ------------------------------------------------------------------------------
-- 5. STORAGE & UPLOAD SESSIONS (CLOUDFLARE R2)
-- ------------------------------------------------------------------------------

CREATE TABLE upload_sessions (
    session_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    app_id VARCHAR(128) NOT NULL REFERENCES applications(app_id),
    r2_object_key VARCHAR(512) NOT NULL,
    filename VARCHAR(255) NOT NULL,
    content_type VARCHAR(128) NOT NULL,
    expected_size_bytes BIGINT NOT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'completed', 'expired')),
    expires_at TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(255) NOT NULL
);

CREATE INDEX idx_upload_sessions_status ON upload_sessions (status, expires_at);