-- 350_devis.sql
USE blitech_facturation_dev;

CREATE TABLE devis (
  id            CHAR(36)     NOT NULL,
  id_entite     CHAR(36)     NOT NULL,
  id_client     CHAR(36)     NOT NULL,
  id_contact    CHAR(36)     DEFAULT NULL,

  no_devis      VARCHAR(50)  NOT NULL,      -- ex: 'DEV_2025-001' ou '25-001'
  date_devis    DATE         NOT NULL,
  date_validite DATE         DEFAULT NULL,  -- ex: date_devis + 30 jours

  statut        VARCHAR(20)  NOT NULL DEFAULT 'brouillon',
  -- ex: 'brouillon', 'envoye', 'accepte', 'refuse', 'expire'

  commentaire   TEXT         DEFAULT NULL,

  date_creation DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (id),

  CONSTRAINT fk_devis_entite
    FOREIGN KEY (id_entite) REFERENCES entites(id)
      ON DELETE RESTRICT ON UPDATE CASCADE,

  CONSTRAINT fk_devis_client
    FOREIGN KEY (id_client) REFERENCES clients(id)
      ON DELETE RESTRICT ON UPDATE CASCADE,

  CONSTRAINT fk_devis_contact
    FOREIGN KEY (id_contact) REFERENCES contacts(id)
      ON DELETE SET NULL ON UPDATE CASCADE,

  UNIQUE KEY uniq_devis_entite_num (id_entite, no_devis)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;
