-- Main files table
CREATE TABLE files (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL,
    uploaded_by UUID NOT NULL,
    original_filename VARCHAR(255) NOT NULL,
    file_type VARCHAR(100) NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    file_size_bytes BIGINT NOT NULL,
    file_hash VARCHAR(64) UNIQUE NOT NULL, -- SHA-256 for deduplication
    storage_path VARCHAR(500) NOT NULL,
    cdn_url VARCHAR(500),
    metadata JSONB DEFAULT '{}',
    tags VARCHAR(255)[] DEFAULT '{}',
    access_level VARCHAR(20) DEFAULT 'private',
    expiry_date TIMESTAMP WITH TIME ZONE,
    is_processed BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- File associations (link files to animals, breeding records, etc.)
CREATE TABLE file_associations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    file_id UUID REFERENCES files(id) ON DELETE CASCADE,
    entity_type VARCHAR(50) NOT NULL, -- 'animal', 'breeding_pair', 'organization'
    entity_id UUID NOT NULL,
    association_type VARCHAR(50) NOT NULL, -- 'profile_photo', 'health_record', 'certificate'
    display_order INTEGER DEFAULT 0,
    is_primary BOOLEAN DEFAULT FALSE,
    caption TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(file_id, entity_type, entity_id, association_type)
);

-- File versions for processed variants
CREATE TABLE file_versions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    original_file_id UUID REFERENCES files(id) ON DELETE CASCADE,
    version_type VARCHAR(50) NOT NULL, -- 'thumbnail', 'medium', 'watermarked'
    file_path VARCHAR(500) NOT NULL,
    cdn_url VARCHAR(500),
    dimensions JSONB, -- {width: 800, height: 600}
    file_size_bytes BIGINT,
    processing_params JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Processing jobs
CREATE TABLE processing_jobs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    file_id UUID REFERENCES files(id) ON DELETE CASCADE,
    job_type VARCHAR(50) NOT NULL, -- 'resize', 'transcode', 'thumbnail'
    status VARCHAR(20) DEFAULT 'queued',
    parameters JSONB DEFAULT '{}',
    progress_percent INTEGER DEFAULT 0,
    error_message TEXT,
    started_at TIMESTAMP WITH TIME ZONE,
    completed_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Processing templates for consistent operations
CREATE TABLE processing_templates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) UNIQUE NOT NULL,
    template_type VARCHAR(50) NOT NULL,
    parameters JSONB NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Rendering jobs for dynamic documents
CREATE TABLE rendering_jobs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL,
    requested_by UUID NOT NULL,
    render_type VARCHAR(50) NOT NULL, -- 'pedigree', 'health_cert', 'breeding_report'
    entity_id UUID NOT NULL, -- animal_id, breeding_pair_id, etc.
    template_id UUID,
    output_format VARCHAR(10) NOT NULL, -- 'pdf', 'svg', 'png'
    parameters JSONB DEFAULT '{}',
    status VARCHAR(20) DEFAULT 'queued',
    output_file_id UUID REFERENCES files(id),
    error_message TEXT,
    started_at TIMESTAMP WITH TIME ZONE,
    completed_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Document templates
CREATE TABLE document_templates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) NOT NULL,
    template_type VARCHAR(50) NOT NULL,
    template_content TEXT NOT NULL, -- HTML/SVG template
    style_sheet TEXT, -- CSS for styling
    variables JSONB DEFAULT '{}', -- Template variables definition
    preview_image_id UUID REFERENCES files(id),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Widget configurations
CREATE TABLE widget_configs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL,
    widget_type VARCHAR(50) NOT NULL, -- 'animal_profile', 'pedigree_tree', 'breeder_showcase'
    entity_id UUID NOT NULL,
    title VARCHAR(255),
    configuration JSONB DEFAULT '{}',
    styling JSONB DEFAULT '{}',
    access_level VARCHAR(20) DEFAULT 'public',
    is_active BOOLEAN DEFAULT TRUE,
    embed_code_generated BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Widget usage analytics
CREATE TABLE widget_analytics (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    widget_id UUID REFERENCES widget_configs(id) ON DELETE CASCADE,
    event_type VARCHAR(50) NOT NULL, -- 'view', 'click', 'interaction'
    event_data JSONB DEFAULT '{}',
    referrer_url VARCHAR(500),
    user_agent TEXT,
    ip_address INET,
    occurred_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);