-- 120_clients.sql
USE blitech_facturation_dev;

CREATE TABLE clients (
  id            CHAR(36)     NOT NULL,
  id_entite     CHAR(36)     NOT NULL,

  no_client     VARCHAR(20)  NOT NULL,
  type_client   VARCHAR(20)  NOT NULL,      -- 'Particulier' / 'Professionnel'

  titre         VARCHAR(20)  DEFAULT NULL,
  prenom        VARCHAR(100) DEFAULT NULL,
  nom           VARCHAR(100) DEFAULT NULL,

  rcs           VARCHAR(150) DEFAULT NULL,
  siren_siret   VARCHAR(32)  DEFAULT NULL,
  raison_sociale VARCHAR(255) DEFAULT NULL,

  adresse       VARCHAR(255) DEFAULT NULL,
  cp            VARCHAR(10)  DEFAULT NULL,
  ville         VARCHAR(100) DEFAULT NULL,

  telephone     VARCHAR(30)  DEFAULT NULL,
  email         VARCHAR(255) DEFAULT NULL,

  remarques     TEXT         DEFAULT NULL,

  date_creation DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (id),

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

  UNIQUE KEY uniq_client_entite_num (id_entite, no_client)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;


START TRANSACTION;

-- Nettoyage : éviter les chaînes vides (on préfère NULL)
UPDATE clients
SET code_client = NULL
WHERE code_client IS NOT NULL AND TRIM(code_client) = '';

ALTER TABLE clients ADD COLUMN code_client VARCHAR(10) NOT NULL AFTER no_client;

/* Sociétés (raison_sociale non NULL) */
UPDATE clients SET code_client = 'QG'   WHERE raison_sociale = 'Quercus Group';
UPDATE clients SET code_client = 'MFI'  WHERE raison_sociale = 'Marketing Fi';
UPDATE clients SET code_client = 'EF'   WHERE raison_sociale = 'English Forever';
UPDATE clients SET code_client = 'GNCP' WHERE raison_sociale = 'Groupement National des Commerçants de Proximité';
UPDATE clients SET code_client = 'AC'   WHERE raison_sociale = 'Accio Courtage';
UPDATE clients SET code_client = 'TM'   WHERE raison_sociale = 'TM';
UPDATE clients SET code_client = 'FD'   WHERE raison_sociale = 'FRED LAB';
UPDATE clients SET code_client = 'RNS'  WHERE raison_sociale = 'Re-nez-sens';
UPDATE clients SET code_client = 'ML'   WHERE raison_sociale = 'mathildelamarre-naturopathe';
UPDATE clients SET code_client = 'IL'   WHERE raison_sociale = 'Inge Loire';

/* Particuliers (raison_sociale NULL) */
UPDATE clients SET code_client = 'DRM'
WHERE raison_sociale IS NULL AND nom = 'Roger-Machart' AND prenom = 'Denis';

UPDATE clients SET code_client = 'MA-GE'
WHERE raison_sociale IS NULL AND nom = 'Marion' AND prenom = 'Gérard';

UPDATE clients SET code_client = 'MI'
WHERE raison_sociale IS NULL AND nom = 'Inigo' AND prenom = 'Matthieu';

UPDATE clients SET code_client = 'VP'
WHERE raison_sociale IS NULL AND nom = 'Pichet' AND prenom = 'Véronique';

UPDATE clients SET code_client = 'DRN'
WHERE raison_sociale IS NULL AND nom = 'Roger-Machart' AND prenom = 'Nicolas';

COMMIT;
