-- Updated SQL schema including beneficiary, guarantors, and attachments
CREATE TABLE IF NOT EXISTS engagement (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ref VARCHAR(50) UNIQUE,
  nom VARCHAR(150),
  prenom VARCHAR(150),
  gerante_entreprise VARCHAR(200),
  date_naissance DATE,
  lieu_naissance VARCHAR(200),
  situation_matrimoniale VARCHAR(100),
  telephone VARCHAR(50),
  email VARCHAR(150),
  adresse TEXT,
  piece_id VARCHAR(100),
  piece_validite DATE,
  rccm VARCHAR(100),
  nif VARCHAR(100),
  forme_juridique VARCHAR(100),
  localisation_activite VARCHAR(200),
  secteur_activite VARCHAR(150),
  nb_employes INT DEFAULT 0,
  compte_bancaire VARCHAR(100),
  objet_credit TEXT,
  montant_credit DECIMAL(20,2),
  duree_credit INT,
  modalite_remboursement TEXT,
  utilisation_fonds TEXT,
  statut ENUM('nouveau','en_cours','accorde','refuse') DEFAULT 'nouveau',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS garant (
  id INT AUTO_INCREMENT PRIMARY KEY,
  engagement_id INT,
  nom VARCHAR(150),
  prenom VARCHAR(150),
  date_naissance DATE,
  lieu_naissance VARCHAR(200),
  profession VARCHAR(150),
  activite VARCHAR(200),
  adresse TEXT,
  telephone VARCHAR(50),
  email VARCHAR(150),
  piece_id VARCHAR(100),
  piece_validite DATE,
  signature_path VARCHAR(255),
  FOREIGN KEY (engagement_id) REFERENCES engagement(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS engagement_image (
  id INT AUTO_INCREMENT PRIMARY KEY,
  engagement_id INT,
  type VARCHAR(50),
  file_path VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (engagement_id) REFERENCES engagement(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS engagement_attachment (
  id INT AUTO_INCREMENT PRIMARY KEY,
  engagement_id INT,
  label VARCHAR(150),
  file_path VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (engagement_id) REFERENCES engagement(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS courrier (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ref VARCHAR(50) UNIQUE,
  sujet VARCHAR(255),
  expediteur VARCHAR(150),
  destinataire VARCHAR(150),
  statut VARCHAR(100),
  position_actuelle VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS courrier_history (
  id INT AUTO_INCREMENT PRIMARY KEY,
  courrier_id INT,
  statut VARCHAR(100),
  position VARCHAR(255),
  commentaire TEXT,
  change_by VARCHAR(150),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (courrier_id) REFERENCES courrier(id) ON DELETE CASCADE
);