-- AI Document Intelligence Platform Database Schema

-- Clients table (reuse from main platform or separate)
CREATE TABLE IF NOT EXISTS clients (
    id SERIAL PRIMARY KEY,
    company_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    subscription_tier VARCHAR(50) DEFAULT 'starter',
    status VARCHAR(50) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Document types configuration
CREATE TABLE IF NOT EXISTS document_types (
    id SERIAL PRIMARY KEY,
    client_id INTEGER REFERENCES clients(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL, -- 'invoice', 'purchase_order', 'receipt', 'contract', etc.
    display_name VARCHAR(255) NOT NULL,
    description TEXT,
    extraction_template JSONB, -- Field definitions for extraction
    validation_rules JSONB, -- Validation rules for extracted data
    is_system_type BOOLEAN DEFAULT FALSE, -- System-provided vs custom
    status VARCHAR(50) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Documents uploaded by clients
CREATE TABLE IF NOT EXISTS documents (
    id SERIAL PRIMARY KEY,
    client_id INTEGER REFERENCES clients(id) ON DELETE CASCADE,
    document_type_id INTEGER REFERENCES document_types(id),

    -- File information
    original_filename VARCHAR(500) NOT NULL,
    file_path VARCHAR(1000) NOT NULL,
    file_size BIGINT NOT NULL, -- bytes
    file_type VARCHAR(50) NOT NULL, -- 'pdf', 'png', 'jpg', 'jpeg'
    file_hash VARCHAR(64), -- SHA-256 for duplicate detection

    -- Processing status
    status VARCHAR(50) DEFAULT 'pending', -- pending, processing, completed, failed, review_required
    processing_started_at TIMESTAMP,
    processing_completed_at TIMESTAMP,
    processing_time_ms INTEGER,

    -- OCR results
    ocr_text TEXT,
    ocr_confidence DECIMAL(5,2), -- 0-100

    -- Extracted data
    extracted_data JSONB, -- The extracted fields
    confidence_scores JSONB, -- Confidence per field

    -- Validation
    validation_status VARCHAR(50) DEFAULT 'pending', -- pending, passed, failed, manual_review
    validation_errors JSONB,
    validated_by INTEGER, -- User ID who validated
    validated_at TIMESTAMP,

    -- AI model info
    ai_model_used VARCHAR(100),
    tokens_used INTEGER,

    -- Metadata
    notes TEXT,
    tags VARCHAR(255)[],
    metadata JSONB,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Extracted fields from documents (normalized for querying)
CREATE TABLE IF NOT EXISTS document_fields (
    id SERIAL PRIMARY KEY,
    document_id INTEGER REFERENCES documents(id) ON DELETE CASCADE,
    field_name VARCHAR(255) NOT NULL,
    field_value TEXT,
    field_type VARCHAR(50), -- 'text', 'number', 'date', 'currency', 'email', etc.
    confidence DECIMAL(5,2),
    bounding_box JSONB, -- Coordinates in the document
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Document processing batches
CREATE TABLE IF NOT EXISTS processing_batches (
    id SERIAL PRIMARY KEY,
    client_id INTEGER REFERENCES clients(id) ON DELETE CASCADE,
    batch_name VARCHAR(255),
    total_documents INTEGER DEFAULT 0,
    processed_documents INTEGER DEFAULT 0,
    successful_documents INTEGER DEFAULT 0,
    failed_documents INTEGER DEFAULT 0,
    status VARCHAR(50) DEFAULT 'processing', -- processing, completed, failed
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP,
    created_by INTEGER,
    metadata JSONB
);

-- Link documents to batches
CREATE TABLE IF NOT EXISTS batch_documents (
    id SERIAL PRIMARY KEY,
    batch_id INTEGER REFERENCES processing_batches(id) ON DELETE CASCADE,
    document_id INTEGER REFERENCES documents(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(batch_id, document_id)
);

-- Templates for common document types
CREATE TABLE IF NOT EXISTS extraction_templates (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    document_type VARCHAR(100) NOT NULL,
    description TEXT,
    fields JSONB NOT NULL, -- Field definitions
    sample_document_url TEXT,
    usage_count INTEGER DEFAULT 0,
    is_public BOOLEAN DEFAULT TRUE,
    created_by INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Export history
CREATE TABLE IF NOT EXISTS exports (
    id SERIAL PRIMARY KEY,
    client_id INTEGER REFERENCES clients(id) ON DELETE CASCADE,
    export_type VARCHAR(50) NOT NULL, -- 'csv', 'json', 'excel', 'api'
    filter_criteria JSONB,
    record_count INTEGER,
    file_path VARCHAR(1000),
    status VARCHAR(50) DEFAULT 'processing',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP
);

-- API webhooks for real-time data delivery
CREATE TABLE IF NOT EXISTS webhooks (
    id SERIAL PRIMARY KEY,
    client_id INTEGER REFERENCES clients(id) ON DELETE CASCADE,
    webhook_url VARCHAR(1000) NOT NULL,
    event_type VARCHAR(100) NOT NULL, -- 'document_processed', 'extraction_completed', etc.
    secret_key VARCHAR(500),
    status VARCHAR(50) DEFAULT 'active',
    last_triggered_at TIMESTAMP,
    failure_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Usage tracking for billing
CREATE TABLE IF NOT EXISTS usage_logs (
    id SERIAL PRIMARY KEY,
    client_id INTEGER REFERENCES clients(id) ON DELETE CASCADE,
    resource_type VARCHAR(50) NOT NULL, -- 'document_processed', 'ocr_page', 'ai_extraction', 'api_call'
    quantity INTEGER DEFAULT 1,
    cost_credits DECIMAL(10,2), -- Internal credit system
    metadata JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Analytics aggregation
CREATE TABLE IF NOT EXISTS document_analytics (
    id SERIAL PRIMARY KEY,
    client_id INTEGER REFERENCES clients(id) ON DELETE CASCADE,
    date DATE NOT NULL,
    total_documents INTEGER DEFAULT 0,
    successful_extractions INTEGER DEFAULT 0,
    failed_extractions INTEGER DEFAULT 0,
    avg_processing_time_ms INTEGER,
    avg_confidence_score DECIMAL(5,2),
    total_pages_processed INTEGER DEFAULT 0,
    documents_by_type JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(client_id, date)
);

-- Indexes for performance
CREATE INDEX idx_documents_client_id ON documents(client_id);
CREATE INDEX idx_documents_status ON documents(status);
CREATE INDEX idx_documents_created_at ON documents(created_at);
CREATE INDEX idx_documents_document_type ON documents(document_type_id);
CREATE INDEX idx_document_fields_document_id ON document_fields(document_id);
CREATE INDEX idx_document_fields_field_name ON document_fields(field_name);
CREATE INDEX idx_usage_logs_client_id ON usage_logs(client_id);
CREATE INDEX idx_usage_logs_created_at ON usage_logs(created_at);

-- Full text search on extracted data
CREATE INDEX idx_documents_extracted_data_gin ON documents USING gin(extracted_data);
CREATE INDEX idx_documents_ocr_text_gin ON documents USING gin(to_tsvector('english', ocr_text));

-- Triggers
CREATE TRIGGER update_documents_updated_at BEFORE UPDATE ON documents
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_document_types_updated_at BEFORE UPDATE ON document_types
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Sample document types (system defaults)
INSERT INTO document_types (client_id, name, display_name, description, extraction_template, is_system_type) VALUES
(NULL, 'invoice', 'Invoice', 'Sales invoices and bills',
 '{"fields": [
   {"name": "invoice_number", "type": "text", "required": true},
   {"name": "invoice_date", "type": "date", "required": true},
   {"name": "due_date", "type": "date", "required": false},
   {"name": "vendor_name", "type": "text", "required": true},
   {"name": "vendor_address", "type": "text", "required": false},
   {"name": "vendor_gstin", "type": "text", "required": false},
   {"name": "customer_name", "type": "text", "required": false},
   {"name": "customer_gstin", "type": "text", "required": false},
   {"name": "subtotal", "type": "currency", "required": true},
   {"name": "tax_amount", "type": "currency", "required": false},
   {"name": "total_amount", "type": "currency", "required": true},
   {"name": "po_number", "type": "text", "required": false},
   {"name": "payment_terms", "type": "text", "required": false},
   {"name": "line_items", "type": "array", "required": false}
 ]}', TRUE),

(NULL, 'receipt', 'Receipt', 'Purchase receipts and expense receipts',
 '{"fields": [
   {"name": "receipt_number", "type": "text", "required": false},
   {"name": "date", "type": "date", "required": true},
   {"name": "merchant_name", "type": "text", "required": true},
   {"name": "total_amount", "type": "currency", "required": true},
   {"name": "tax_amount", "type": "currency", "required": false},
   {"name": "payment_method", "type": "text", "required": false},
   {"name": "category", "type": "text", "required": false}
 ]}', TRUE),

(NULL, 'purchase_order', 'Purchase Order', 'Purchase orders from customers',
 '{"fields": [
   {"name": "po_number", "type": "text", "required": true},
   {"name": "po_date", "type": "date", "required": true},
   {"name": "vendor_name", "type": "text", "required": true},
   {"name": "total_amount", "type": "currency", "required": true},
   {"name": "delivery_date", "type": "date", "required": false},
   {"name": "shipping_address", "type": "text", "required": false}
 ]}', TRUE),

(NULL, 'id_document', 'ID Document', 'Aadhar, PAN, Driving License, Passport',
 '{"fields": [
   {"name": "document_type", "type": "text", "required": true},
   {"name": "document_number", "type": "text", "required": true},
   {"name": "name", "type": "text", "required": true},
   {"name": "date_of_birth", "type": "date", "required": false},
   {"name": "address", "type": "text", "required": false},
   {"name": "issue_date", "type": "date", "required": false},
   {"name": "expiry_date", "type": "date", "required": false}
 ]}', TRUE);