-- Schéma applicatif du portail tarifs.
-- Complète schema.sql (tables mo_* du catalogue midocean).
-- MySQL 8 / MariaDB 10.5+


-- ---------------------------------------------------------------
-- Prix d'achat nets midocean (API pricelist/2.0)
-- Un tarif par SKU et par palier de quantité.
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS mo_price (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    sku           VARCHAR(64)     NOT NULL,
    quantity_from INT UNSIGNED    NOT NULL DEFAULT 1,
    net_price     DECIMAL(12,4)   NOT NULL,
    currency      CHAR(3)         NOT NULL DEFAULT 'EUR',
    valid_from    DATE            DEFAULT NULL,
    valid_until   DATE            DEFAULT NULL,
    raw_json      JSON            DEFAULT NULL,
    last_seen_at  DATETIME        NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_price (sku, quantity_from, currency),
    KEY idx_price_sku (sku)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------
-- Comptes clients (une entreprise) et utilisateurs (une personne)
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS app_client (
    id             INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name           VARCHAR(160) NOT NULL,
    reference      VARCHAR(32)  DEFAULT NULL,
    contact_email  VARCHAR(190) DEFAULT NULL,
    notes          TEXT         DEFAULT NULL,
    is_active      TINYINT(1)   NOT NULL DEFAULT 1,
    created_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_client_ref (reference),
    KEY idx_client_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS app_user (
    id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    client_id     INT UNSIGNED DEFAULT NULL,   -- NULL pour un admin
    email         VARCHAR(190) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name     VARCHAR(160) DEFAULT NULL,
    role          ENUM('admin','client') NOT NULL DEFAULT 'client',
    is_active     TINYINT(1)   NOT NULL DEFAULT 1,
    last_login_at DATETIME     DEFAULT NULL,
    failed_logins SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    locked_until  DATETIME     DEFAULT NULL,
    created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_user_email (email),
    KEY idx_user_client (client_id),
    CONSTRAINT fk_user_client FOREIGN KEY (client_id)
        REFERENCES app_client (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------
-- Règles de coefficient
--
-- Une seule table pour toutes les granularités. Les colonnes de
-- ciblage NULL valent « n'importe lequel ». La règle retenue pour un
-- couple (client, produit, quantité) est la plus spécifique :
--
--   client_id   → +16
--   master_code → +8
--   category_code → +4
--   brand       → +2
--
-- À score égal, la règle la plus récente gagne. Une règle sans aucun
-- ciblage (score 0) est le coefficient par défaut du portail.
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS app_coefficient (
    id            INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    label         VARCHAR(160)  DEFAULT NULL,
    client_id     INT UNSIGNED  DEFAULT NULL,
    brand         VARCHAR(64)   DEFAULT NULL,
    category_code VARCHAR(64)   DEFAULT NULL,
    master_code   VARCHAR(32)   DEFAULT NULL,
    min_qty       INT UNSIGNED  NOT NULL DEFAULT 1,
    coefficient   DECIMAL(6,4)  NOT NULL,
    is_active     TINYINT(1)    NOT NULL DEFAULT 1,
    created_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_coef_lookup (is_active, client_id, master_code, category_code, brand, min_qty),
    CONSTRAINT fk_coef_client FOREIGN KEY (client_id)
        REFERENCES app_client (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Coefficient par défaut : marge de 40 %
INSERT INTO app_coefficient (label, coefficient)
SELECT 'Coefficient par défaut', 1.4000
WHERE NOT EXISTS (
    SELECT 1 FROM app_coefficient
    WHERE client_id IS NULL AND brand IS NULL AND category_code IS NULL AND master_code IS NULL
);

-- ---------------------------------------------------------------
-- Visibilité du catalogue : par défaut tout est masqué côté client,
-- l'admin publie ce qu'il veut exposer.
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS app_published_product (
    master_code  VARCHAR(32) NOT NULL,
    published_at DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (master_code),
    CONSTRAINT fk_pub_product FOREIGN KEY (master_code)
        REFERENCES mo_product (master_code) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------
-- Demandes de devis
-- Les montants sont figés à la création : un changement de
-- coefficient ne doit jamais réécrire un devis déjà envoyé.
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS app_quote (
    id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
    reference  VARCHAR(24)  NOT NULL,
    client_id  INT UNSIGNED NOT NULL,
    user_id    INT UNSIGNED DEFAULT NULL,
    status     ENUM('brouillon','envoyee','traitee','annulee') NOT NULL DEFAULT 'brouillon',
    comment    TEXT         DEFAULT NULL,
    total_ht   DECIMAL(12,2) NOT NULL DEFAULT 0,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_quote_ref (reference),
    KEY idx_quote_client (client_id, status),
    CONSTRAINT fk_quote_client FOREIGN KEY (client_id)
        REFERENCES app_client (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS app_quote_item (
    id           INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    quote_id     INT UNSIGNED  NOT NULL,
    sku          VARCHAR(64)   NOT NULL,
    label        VARCHAR(255)  NOT NULL,
    quantity     INT UNSIGNED  NOT NULL,
    net_price    DECIMAL(12,4) NOT NULL,  -- prix d'achat retenu
    coefficient  DECIMAL(6,4)  NOT NULL,  -- coefficient appliqué
    unit_price   DECIMAL(12,4) NOT NULL,  -- prix de vente unitaire
    line_total   DECIMAL(12,2) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_item_quote (quote_id),
    CONSTRAINT fk_item_quote FOREIGN KEY (quote_id)
        REFERENCES app_quote (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------
-- Journal d'audit : qui a vu quoi, qui a changé quel coefficient
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS app_audit (
    id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id    INT UNSIGNED DEFAULT NULL,
    action     VARCHAR(64)  NOT NULL,
    target     VARCHAR(190) DEFAULT NULL,
    detail     JSON         DEFAULT NULL,
    ip         VARBINARY(16) DEFAULT NULL,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_audit_user (user_id, created_at),
    KEY idx_audit_action (action, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
