-- Migration 010: Supplier Invoice Templates
-- Template-based invoice recognition (inspired by OmniSync)

-- Vendor templates for automatic invoice field extraction
CREATE TABLE IF NOT EXISTS supplier_invoice_templates (
    template_id SERIAL PRIMARY KEY,
    vendor_id INTEGER REFERENCES vendors(id) ON DELETE CASCADE,
    template_name VARCHAR(255) NOT NULL,
    
    -- Detection patterns (JSON array of patterns to identify this template)
    detection_patterns JSONB DEFAULT '[]',
    -- Example: [
    --   {"type": "text", "pattern": "BMC Denmark ApS", "weight": 0.5},
    --   {"type": "cvr", "value": "12345678", "weight": 1.0}
    -- ]
    
    -- Field extraction rules (regex patterns with capture groups)
    field_mappings JSONB DEFAULT '{}',
    -- Example: {
    --   "invoice_number": {"pattern": "Faktura\\s*:?\\s*(\\d+)", "group": 1},
    --   "invoice_date": {"pattern": "(\\d{2}[/-]\\d{2}[/-]\\d{2,4})", "format": "DD/MM/YYYY", "group": 1},
    --   "total_amount": {"pattern": "Total\\s*:?\\s*([\\d.,]+)", "group": 1}
    -- }
    
    -- Statistics
    usage_count INTEGER DEFAULT 0,
    success_count INTEGER DEFAULT 0,
    last_used_at TIMESTAMP,
    
    -- Metadata
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE(vendor_id, template_name)
);

CREATE INDEX idx_supplier_invoice_templates_vendor ON supplier_invoice_templates(vendor_id);
CREATE INDEX idx_supplier_invoice_templates_active ON supplier_invoice_templates(is_active);

-- Add template reference to incoming_files
ALTER TABLE incoming_files ADD COLUMN IF NOT EXISTS template_id INTEGER REFERENCES supplier_invoice_templates(template_id);

-- Template usage tracking
CREATE TABLE IF NOT EXISTS template_usage_log (
    log_id SERIAL PRIMARY KEY,
    template_id INTEGER REFERENCES supplier_invoice_templates(template_id) ON DELETE CASCADE,
    file_id INTEGER REFERENCES incoming_files(file_id) ON DELETE CASCADE,
    matched BOOLEAN DEFAULT FALSE,
    confidence DECIMAL(3,2),
    fields_extracted JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_template_usage_log_template ON template_usage_log(template_id);
CREATE INDEX idx_template_usage_log_file ON template_usage_log(file_id);

COMMENT ON TABLE supplier_invoice_templates IS 'Templates for automatic invoice field extraction per vendor';
COMMENT ON COLUMN supplier_invoice_templates.detection_patterns IS 'JSON array of patterns to detect this template in PDF text';
COMMENT ON COLUMN supplier_invoice_templates.field_mappings IS 'JSON object with regex patterns to extract invoice fields';