backend/database/schema_sqlite.sql

-- ============================================
-- Invoice Payment Prediction System - SQLite Schema
-- Version: 1.0 (SQLite)
-- ============================================

-- Drop existing tables
DROP TABLE IF EXISTS predictions_log;
DROP TABLE IF EXISTS business_code_aggregates;
DROP TABLE IF EXISTS payment_terms_aggregates;
DROP TABLE IF EXISTS customer_aggregates;
DROP TABLE IF EXISTS invoices_history;

-- ============================================
-- Table 1: invoices_history
-- ============================================
CREATE TABLE invoices_history (
    invoice_id INTEGER PRIMARY KEY,
    business_code TEXT NOT NULL,
    cust_number TEXT NOT NULL,
    name_customer TEXT,
    
    -- Dates (stored as TEXT in ISO format: YYYY-MM-DD HH:MM:SS)
    posting_date TEXT NOT NULL,
    document_create_date TEXT,
    document_create_date_alt TEXT,
    due_in_date TEXT,
    baseline_create_date TEXT,
    clear_date TEXT,
    
    -- Financial
    total_open_amount REAL NOT NULL,
    invoice_currency TEXT DEFAULT 'USD',
    
    -- Metadata
    document_type TEXT,
    cust_payment_terms TEXT,
    posting_id REAL,
    is_open INTEGER DEFAULT 1,
    business_year INTEGER,
    
    -- Computed fields
    days_to_clear INTEGER,
    days_posting_to_due INTEGER,
    days_create_to_posting INTEGER,
    days_baseline_to_posting INTEGER,
    is_overdue INTEGER DEFAULT 0,
    
    -- Audit
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_invoices_cust ON invoices_history(cust_number);
CREATE INDEX idx_invoices_posting ON invoices_history(posting_date);
CREATE INDEX idx_invoices_cleared ON invoices_history(cust_number, posting_date) WHERE clear_date IS NOT NULL;

-- ============================================
-- Table 2: customer_aggregates
-- ============================================
CREATE TABLE customer_aggregates (
    cust_number TEXT PRIMARY KEY,
    cust_invoice_count INTEGER DEFAULT 0,
    cust_cleared_count INTEGER DEFAULT 0,
    
    cust_avg_days REAL,
    cust_median_days REAL,
    cust_std_days REAL,
    cust_min_days INTEGER,
    cust_max_days INTEGER,
    
    cust_avg_amount REAL,
    cust_total_amount REAL,
    cust_pct_overdue REAL DEFAULT 0.0,
    
    most_common_payment_term TEXT,
    most_common_business_code TEXT,
    most_common_currency TEXT,
    
    last_computed_at TEXT DEFAULT CURRENT_TIMESTAMP,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- ============================================
-- Table 3: payment_terms_aggregates
-- ============================================
CREATE TABLE payment_terms_aggregates (
    cust_payment_terms TEXT PRIMARY KEY,
    payment_terms_avg_days REAL,
    payment_terms_median_days REAL,
    payment_terms_count INTEGER DEFAULT 0,
    last_computed_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- ============================================
-- Table 4: business_code_aggregates
-- ============================================
CREATE TABLE business_code_aggregates (
    business_code TEXT PRIMARY KEY,
    business_avg_days REAL,
    business_median_days REAL,
    business_count INTEGER DEFAULT 0,
    last_computed_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- ============================================
-- Table 5: predictions_log
-- ============================================
CREATE TABLE predictions_log (
    prediction_id INTEGER PRIMARY KEY AUTOINCREMENT,
    invoice_id INTEGER,
    cust_number TEXT NOT NULL,
    posting_date TEXT NOT NULL,
    total_open_amount REAL NOT NULL,
    business_code TEXT,
    cust_payment_terms TEXT,
    
    predicted_days_to_clear REAL NOT NULL,
    predicted_clear_date TEXT NOT NULL,
    
    model_version TEXT,
    features_json TEXT,
    
    actual_clear_date TEXT,
    actual_days_to_clear INTEGER,
    prediction_error REAL,
    absolute_error REAL,
    
    predicted_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_predictions_cust ON predictions_log(cust_number);
CREATE INDEX idx_predictions_date ON predictions_log(predicted_at);