bmc_hub/migrations/008_supplier_invoices.sql

-- Migration 008: Supplier Invoices (Kassekladde / Leverandørfakturaer)
-- Tables for tracking invoices WE RECEIVE from vendors (incoming invoices we must pay)
-- Different from customer invoices which track invoices WE SEND to customers
-- Integrates with e-conomic kassekladde (journals/vouchers API)

-- Main supplier invoices table
CREATE TABLE IF NOT EXISTS supplier_invoices (
    id SERIAL PRIMARY KEY,
    invoice_number VARCHAR(100) NOT NULL,
    vendor_id INTEGER REFERENCES vendors(id),
    vendor_name VARCHAR(255),
    invoice_date DATE NOT NULL,
    due_date DATE,
    total_amount DECIMAL(15, 2) NOT NULL DEFAULT 0,
    vat_amount DECIMAL(15, 2) DEFAULT 0,
    net_amount DECIMAL(15, 2) DEFAULT 0,
    currency VARCHAR(10) DEFAULT 'DKK',
    description TEXT,
    notes TEXT,
    status VARCHAR(50) DEFAULT 'pending', -- pending, approved, sent_to_economic, paid, overdue, cancelled
    
    -- e-conomic integration fields
    economic_supplier_number INTEGER, -- Supplier number in e-conomic
    economic_journal_number INTEGER, -- Kassekladde number used
    economic_voucher_number INTEGER, -- Voucher number in e-conomic
    economic_accounting_year VARCHAR(4), -- Accounting year (e.g., "2025")
    sent_to_economic_at TIMESTAMP,
    
    -- Payment tracking
    paid_date DATE,
    payment_reference VARCHAR(100),
    
    -- Approval workflow
    approved_by VARCHAR(255),
    approved_at TIMESTAMP,
    
    -- File attachments
    file_path VARCHAR(500), -- Path to uploaded PDF/invoice file
    attachment_url VARCHAR(500), -- URL to external attachment
    
    -- Metadata
    created_by VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Line items for supplier invoices with VAT breakdown
CREATE TABLE IF NOT EXISTS supplier_invoice_lines (
    id SERIAL PRIMARY KEY,
    supplier_invoice_id INTEGER NOT NULL REFERENCES supplier_invoices(id) ON DELETE CASCADE,
    line_number INTEGER,
    description TEXT,
    quantity DECIMAL(10, 2) DEFAULT 1,
    unit_price DECIMAL(15, 2) DEFAULT 0,
    line_total DECIMAL(15, 2) DEFAULT 0,
    
    -- VAT details per line
    vat_code VARCHAR(20) DEFAULT 'I25', -- e-conomic VAT codes: I25, I0, IY25, IYEU, IVEU, etc.
    vat_rate DECIMAL(5, 2) DEFAULT 25.00,
    vat_amount DECIMAL(15, 2) DEFAULT 0,
    
    -- e-conomic account mapping
    contra_account VARCHAR(10) DEFAULT '5810', -- Default expense account
    
    -- Product linking (optional)
    product_id INTEGER,
    sku VARCHAR(100),
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- System settings for e-conomic kassekladde integration
-- Add to existing system_settings table if it exists, or track separately
CREATE TABLE IF NOT EXISTS supplier_invoice_settings (
    id SERIAL PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    description TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert default settings for kassekladde
INSERT INTO supplier_invoice_settings (setting_key, setting_value, description)
VALUES 
    ('economic_default_journal', '1', 'Default kassekladde nummer til leverandørfakturaer'),
    ('economic_default_contra_account', '5810', 'Default expense account (drift/materialer)'),
    ('auto_approve_under_amount', '1000', 'Auto-approve invoices under this amount (DKK)'),
    ('require_attachment', 'true', 'Require PDF attachment before sending to e-conomic')
ON CONFLICT (setting_key) DO NOTHING;

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_supplier_invoices_vendor ON supplier_invoices(vendor_id);
CREATE INDEX IF NOT EXISTS idx_supplier_invoices_status ON supplier_invoices(status);
CREATE INDEX IF NOT EXISTS idx_supplier_invoices_due_date ON supplier_invoices(due_date);
CREATE INDEX IF NOT EXISTS idx_supplier_invoices_economic_voucher ON supplier_invoices(economic_voucher_number);
CREATE INDEX IF NOT EXISTS idx_supplier_invoice_lines_invoice ON supplier_invoice_lines(supplier_invoice_id);

-- Trigger to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_supplier_invoice_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER supplier_invoices_updated_at
    BEFORE UPDATE ON supplier_invoices
    FOR EACH ROW
    EXECUTE FUNCTION update_supplier_invoice_timestamp();

-- View for overdue supplier invoices
CREATE OR REPLACE VIEW overdue_supplier_invoices AS
SELECT 
    si.*,
    v.name as vendor_full_name,
    v.economic_supplier_number as vendor_economic_id,
    (CURRENT_DATE - si.due_date) as days_overdue
FROM supplier_invoices si
LEFT JOIN vendors v ON si.vendor_id = v.id
WHERE si.status IN ('pending', 'approved')
  AND si.due_date < CURRENT_DATE
ORDER BY si.due_date ASC;

-- View for pending e-conomic sync
CREATE OR REPLACE VIEW pending_economic_sync AS
SELECT 
    si.*,
    v.name as vendor_full_name,
    v.economic_supplier_number as vendor_economic_id,
    COUNT(sil.id) as line_count
FROM supplier_invoices si
LEFT JOIN vendors v ON si.vendor_id = v.id
LEFT JOIN supplier_invoice_lines sil ON si.id = sil.supplier_invoice_id
WHERE si.status = 'approved'
  AND si.economic_voucher_number IS NULL
  AND (
      (SELECT setting_value FROM supplier_invoice_settings WHERE setting_key = 'require_attachment') = 'false'
      OR si.file_path IS NOT NULL
  )
GROUP BY si.id, v.name, v.economic_supplier_number
ORDER BY si.invoice_date ASC;

COMMENT ON TABLE supplier_invoices IS 'Leverandørfakturaer - invoices received from vendors that we must pay';
COMMENT ON TABLE supplier_invoice_lines IS 'Line items for supplier invoices with VAT and account details';
COMMENT ON TABLE supplier_invoice_settings IS 'System settings for supplier invoice and e-conomic kassekladde integration';
COMMENT ON VIEW overdue_supplier_invoices IS 'All unpaid supplier invoices past their due date';
COMMENT ON VIEW pending_economic_sync IS 'Approved supplier invoices ready to be sent to e-conomic kassekladde';