-- =============================================================================
-- BeKind Backend — File Schema
-- Tables: files, file_variants
-- Depends on: 001-auth-schema.sql (users, update_updated_at_column)
-- =============================================================================

BEGIN;

-- =============================================================================
-- ENUMS
-- =============================================================================

DO $$ BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'enum_file_provider') THEN
        CREATE TYPE enum_file_provider AS ENUM ('local', 'minio', 's3');
    END IF;
END $$;

DO $$ BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'enum_file_status') THEN
        -- pending_upload → uploaded → scanning → processing → ready
        --                                       ↘ quarantined (malware detected)
        -- expired: TTL elapsed, awaiting deletion
        CREATE TYPE enum_file_status AS ENUM (
            'pending_upload',
            'uploaded',
            'scanning',
            'quarantined',
            'processing',
            'ready',
            'failed',
            'expired'
        );
    END IF;
END $$;

DO $$ BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'enum_file_visibility') THEN
        CREATE TYPE enum_file_visibility AS ENUM (
            'private',
            'tenant',
            'public'
        );
    END IF;
END $$;

-- =============================================================================
-- TABLE: files
-- =============================================================================

CREATE TABLE IF NOT EXISTS files (
    -- Primary
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Ownership
    tenant_id     UUID,
    owner_user_id UUID REFERENCES users(id) ON DELETE SET NULL,

    -- User-facing names
    original_name VARCHAR(255) NOT NULL,
    display_name  VARCHAR(255),
    title         VARCHAR(255),
    description   TEXT,
    note          TEXT,

    -- Storage: provider + bucket_name + object_key is the globally unique address
    provider    enum_file_provider NOT NULL DEFAULT 'local',
    bucket_name VARCHAR(100)       NOT NULL DEFAULT '',
    object_key  TEXT               NOT NULL,

    -- File metadata
    mime_type  VARCHAR(100) NOT NULL,
    extension  VARCHAR(20),
    category   VARCHAR(50),
    size_bytes BIGINT NOT NULL CHECK (size_bytes >= 0),

    -- Optional media metadata
    width        INTEGER,
    height       INTEGER,
    duration_sec INTEGER,
    page_count   INTEGER,

    -- Integrity
    checksum_sha256 VARCHAR(64),

    -- Access control
    visibility   enum_file_visibility NOT NULL DEFAULT 'private',
    public_token VARCHAR(100),
    is_library   BOOLEAN NOT NULL DEFAULT FALSE,

    -- Lifecycle
    status       enum_file_status NOT NULL DEFAULT 'pending_upload',
    last_error   TEXT,
    processed_at TIMESTAMPTZ,

    -- Soft delete (deleted_at NULL = active, non-NULL = deleted)
    deleted_at TIMESTAMPTZ,
    deleted_by UUID REFERENCES users(id) ON DELETE SET NULL,

    -- Audit
    created_by UUID REFERENCES users(id) ON DELETE SET NULL,
    updated_by UUID REFERENCES users(id) ON DELETE SET NULL,

    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Constraints
    CONSTRAINT uq_files_storage_location    UNIQUE (provider, bucket_name, object_key),
    CONSTRAINT uq_files_public_token        UNIQUE (public_token),
    CONSTRAINT uq_files_content_dedupe      UNIQUE (checksum_sha256, size_bytes, mime_type)
);

-- =============================================================================
-- TABLE: file_variants
-- Compressed / resized / converted derivatives of a file
-- e.g. thumbnail, mobile, webp, preview
-- =============================================================================

CREATE TABLE IF NOT EXISTS file_variants (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    file_id      UUID         NOT NULL REFERENCES files(id) ON DELETE CASCADE,
    variant_name VARCHAR(50)  NOT NULL,   -- thumbnail | mobile | webp | preview | pdf

    provider    enum_file_provider NOT NULL DEFAULT 'local',
    bucket_name VARCHAR(100)       NOT NULL DEFAULT '',
    object_key  TEXT               NOT NULL,

    mime_type  VARCHAR(100),
    size_bytes BIGINT CHECK (size_bytes >= 0),
    width      INTEGER,
    height     INTEGER,

    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT uq_file_variant             UNIQUE (file_id, variant_name),
    CONSTRAINT uq_variant_storage_location UNIQUE (provider, bucket_name, object_key)
);


-- =============================================================================
-- INDEXES
-- =============================================================================

-- Partial indexes on files (exclude soft-deleted rows to keep them small)
CREATE INDEX IF NOT EXISTS idx_files_owner_created
    ON files(owner_user_id, created_at DESC)
    WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_files_tenant_created
    ON files(tenant_id, created_at DESC)
    WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_files_status_created
    ON files(status, created_at ASC)
    WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_files_category
    ON files(category)
    WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_files_is_library
    ON files(is_library)
    WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_files_visibility
    ON files(visibility);

CREATE INDEX IF NOT EXISTS idx_files_checksum
    ON files(checksum_sha256);

-- Quota system: SUM(size_bytes) GROUP BY owner_user_id
CREATE INDEX IF NOT EXISTS idx_files_owner_size
    ON files(owner_user_id, size_bytes)
    WHERE deleted_at IS NULL;

-- file_variants: composite for lookup + ordering
CREATE INDEX IF NOT EXISTS idx_file_variants_file_created
    ON file_variants(file_id, created_at DESC);


-- =============================================================================
-- TRIGGER
-- =============================================================================

DROP TRIGGER IF EXISTS update_files_updated_at ON files;

CREATE TRIGGER update_files_updated_at
    BEFORE UPDATE ON files
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMIT;

