CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

-- Instruments : tout actif coté, y compris les devises (EUR prix = 1)
CREATE TABLE instrument (
    id              SERIAL PRIMARY KEY,
    type            TEXT NOT NULL,          -- devise, action, etf, crypto
    code            TEXT NOT NULL UNIQUE,   -- EUR, ISIN, ticker
    name            TEXT NOT NULL,
    devise_cotation TEXT NOT NULL DEFAULT 'EUR'
);

-- Historique de prix intraday (hypertable TimescaleDB)
-- Le job de nettoyage consolide à J-1 en gardant uniquement le dernier fetch
CREATE TABLE price_history (
    instrument_id INTEGER     NOT NULL REFERENCES instrument(id),
    fetched_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    prix          NUMERIC(24, 8) NOT NULL,
    PRIMARY KEY (instrument_id, fetched_at)
);
SELECT create_hypertable('price_history', by_range('fetched_at'));
CREATE INDEX idx_price_history_instrument ON price_history(instrument_id, fetched_at DESC);

-- Cache OpenFIGI (ISIN → ticker Yahoo Finance) et CoinGecko (ticker → coin ID)
CREATE TABLE instrument_ticker_cache (
    instrument_id INTEGER     PRIMARY KEY REFERENCES instrument(id) ON DELETE CASCADE,
    ticker        TEXT        NOT NULL,
    fetched_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Comptes et enveloppes : les enveloppes sont des sous-comptes (master_account_id non null)
-- Un sous-compte hérite du type et de la devise de son maître ; pas de chaînage
CREATE TABLE account (
    id                SERIAL PRIMARY KEY,
    nom               TEXT NOT NULL,
    type              TEXT NOT NULL,         -- courant, livret, pea, cto, crypto, ...
    devise_reference  TEXT NOT NULL DEFAULT 'EUR',
    plafond           NUMERIC(24, 8),        -- indicatif (ex : plafond réglementaire Livret A)
    master_account_id INTEGER REFERENCES account(id) ON DELETE CASCADE,
    objectif          TEXT                   -- description libre pour les enveloppes
);

-- Règles de récurrence
CREATE TABLE recurring_rule (
    id                   SERIAL PRIMARY KEY,
    account_source_id    INTEGER REFERENCES account(id),
    instrument_source_id INTEGER REFERENCES instrument(id),
    quantite_source      NUMERIC(30, 18),
    account_dest_id      INTEGER REFERENCES account(id),
    instrument_dest_id   INTEGER REFERENCES instrument(id),
    quantite_dest        NUMERIC(30, 18),
    tiers                TEXT,
    label                TEXT NOT NULL,
    categorie            TEXT,
    frequence            TEXT NOT NULL,     -- RRULE string (RFC 5545)
    date_debut           DATE NOT NULL,
    date_fin             DATE,
    generated_until      DATE               -- curseur d'idempotence
);

-- Transactions : échange atomique source/dest généralisé aux instruments
CREATE TABLE transaction (
    id                   BIGSERIAL PRIMARY KEY,
    date                 DATE NOT NULL,
    account_source_id    INTEGER REFERENCES account(id),
    instrument_source_id INTEGER REFERENCES instrument(id),
    quantite_source      NUMERIC(30, 18),
    account_dest_id      INTEGER REFERENCES account(id),
    instrument_dest_id   INTEGER REFERENCES instrument(id),
    quantite_dest        NUMERIC(30, 18),
    tiers                TEXT,
    label                TEXT NOT NULL,
    categorie            TEXT,
    validated            BOOLEAN NOT NULL DEFAULT FALSE,
    recurring_rule_id    INTEGER REFERENCES recurring_rule(id),

    -- Chaque côté est soit entièrement renseigné soit entièrement null
    -- Au moins un côté doit exister
    CONSTRAINT chk_transaction_sides CHECK (
        (
            account_source_id    IS NOT NULL AND
            instrument_source_id IS NOT NULL AND
            quantite_source      IS NOT NULL
            OR
            account_source_id    IS NULL AND
            instrument_source_id IS NULL AND
            quantite_source      IS NULL
        )
        AND
        (
            account_dest_id    IS NOT NULL AND
            instrument_dest_id IS NOT NULL AND
            quantite_dest      IS NOT NULL
            OR
            account_dest_id    IS NULL AND
            instrument_dest_id IS NULL AND
            quantite_dest      IS NULL
        )
        AND
        (account_source_id IS NOT NULL OR account_dest_id IS NOT NULL)
    )
);

CREATE INDEX idx_transaction_date           ON transaction(date);
CREATE INDEX idx_transaction_account_source ON transaction(account_source_id) WHERE account_source_id IS NOT NULL;
CREATE INDEX idx_transaction_account_dest   ON transaction(account_dest_id)   WHERE account_dest_id   IS NOT NULL;
CREATE INDEX idx_transaction_unvalidated    ON transaction(date)               WHERE validated = FALSE;

-- Snapshots de position par instrument et par compte (hypertable TimescaleDB)
CREATE TABLE position_snapshot (
    date          DATE    NOT NULL,
    account_id    INTEGER NOT NULL REFERENCES account(id),
    instrument_id INTEGER NOT NULL REFERENCES instrument(id),
    quantite      NUMERIC(30, 18) NOT NULL,
    pru           NUMERIC(24, 8),
    prix_cloture  NUMERIC(24, 8),
    valeur        NUMERIC(24, 8),
    PRIMARY KEY (date, account_id, instrument_id)
);
SELECT create_hypertable('position_snapshot', by_range('date'));
CREATE INDEX idx_position_snapshot_lookup ON position_snapshot(account_id, instrument_id, date DESC);

-- Snapshots de valeur agrégée par compte (hypertable TimescaleDB)
CREATE TABLE account_snapshot (
    date       DATE    NOT NULL,
    account_id INTEGER NOT NULL REFERENCES account(id),
    valeur     NUMERIC(24, 8) NOT NULL,
    PRIMARY KEY (date, account_id)
);
SELECT create_hypertable('account_snapshot', by_range('date'));
CREATE INDEX idx_account_snapshot_lookup ON account_snapshot(account_id, date DESC);

-- EUR : instrument de base, prix toujours = 1
INSERT INTO instrument (type, code, name, devise_cotation) VALUES ('devise', 'EUR', 'Euro', 'EUR');