-- Migration: Export jobs table for user data exports
-- Version: 20260118000002
-- Description: Adds export_jobs table for tracking user data export requests

-- Export jobs table for tracking user data exports
CREATE TABLE export_jobs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    format TEXT NOT NULL CHECK (format IN ('json', 'csv')),
    status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'completed', 'failed')),
    include_settings BOOLEAN DEFAULT true,
    include_api_keys BOOLEAN DEFAULT true,
    include_usage_stats BOOLEAN DEFAULT false,
    file_path TEXT,
    file_size_bytes BIGINT,
    error_message TEXT,
    expires_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    completed_at TIMESTAMPTZ
);

-- Index for querying user's export history
CREATE INDEX idx_export_jobs_user_id ON export_jobs(user_id);

-- Index for background worker to find pending jobs
CREATE INDEX idx_export_jobs_status ON export_jobs(status) WHERE status = 'pending';

-- Index for cleanup of expired jobs
CREATE INDEX idx_export_jobs_expires_at ON export_jobs(expires_at) WHERE expires_at IS NOT NULL;

-- Comment documentation
COMMENT ON TABLE export_jobs IS 'User data export jobs with status tracking and file management';
COMMENT ON COLUMN export_jobs.format IS 'Export file format: json or csv';
COMMENT ON COLUMN export_jobs.status IS 'Job lifecycle status: pending, processing, completed, or failed';
COMMENT ON COLUMN export_jobs.expires_at IS 'When the export file will be automatically deleted (default: 7 days)';
COMMENT ON COLUMN export_jobs.file_path IS 'Path to completed export file on disk';
COMMENT ON COLUMN export_jobs.file_size_bytes IS 'Size of completed export file in bytes';
