-- Migration: V3__add_processed_attachment_table.sql
-- Author: Martin.Schweitzer@procon.co.at and claude.ai
-- Description: Add table for tracking processed mail attachments with idempotency support

-- Create processed_attachment table for tracking mail attachments
CREATE TABLE IF NOT EXISTS ted.processed_attachment (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Content hash for idempotent processing (SHA-256, 64 hex chars)
    content_hash VARCHAR(64) NOT NULL UNIQUE,

    -- File metadata
    original_filename VARCHAR(500) NOT NULL,
    file_type VARCHAR(50),
    content_type VARCHAR(255),
    file_size BIGINT,

    -- Processing status
    processing_status VARCHAR(20) NOT NULL DEFAULT 'PENDING',

    -- Extracted content
    extracted_text TEXT,

    -- Storage path
    saved_path VARCHAR(1000),

    -- Source email metadata
    mail_subject VARCHAR(500),
    mail_from VARCHAR(500),

    -- Parent reference (for files extracted from ZIP)
    parent_hash VARCHAR(64),

    -- Error handling
    error_message TEXT,

    -- Child count (for ZIP files)
    child_count INTEGER,

    -- Timestamps
    received_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    processed_at TIMESTAMP,

    -- Constraints
    CONSTRAINT chk_processing_status CHECK (processing_status IN ('PENDING', 'PROCESSING', 'COMPLETED', 'FAILED', 'DUPLICATE'))
);

-- Index on content_hash for fast idempotency lookups
CREATE UNIQUE INDEX IF NOT EXISTS idx_processed_attachment_hash
    ON ted.processed_attachment(content_hash);

-- Index on processing_status for finding pending/failed items
CREATE INDEX IF NOT EXISTS idx_processed_attachment_status
    ON ted.processed_attachment(processing_status);

-- Index on file_type for filtering by type
CREATE INDEX IF NOT EXISTS idx_processed_attachment_type
    ON ted.processed_attachment(file_type);

-- Index on parent_hash for finding children of ZIP files
CREATE INDEX IF NOT EXISTS idx_processed_attachment_parent
    ON ted.processed_attachment(parent_hash);

-- Index on received_at for chronological queries
CREATE INDEX IF NOT EXISTS idx_processed_attachment_received
    ON ted.processed_attachment(received_at DESC);

-- Comment on table
COMMENT ON TABLE ted.processed_attachment IS 'Tracks processed mail attachments with idempotency via content hash';
COMMENT ON COLUMN ted.processed_attachment.content_hash IS 'SHA-256 hash of file content for duplicate detection';
COMMENT ON COLUMN ted.processed_attachment.parent_hash IS 'Reference to parent attachment (for files extracted from ZIP)';
COMMENT ON COLUMN ted.processed_attachment.extracted_text IS 'Text content extracted from PDF and other document types';
