cash_flow_forecast_postgresql.sql

-- =====================================================
-- OUTIL DE PRÉVISION DE TRÉSORERIE
-- Base de données PostgreSQL
-- Version: 1.0
-- =====================================================

-- Suppression des tables existantes (dans l'ordre des dépendances)
DROP TABLE IF EXISTS forecast_adjustments CASCADE;
DROP TABLE IF EXISTS payment_behavior_patterns CASCADE;
DROP TABLE IF EXISTS forecasts CASCADE;
DROP TABLE IF EXISTS alerts CASCADE;
DROP TABLE IF EXISTS transactions CASCADE;
DROP TABLE IF EXISTS invoices CASCADE;
DROP TABLE IF EXISTS third_parties CASCADE;
DROP TABLE IF EXISTS categories CASCADE;
DROP TABLE IF EXISTS bank_accounts CASCADE;
DROP TABLE IF EXISTS companies CASCADE;
DROP TABLE IF EXISTS users CASCADE;

-- Suppression des types ENUM personnalisés
DROP TYPE IF EXISTS user_role CASCADE;
DROP TYPE IF EXISTS third_party_type CASCADE;
DROP TYPE IF EXISTS invoice_type CASCADE;
DROP TYPE IF EXISTS invoice_status CASCADE;
DROP TYPE IF EXISTS transaction_type CASCADE;
DROP TYPE IF EXISTS pattern_type CASCADE;
DROP TYPE IF EXISTS trend_direction CASCADE;
DROP TYPE IF EXISTS scenario_type CASCADE;
DROP TYPE IF EXISTS adjustment_type CASCADE;
DROP TYPE IF EXISTS alert_type CASCADE;
DROP TYPE IF EXISTS severity_level CASCADE;
DROP TYPE IF EXISTS entity_type CASCADE;
DROP TYPE IF EXISTS category_type CASCADE;

-- =====================================================
-- CRÉATION DES TYPES ENUM
-- =====================================================
CREATE TYPE user_role AS ENUM ('admin', 'manager', 'analyst', 'viewer');
CREATE TYPE third_party_type AS ENUM ('customer', 'supplier', 'both');
CREATE TYPE invoice_type AS ENUM ('customer_invoice', 'supplier_invoice');
CREATE TYPE invoice_status AS ENUM ('draft', 'sent', 'partial', 'paid', 'overdue', 'cancelled');
CREATE TYPE transaction_type AS ENUM ('debit', 'credit');
CREATE TYPE pattern_type AS ENUM ('payment_delay', 'seasonality', 'amount_trend');
CREATE TYPE trend_direction AS ENUM ('increasing', 'stable', 'decreasing');
CREATE TYPE scenario_type AS ENUM ('optimistic', 'realistic', 'pessimistic');
CREATE TYPE adjustment_type AS ENUM ('manual_override', 'exceptional_event', 'correction');
CREATE TYPE alert_type AS ENUM ('low_balance', 'overdue_payment', 'unusual_pattern', 'threshold_breach');
CREATE TYPE severity_level AS ENUM ('info', 'warning', 'critical');
CREATE TYPE entity_type AS ENUM ('invoice', 'transaction', 'forecast', 'third_party');
CREATE TYPE category_type AS ENUM ('income', 'expense');

-- =====================================================
-- TABLE: users
-- Description: Utilisateurs du système
-- =====================================================
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    role user_role NOT NULL DEFAULT 'viewer',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_role ON users(role);
CREATE INDEX idx_users_active ON users(is_active);

-- =====================================================
-- TABLE: companies
-- Description: Entreprises/entités gérées
-- =====================================================
CREATE TABLE companies (
    company_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    legal_name VARCHAR(255),
    registration_number VARCHAR(50),
    tax_id VARCHAR(50),
    currency VARCHAR(3) NOT NULL DEFAULT 'EUR',
    fiscal_year_start DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

CREATE INDEX idx_companies_name ON companies(name);
CREATE INDEX idx_companies_active ON companies(is_active);

-- =====================================================
-- TABLE: bank_accounts
-- Description: Comptes bancaires de l'entreprise
-- =====================================================
CREATE TABLE bank_accounts (
    account_id SERIAL PRIMARY KEY,
    company_id INTEGER NOT NULL,
    account_name VARCHAR(255) NOT NULL,
    account_number VARCHAR(50) NOT NULL,
    bank_name VARCHAR(255),
    iban VARCHAR(34),
    swift_bic VARCHAR(11),
    currency VARCHAR(3) NOT NULL DEFAULT 'EUR',
    current_balance DECIMAL(15, 2) DEFAULT 0.00,
    minimum_balance_threshold DECIMAL(15, 2),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_bank_accounts_company 
        FOREIGN KEY (company_id) REFERENCES companies(company_id) ON DELETE CASCADE
);

CREATE INDEX idx_bank_accounts_company ON bank_accounts(company_id);
CREATE INDEX idx_bank_accounts_active ON bank_accounts(is_active);
CREATE INDEX idx_bank_accounts_number ON bank_accounts(account_number);

-- =====================================================
-- TABLE: categories
-- Description: Catégories de transactions
-- =====================================================
CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    company_id INTEGER NOT NULL,
    name VARCHAR(100) NOT NULL,
    type category_type NOT NULL,
    parent_category_id INTEGER NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_categories_company 
        FOREIGN KEY (company_id) REFERENCES companies(company_id) ON DELETE CASCADE,
    CONSTRAINT fk_categories_parent 
        FOREIGN KEY (parent_category_id) REFERENCES categories(category_id) ON DELETE SET NULL
);

CREATE INDEX idx_categories_company ON categories(company_id);
CREATE INDEX idx_categories_type ON categories(type);
CREATE INDEX idx_categories_parent ON categories(parent_category_id);

-- =====================================================
-- TABLE: third_parties
-- Description: Clients et fournisseurs
-- =====================================================
CREATE TABLE third_parties (
    third_party_id SERIAL PRIMARY KEY,
    company_id INTEGER NOT NULL,
    name VARCHAR(255) NOT NULL,
    type third_party_type NOT NULL,
    registration_number VARCHAR(50),
    tax_id VARCHAR(50),
    email VARCHAR(255),
    phone VARCHAR(50),
    address TEXT,
    payment_terms_days INTEGER DEFAULT 30,
    credit_limit DECIMAL(15, 2),
    reliability_score DECIMAL(3, 2) DEFAULT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_third_parties_company 
        FOREIGN KEY (company_id) REFERENCES companies(company_id) ON DELETE CASCADE
);

COMMENT ON COLUMN third_parties.reliability_score IS 'Score 0-1 basé sur historique de paiement';

CREATE INDEX idx_third_parties_company ON third_parties(company_id);
CREATE INDEX idx_third_parties_type ON third_parties(type);
CREATE INDEX idx_third_parties_name ON third_parties(name);
CREATE INDEX idx_third_parties_active ON third_parties(is_active);
CREATE INDEX idx_third_parties_reliability ON third_parties(reliability_score);

-- =====================================================
-- TABLE: invoices
-- Description: Factures clients et fournisseurs
-- =====================================================
CREATE TABLE invoices (
    invoice_id SERIAL PRIMARY KEY,
    company_id INTEGER NOT NULL,
    third_party_id INTEGER NOT NULL,
    invoice_number VARCHAR(50) NOT NULL,
    invoice_type invoice_type NOT NULL,
    invoice_date DATE NOT NULL,
    due_date DATE NOT NULL,
    amount DECIMAL(15, 2) NOT NULL,
    tax_amount DECIMAL(15, 2) DEFAULT 0.00,
    total_amount DECIMAL(15, 2) NOT NULL,
    currency VARCHAR(3) NOT NULL DEFAULT 'EUR',
    status invoice_status DEFAULT 'draft',
    paid_amount DECIMAL(15, 2) DEFAULT 0.00,
    category_id INTEGER,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_invoices_company 
        FOREIGN KEY (company_id) REFERENCES companies(company_id) ON DELETE CASCADE,
    CONSTRAINT fk_invoices_third_party 
        FOREIGN KEY (third_party_id) REFERENCES third_parties(third_party_id) ON DELETE RESTRICT,
    CONSTRAINT fk_invoices_category 
        FOREIGN KEY (category_id) REFERENCES categories(category_id) ON DELETE SET NULL,
    CONSTRAINT unique_invoice_number UNIQUE (company_id, invoice_number)
);

CREATE INDEX idx_invoices_company ON invoices(company_id);
CREATE INDEX idx_invoices_third_party ON invoices(third_party_id);
CREATE INDEX idx_invoices_invoice_date ON invoices(invoice_date);
CREATE INDEX idx_invoices_due_date ON invoices(due_date);
CREATE INDEX idx_invoices_status ON invoices(status);
CREATE INDEX idx_invoices_type ON invoices(invoice_type);

-- =====================================================
-- TABLE: transactions
-- Description: Transactions bancaires réelles
-- =====================================================
CREATE TABLE transactions (
    transaction_id SERIAL PRIMARY KEY,
    account_id INTEGER NOT NULL,
    company_id INTEGER NOT NULL,
    third_party_id INTEGER,
    invoice_id INTEGER,
    category_id INTEGER,
    transaction_date DATE NOT NULL,
    value_date DATE,
    amount DECIMAL(15, 2) NOT NULL,
    type transaction_type NOT NULL,
    description TEXT,
    reference VARCHAR(100),
    is_reconciled BOOLEAN DEFAULT FALSE,
    reconciliation_date TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_transactions_account 
        FOREIGN KEY (account_id) REFERENCES bank_accounts(account_id) ON DELETE CASCADE,
    CONSTRAINT fk_transactions_company 
        FOREIGN KEY (company_id) REFERENCES companies(company_id) ON DELETE CASCADE,
    CONSTRAINT fk_transactions_third_party 
        FOREIGN KEY (third_party_id) REFERENCES third_parties(third_party_id) ON DELETE SET NULL,
    CONSTRAINT fk_transactions_invoice 
        FOREIGN KEY (invoice_id) REFERENCES invoices(invoice_id) ON DELETE SET NULL,
    CONSTRAINT fk_transactions_category 
        FOREIGN KEY (category_id) REFERENCES categories(category_id) ON DELETE SET NULL
);

CREATE INDEX idx_transactions_account ON transactions(account_id);
CREATE INDEX idx_transactions_company ON transactions(company_id);
CREATE INDEX idx_transactions_transaction_date ON transactions(transaction_date);
CREATE INDEX idx_transactions_value_date ON transactions(value_date);
CREATE INDEX idx_transactions_type ON transactions(type);
CREATE INDEX idx_transactions_third_party ON transactions(third_party_id);
CREATE INDEX idx_transactions_reconciled ON transactions(is_reconciled);

-- =====================================================
-- TABLE: payment_behavior_patterns
-- Description: Patterns de comportement de paiement
-- =====================================================
CREATE TABLE payment_behavior_patterns (
    pattern_id SERIAL PRIMARY KEY,
    third_party_id INTEGER NOT NULL,
    company_id INTEGER NOT NULL,
    pattern_type pattern_type NOT NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    average_delay_days DECIMAL(5, 2),
    median_delay_days INTEGER,
    standard_deviation DECIMAL(5, 2),
    on_time_payment_rate DECIMAL(5, 4),
    sample_size INTEGER,
    confidence_score DECIMAL(3, 2),
    seasonal_factor DECIMAL(5, 4),
    trend_direction trend_direction,
    notes TEXT,
    calculated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_patterns_third_party 
        FOREIGN KEY (third_party_id) REFERENCES third_parties(third_party_id) ON DELETE CASCADE,
    CONSTRAINT fk_patterns_company 
        FOREIGN KEY (company_id) REFERENCES companies(company_id) ON DELETE CASCADE
);

COMMENT ON COLUMN payment_behavior_patterns.on_time_payment_rate IS 'Pourcentage de paiements à temps';
COMMENT ON COLUMN payment_behavior_patterns.sample_size IS 'Nombre de transactions analysées';
COMMENT ON COLUMN payment_behavior_patterns.confidence_score IS 'Score de confiance 0-1';
COMMENT ON COLUMN payment_behavior_patterns.seasonal_factor IS 'Facteur de saisonnalité';

CREATE INDEX idx_patterns_third_party ON payment_behavior_patterns(third_party_id);
CREATE INDEX idx_patterns_company ON payment_behavior_patterns(company_id);
CREATE INDEX idx_patterns_type ON payment_behavior_patterns(pattern_type);
CREATE INDEX idx_patterns_period ON payment_behavior_patterns(period_start, period_end);
CREATE INDEX idx_patterns_calculated ON payment_behavior_patterns(calculated_at);

-- =====================================================
-- TABLE: forecasts
-- Description: Prévisions de trésorerie
-- =====================================================
CREATE TABLE forecasts (
    forecast_id SERIAL PRIMARY KEY,
    company_id INTEGER NOT NULL,
    account_id INTEGER,
    forecast_date DATE NOT NULL,
    scenario_type scenario_type DEFAULT 'realistic',
    predicted_balance DECIMAL(15, 2) NOT NULL,
    predicted_inflow DECIMAL(15, 2) DEFAULT 0.00,
    predicted_outflow DECIMAL(15, 2) DEFAULT 0.00,
    confidence_level DECIMAL(5, 4),
    base_amount DECIMAL(15, 2),
    model_version VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by INTEGER,
    notes TEXT,
    CONSTRAINT fk_forecasts_company 
        FOREIGN KEY (company_id) REFERENCES companies(company_id) ON DELETE CASCADE,
    CONSTRAINT fk_forecasts_account 
        FOREIGN KEY (account_id) REFERENCES bank_accounts(account_id) ON DELETE SET NULL,
    CONSTRAINT fk_forecasts_created_by 
        FOREIGN KEY (created_by) REFERENCES users(user_id) ON DELETE SET NULL,
    CONSTRAINT unique_forecast UNIQUE (company_id, account_id, forecast_date, scenario_type)
);

COMMENT ON COLUMN forecasts.confidence_level IS 'Niveau de confiance 0-1';
COMMENT ON COLUMN forecasts.base_amount IS 'Montant de base avant ajustements';

CREATE INDEX idx_forecasts_company ON forecasts(company_id);
CREATE INDEX idx_forecasts_account ON forecasts(account_id);
CREATE INDEX idx_forecasts_forecast_date ON forecasts(forecast_date);
CREATE INDEX idx_forecasts_scenario ON forecasts(scenario_type);
CREATE INDEX idx_forecasts_created ON forecasts(created_at);

-- =====================================================
-- TABLE: forecast_adjustments
-- Description: Ajustements manuels des prévisions
-- =====================================================
CREATE TABLE forecast_adjustments (
    adjustment_id SERIAL PRIMARY KEY,
    forecast_id INTEGER NOT NULL,
    adjustment_date DATE NOT NULL,
    adjustment_amount DECIMAL(15, 2) NOT NULL,
    adjustment_type adjustment_type NOT NULL,
    reason TEXT NOT NULL,
    created_by INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_adjustments_forecast 
        FOREIGN KEY (forecast_id) REFERENCES forecasts(forecast_id) ON DELETE CASCADE,
    CONSTRAINT fk_adjustments_created_by 
        FOREIGN KEY (created_by) REFERENCES users(user_id) ON DELETE RESTRICT
);

CREATE INDEX idx_adjustments_forecast ON forecast_adjustments(forecast_id);
CREATE INDEX idx_adjustments_adjustment_date ON forecast_adjustments(adjustment_date);
CREATE INDEX idx_adjustments_type ON forecast_adjustments(adjustment_type);

-- =====================================================
-- TABLE: alerts
-- Description: Alertes et notifications
-- =====================================================
CREATE TABLE alerts (
    alert_id SERIAL PRIMARY KEY,
    company_id INTEGER NOT NULL,
    alert_type alert_type NOT NULL,
    severity severity_level NOT NULL,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    related_entity_type entity_type NULL,
    related_entity_id INTEGER NULL,
    threshold_value DECIMAL(15, 2),
    current_value DECIMAL(15, 2),
    is_read BOOLEAN DEFAULT FALSE,
    is_resolved BOOLEAN DEFAULT FALSE,
    resolved_at TIMESTAMP NULL,
    resolved_by INTEGER NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_alerts_company 
        FOREIGN KEY (company_id) REFERENCES companies(company_id) ON DELETE CASCADE,
    CONSTRAINT fk_alerts_resolved_by 
        FOREIGN KEY (resolved_by) REFERENCES users(user_id) ON DELETE SET NULL
);

CREATE INDEX idx_alerts_company ON alerts(company_id);
CREATE INDEX idx_alerts_type ON alerts(alert_type);
CREATE INDEX idx_alerts_severity ON alerts(severity);
CREATE INDEX idx_alerts_read ON alerts(is_read);
CREATE INDEX idx_alerts_resolved ON alerts(is_resolved);
CREATE INDEX idx_alerts_created ON alerts(created_at);

-- =====================================================
-- FONCTIONS TRIGGERS POUR updated_at
-- =====================================================
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Application des triggers
CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_companies_updated_at BEFORE UPDATE ON companies
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_bank_accounts_updated_at BEFORE UPDATE ON bank_accounts
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_third_parties_updated_at BEFORE UPDATE ON third_parties
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_invoices_updated_at BEFORE UPDATE ON invoices
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_transactions_updated_at BEFORE UPDATE ON transactions
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- =====================================================
-- VUES UTILES
-- =====================================================

-- Vue: Résumé de trésorerie par compte
CREATE OR REPLACE VIEW v_account_summary AS
SELECT 
    ba.account_id,
    ba.company_id,
    ba.account_name,
    ba.current_balance,
    COUNT(DISTINCT t.transaction_id) AS transaction_count,
    SUM(CASE WHEN t.type = 'credit' THEN t.amount ELSE 0 END) AS total_inflow,
    SUM(CASE WHEN t.type = 'debit' THEN t.amount ELSE 0 END) AS total_outflow,
    MAX(t.transaction_date) AS last_transaction_date
FROM bank_accounts ba
LEFT JOIN transactions t ON ba.account_id = t.account_id
GROUP BY ba.account_id, ba.company_id, ba.account_name, ba.current_balance;

-- Vue: Factures en attente de paiement
CREATE OR REPLACE VIEW v_outstanding_invoices AS
SELECT 
    i.invoice_id,
    i.company_id,
    i.invoice_number,
    i.invoice_type,
    tp.name AS third_party_name,
    i.invoice_date,
    i.due_date,
    i.total_amount,
    i.paid_amount,
    (i.total_amount - i.paid_amount) AS outstanding_amount,
    (CURRENT_DATE - i.due_date) AS days_overdue,
    CASE 
        WHEN i.due_date < CURRENT_DATE THEN 'overdue'
        WHEN i.due_date <= CURRENT_DATE + INTERVAL '7 days' THEN 'due_soon'
        ELSE 'pending'
    END AS payment_status
FROM invoices i
INNER JOIN third_parties tp ON i.third_party_id = tp.third_party_id
WHERE i.status NOT IN ('paid', 'cancelled')
    AND (i.total_amount - i.paid_amount) > 0;

-- Vue: Performance des prévisions
CREATE OR REPLACE VIEW v_forecast_accuracy AS
SELECT 
    f.forecast_id,
    f.company_id,
    f.forecast_date,
    f.scenario_type,
    f.predicted_balance,
    ba.current_balance AS actual_balance,
    ABS(f.predicted_balance - ba.current_balance) AS variance,
    CASE 
        WHEN ba.current_balance != 0 THEN 
            (ABS(f.predicted_balance - ba.current_balance) / ABS(ba.current_balance)) * 100
        ELSE NULL 
    END AS variance_percentage
FROM forecasts f
INNER JOIN bank_accounts ba ON f.account_id = ba.account_id
WHERE f.forecast_date <= CURRENT_DATE;

-- =====================================================
-- DONNÉES D'EXEMPLE (optionnel)
-- =====================================================

-- Insertion d'un utilisateur admin par défaut
INSERT INTO users (email, password_hash, first_name, last_name, role)
VALUES ('admin@cashflow.com', '$2y$10$example_hash', 'Admin', 'System', 'admin');

-- Insertion d'une entreprise exemple
INSERT INTO companies (name, legal_name, currency, fiscal_year_start)
VALUES ('TechFlow SAS', 'TechFlow Société par Actions Simplifiée', 'EUR', '2024-01-01');

-- =====================================================
-- FIN DU SCRIPT
-- =====================================================