-- ═══════════════════════════════════════════════════════════
-- Floramaar — Schéma MySQL P0
-- Toujours utiliser DECIMAL(10,2) pour les montants.
-- Jamais de FLOAT pour les prix ou commissions.
-- ═══════════════════════════════════════════════════════════

SET FOREIGN_KEY_CHECKS = 0;
SET NAMES utf8mb4;

-- ─── Couleurs ────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS colors (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(50)  NOT NULL UNIQUE,
  hex_color     VARCHAR(10)  NULL,
  border_color  VARCHAR(10)  NULL,
  is_multicolor TINYINT(1)   NOT NULL DEFAULT 0,
  is_special    TINYINT(1)   NOT NULL DEFAULT 0   -- ex: Jaune → vigilance visuelle
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── Produits ────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS products (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sku          VARCHAR(20)    NOT NULL UNIQUE,
  name         VARCHAR(150)   NOT NULL,
  short_name   VARCHAR(80)    NOT NULL,
  icon_type    VARCHAR(30)    NOT NULL DEFAULT 'puce',
  public_price DECIMAL(10,2)  NOT NULL,
  image_path   VARCHAR(255)   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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── Couleurs autorisées par produit (NULL = toutes) ─────────────────
CREATE TABLE IF NOT EXISTS product_colors (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  product_id INT UNSIGNED NOT NULL,
  color_id   INT UNSIGNED NOT NULL,
  UNIQUE KEY uq_prod_col (product_id, color_id),
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  FOREIGN KEY (color_id)   REFERENCES colors(id)   ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── Partenaires ────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS partners (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name             VARCHAR(150)  NOT NULL,
  contact_name     VARCHAR(150)  NULL,
  phone            VARCHAR(30)   NULL,
  email            VARCHAR(180)  NULL,
  address          VARCHAR(255)  NULL,
  is_active        TINYINT(1)    NOT NULL DEFAULT 1,
  commission_type  ENUM('pct','palier') NOT NULL DEFAULT 'pct',
  commission_value DECIMAL(5,2)  NULL,       -- % si type=pct
  created_at       DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at       DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── Paliers de commission ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS commission_tiers (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  partner_id  INT UNSIGNED   NOT NULL,
  max_price   DECIMAL(10,2)  NOT NULL,
  gain_amount DECIMAL(10,2)  NOT NULL,
  sort_order  INT UNSIGNED   NOT NULL DEFAULT 0,
  FOREIGN KEY (partner_id) REFERENCES partners(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── Utilisateurs ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  login         VARCHAR(80)  NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  display_name  VARCHAR(150) NOT NULL,
  role          ENUM('admin','partner') NOT NULL DEFAULT 'partner',
  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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── Liaison utilisateur ↔ partenaire ────────────────────────────────
CREATE TABLE IF NOT EXISTS partner_users (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  partner_id INT UNSIGNED NOT NULL,
  user_id    INT UNSIGNED NOT NULL,
  UNIQUE KEY uq_pu (partner_id, user_id),
  FOREIGN KEY (partner_id) REFERENCES partners(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id)    REFERENCES users(id)    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── Dépôts (en-tête) ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS deposits (
  id                 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  partner_id         INT UNSIGNED  NOT NULL,
  deposit_reference  VARCHAR(50)   NULL,
  deposited_at       DATE          NULL,
  status             ENUM('active','closed') NOT NULL DEFAULT 'active',
  validation_status  ENUM('pending','validated') NOT NULL DEFAULT 'pending',
  validated_by_user_id INT UNSIGNED NULL,
  validated_at       DATETIME NULL,
  is_initial_snapshot TINYINT(1)  NOT NULL DEFAULT 0,
  snapshot_note      TEXT          NULL,
  created_at         DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (partner_id) REFERENCES partners(id) ON DELETE RESTRICT,
  CONSTRAINT fk_deposits_validated_by FOREIGN KEY (validated_by_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── Lignes de dépôt ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS deposit_items (
  id                    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  deposit_id            INT UNSIGNED  NOT NULL,
  product_id            INT UNSIGNED  NOT NULL,
  color_snapshot        VARCHAR(50)   NOT NULL,
  initial_quantity      INT UNSIGNED  NOT NULL DEFAULT 0,
  initial_sold_quantity INT UNSIGNED  NOT NULL DEFAULT 0,
  current_quantity      INT UNSIGNED  NOT NULL DEFAULT 0,
  created_at            DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT chk_qty_positive CHECK (current_quantity >= 0),
  FOREIGN KEY (deposit_id) REFERENCES deposits(id) ON DELETE RESTRICT,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT,
  INDEX idx_di_deposit   (deposit_id),
  INDEX idx_di_product   (product_id),
  INDEX idx_di_stock     (current_quantity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── Mouvements de stock ────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS stock_movements (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  deposit_item_id INT UNSIGNED NOT NULL,
  type            ENUM('sale','restock','adjustment') NOT NULL,
  quantity        INT          NOT NULL,       -- négatif pour les ventes
  occurred_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  user_id         INT UNSIGNED NULL,
  note            TEXT         NULL,
  FOREIGN KEY (deposit_item_id) REFERENCES deposit_items(id) ON DELETE RESTRICT,
  FOREIGN KEY (user_id)         REFERENCES users(id)         ON DELETE SET NULL,
  INDEX idx_sm_item (deposit_item_id),
  INDEX idx_sm_date (occurred_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── Ventes (en-tête) ───────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS sales (
  id               INT UNSIGNED   AUTO_INCREMENT PRIMARY KEY,
  partner_id       INT UNSIGNED   NOT NULL,
  sold_by_user_id  INT UNSIGNED   NULL,
  sold_at          DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  total_amount     DECIMAL(10,2)  NOT NULL DEFAULT 0.00,
  commission_total DECIMAL(10,2)  NOT NULL DEFAULT 0.00,
  status           ENUM('completed','cancelled') NOT NULL DEFAULT 'completed',
  created_at       DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (partner_id)      REFERENCES partners(id) ON DELETE RESTRICT,
  FOREIGN KEY (sold_by_user_id) REFERENCES users(id)    ON DELETE SET NULL,
  INDEX idx_sales_partner (partner_id),
  INDEX idx_sales_date    (sold_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── Lignes de vente (snapshots immuables) ──────────────────────────
CREATE TABLE IF NOT EXISTS sale_items (
  id                          INT UNSIGNED   AUTO_INCREMENT PRIMARY KEY,
  sale_id                     INT UNSIGNED   NOT NULL,
  deposit_item_id             INT UNSIGNED   NULL,
  product_id                  INT UNSIGNED   NULL,
  product_reference_snapshot  VARCHAR(50)    NOT NULL,
  product_name_snapshot       VARCHAR(150)   NOT NULL,
  color_snapshot              VARCHAR(50)    NOT NULL,
  quantity                    INT UNSIGNED   NOT NULL DEFAULT 1,
  unit_price_snapshot         DECIMAL(10,2)  NOT NULL,
  commission_type_snapshot    VARCHAR(20)    NOT NULL,
  commission_value_snapshot   DECIMAL(10,2)  NULL,
  commission_amount_snapshot  DECIMAL(10,2)  NOT NULL DEFAULT 0.00,
  line_total                  DECIMAL(10,2)  NOT NULL,
  created_at                  DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (sale_id)         REFERENCES sales(id)         ON DELETE RESTRICT,
  FOREIGN KEY (deposit_item_id) REFERENCES deposit_items(id) ON DELETE SET NULL,
  FOREIGN KEY (product_id)      REFERENCES products(id)      ON DELETE SET NULL,
  INDEX idx_si_sale    (sale_id),
  INDEX idx_si_partner (sale_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;