-- 300_abonnements.sql
USE blitech_facturation_dev;

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

  libelle      VARCHAR(255) NOT NULL,
  description  TEXT         DEFAULT NULL,

  periodicite  VARCHAR(20)  NOT NULL,  -- 'mensuelle', ...
  date_debut   DATE         NOT NULL,
  date_fin     DATE         DEFAULT NULL,
  nb_occurrences INT        DEFAULT NULL,

  actif        TINYINT(1)   NOT NULL DEFAULT 1,
  date_creation DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (id),

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

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

  CONSTRAINT fk_abonnements_contact
    FOREIGN KEY (id_contact) REFERENCES contacts(id)
      ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;
