flatrender/backend/db/migrations/13_file_manager.sql

-- =====================================================================
-- FILE_MGR SCHEMA — File Manager, Storage Quotas, Cleanup Scheduler
-- =====================================================================

SET search_path TO file_mgr, public;

CREATE TYPE file_kind          AS ENUM ('Video','Image','Audio','Voiceover','Document','Other');
CREATE TYPE folder_kind        AS ENUM ('System','User','Shared','Tenant');
CREATE TYPE upload_status      AS ENUM ('Pending','Uploading','Processing','Ready','Failed','Quarantined');
CREATE TYPE cleanup_entity_type AS ENUM ('Export','TempRenderFolder','OrphanedFile','UnusedUpload','SnapshotExpired');
CREATE TYPE cleanup_status     AS ENUM ('Scheduled','Notified','Processing','Done','Skipped','Failed');

-- ---------------------------------------------------------------------
-- user_folders — hierarchical folders
-- ---------------------------------------------------------------------
CREATE TABLE user_folders (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID NOT NULL,
    user_id             UUID NOT NULL,

    name                TEXT NOT NULL,
    folder_type         folder_kind NOT NULL DEFAULT 'User',
    parent_folder_id    UUID REFERENCES user_folders(id) ON DELETE CASCADE,

    -- Stats (denormalized for fast UI)
    file_count          INT NOT NULL DEFAULT 0,
    total_size_bytes    BIGINT NOT NULL DEFAULT 0,

    sort                INT NOT NULL DEFAULT 0,
    is_shared           BOOLEAN NOT NULL DEFAULT FALSE,
    share_token         TEXT,

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

CREATE INDEX idx_folders_user    ON user_folders(user_id, parent_folder_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_folders_parent  ON user_folders(parent_folder_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_folders_share   ON user_folders(share_token) WHERE share_token IS NOT NULL;

CREATE TRIGGER tg_folders_updated_at
BEFORE UPDATE ON user_folders FOR EACH ROW EXECUTE FUNCTION public.tg_set_updated_at();

-- ---------------------------------------------------------------------
-- user_files
-- ---------------------------------------------------------------------
CREATE TABLE user_files (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID NOT NULL,
    user_id             UUID NOT NULL,
    user_folder_id      UUID REFERENCES user_folders(id) ON DELETE SET NULL,

    -- Identity
    name                TEXT NOT NULL,
    original_filename   TEXT,
    file_extension      TEXT,
    mime_type           TEXT,
    file_type           file_kind NOT NULL,

    -- Storage
    minio_bucket        TEXT NOT NULL,
    minio_key           TEXT NOT NULL,
    cdn_url             TEXT,
    file_address        TEXT NOT NULL,                  -- canonical URL
    size_bytes          BIGINT NOT NULL,
    md5_hash            TEXT,
    sha256_hash         TEXT,

    -- Media metadata
    duration_sec        NUMERIC(8,2),
    width               INT,
    height              INT,
    fps                 NUMERIC(5,2),
    bitrate_kbps        INT,
    codec               TEXT,
    has_audio           BOOLEAN,
    has_video           BOOLEAN,

    -- Thumbnails
    thumbnail_url       TEXT,
    waveform_data       JSONB,                          -- for audio files

    -- Upload state
    upload_status       upload_status NOT NULL DEFAULT 'Ready',
    upload_id           TEXT,                           -- multipart upload ID if used
    upload_progress     INT NOT NULL DEFAULT 100,
    processing_error    TEXT,

    -- Source / linkage
    source              TEXT,                           -- 'upload','export','snapshot','voiceover_record','stock'
    export_id           UUID,                           -- references render.exports
    parent_file_id      UUID REFERENCES user_files(id) ON DELETE SET NULL,  -- derived files

    -- Lifecycle
    last_used_at        TIMESTAMPTZ,
    use_count           INT NOT NULL DEFAULT 0,

    -- Sharing
    is_public           BOOLEAN NOT NULL DEFAULT FALSE,
    share_token         TEXT,

    metadata            JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at          TIMESTAMPTZ
);

CREATE INDEX idx_files_user_folder  ON user_files(user_id, user_folder_id, created_at DESC) WHERE deleted_at IS NULL;
CREATE INDEX idx_files_tenant       ON user_files(tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_files_type         ON user_files(user_id, file_type) WHERE deleted_at IS NULL;
CREATE INDEX idx_files_hash         ON user_files(md5_hash) WHERE md5_hash IS NOT NULL;
CREATE INDEX idx_files_unused       ON user_files(last_used_at) WHERE deleted_at IS NULL;
CREATE INDEX idx_files_name_trgm    ON user_files USING gin (name gin_trgm_ops);
CREATE INDEX idx_files_share        ON user_files(share_token) WHERE share_token IS NOT NULL;

CREATE TRIGGER tg_files_updated_at
BEFORE UPDATE ON user_files FOR EACH ROW EXECUTE FUNCTION public.tg_set_updated_at();

-- ---------------------------------------------------------------------
-- storage_quotas — current usage per user
-- ---------------------------------------------------------------------
CREATE TABLE storage_quotas (
    user_id             UUID PRIMARY KEY,
    tenant_id           UUID NOT NULL,

    plan_quota_bytes    BIGINT NOT NULL DEFAULT 0,      -- from plan
    bonus_quota_bytes   BIGINT NOT NULL DEFAULT 0,      -- purchased extra
    used_bytes          BIGINT NOT NULL DEFAULT 0,

    -- Cached counts
    video_count         INT NOT NULL DEFAULT 0,
    image_count         INT NOT NULL DEFAULT 0,
    audio_count         INT NOT NULL DEFAULT 0,
    video_bytes         BIGINT NOT NULL DEFAULT 0,
    image_bytes         BIGINT NOT NULL DEFAULT 0,
    audio_bytes         BIGINT NOT NULL DEFAULT 0,

    -- Notifications
    last_90pct_notified_at TIMESTAMPTZ,
    last_100pct_notified_at TIMESTAMPTZ,

    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_quotas_tenant ON storage_quotas(tenant_id);

CREATE TRIGGER tg_quotas_updated_at
BEFORE UPDATE ON storage_quotas FOR EACH ROW EXECUTE FUNCTION public.tg_set_updated_at();

-- ---------------------------------------------------------------------
-- cleanup_schedules — track what's queued for auto-delete
-- ---------------------------------------------------------------------
CREATE TABLE cleanup_schedules (
    id                          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id                   UUID,
    user_id                     UUID,

    entity_type                 cleanup_entity_type NOT NULL,
    entity_id                   UUID NOT NULL,
    entity_path                 TEXT,                       -- filesystem path for temp folders

    scheduled_delete_at         TIMESTAMPTZ NOT NULL,
    notify_user_at              TIMESTAMPTZ,                -- send "expires in 3 days" notice
    user_notified               BOOLEAN NOT NULL DEFAULT FALSE,
    user_notified_at            TIMESTAMPTZ,

    status                      cleanup_status NOT NULL DEFAULT 'Scheduled',
    processed_at                TIMESTAMPTZ,
    processing_error            TEXT,
    bytes_freed                 BIGINT,

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

CREATE INDEX idx_cleanup_due        ON cleanup_schedules(scheduled_delete_at) WHERE status = 'Scheduled';
CREATE INDEX idx_cleanup_notify_due ON cleanup_schedules(notify_user_at) WHERE user_notified = FALSE AND notify_user_at IS NOT NULL;
CREATE INDEX idx_cleanup_entity     ON cleanup_schedules(entity_type, entity_id);

CREATE TRIGGER tg_cleanup_updated_at
BEFORE UPDATE ON cleanup_schedules FOR EACH ROW EXECUTE FUNCTION public.tg_set_updated_at();

-- ---------------------------------------------------------------------
-- upload_sessions — multipart / chunked upload tracking
-- ---------------------------------------------------------------------
CREATE TABLE upload_sessions (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID NOT NULL,
    user_id             UUID NOT NULL,

    minio_bucket        TEXT NOT NULL,
    minio_key           TEXT NOT NULL,
    minio_upload_id     TEXT NOT NULL,                  -- S3/MinIO multipart ID

    filename            TEXT NOT NULL,
    mime_type           TEXT,
    total_size_bytes    BIGINT NOT NULL,
    chunks_received     INT NOT NULL DEFAULT 0,
    bytes_received      BIGINT NOT NULL DEFAULT 0,
    chunk_size_bytes    INT NOT NULL DEFAULT 5242880,   -- 5MB default

    target_folder_id    UUID REFERENCES user_folders(id) ON DELETE SET NULL,
    target_file_id      UUID,                           -- created when complete

    status              upload_status NOT NULL DEFAULT 'Uploading',
    error_message       TEXT,

    expires_at          TIMESTAMPTZ NOT NULL DEFAULT NOW() + INTERVAL '24 hours',
    completed_at        TIMESTAMPTZ,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_uploads_user    ON upload_sessions(user_id, created_at DESC);
CREATE INDEX idx_uploads_expired ON upload_sessions(expires_at) WHERE status = 'Uploading';

CREATE TRIGGER tg_uploads_updated_at
BEFORE UPDATE ON upload_sessions FOR EACH ROW EXECUTE FUNCTION public.tg_set_updated_at();

-- ---------------------------------------------------------------------
-- minio_buckets — bucket registry (per region, per purpose)
-- ---------------------------------------------------------------------
CREATE TABLE minio_buckets (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name                TEXT NOT NULL UNIQUE,
    region              TEXT NOT NULL,
    endpoint            TEXT NOT NULL,
    purpose             TEXT NOT NULL,                  -- 'templates','user-uploads','exports','snapshots','voiceovers'
    is_public           BOOLEAN NOT NULL DEFAULT FALSE,
    cdn_base_url        TEXT,
    is_active           BOOLEAN NOT NULL DEFAULT TRUE,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_buckets_region_purpose ON minio_buckets(region, purpose) WHERE is_active = TRUE;