SET NAMES utf8mb4;

CREATE TABLE users (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  nom VARCHAR(100) NOT NULL,
  prenom VARCHAR(100) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  telephone VARCHAR(30) NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('client','admin','partenaire') NOT NULL DEFAULT 'client',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE parcels (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,

  recipient_nom VARCHAR(100) NOT NULL,
  recipient_tel VARCHAR(30) NOT NULL,
  recipient_adresse VARCHAR(255) NOT NULL,
  recipient_pays VARCHAR(100) NOT NULL,
  recipient_ville VARCHAR(100) NOT NULL,

  type_colis VARCHAR(50) NOT NULL,
  poids DECIMAL(10,2) NOT NULL,
  valeur_declaree DECIMAL(10,2) NULL,

  mode_transport ENUM('standard','express','economique') NOT NULL DEFAULT 'standard',
  prix DECIMAL(10,2) NOT NULL DEFAULT 0.00,

  paiement_statut ENUM('pending','paid','failed') NOT NULL DEFAULT 'pending',

  tracking_number VARCHAR(50) NOT NULL UNIQUE,
  status ENUM('registered','picked_up','transit','arrived_country','out_for_delivery','delivered','incident')
         NOT NULL DEFAULT 'registered',

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_parcels_user (user_id),
  INDEX idx_parcels_status (status),

  CONSTRAINT fk_parcels_user FOREIGN KEY (user_id)
    REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE tracking_events (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  parcel_id BIGINT UNSIGNED NOT NULL,

  status ENUM('registered','picked_up','transit','arrived_country','out_for_delivery','delivered','incident')
         NOT NULL,

  lieu VARCHAR(190) NULL,
  commentaire VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_tracking_parcel (parcel_id, created_at),

  CONSTRAINT fk_tracking_parcel FOREIGN KEY (parcel_id)
    REFERENCES parcels(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE payments (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  parcel_id BIGINT UNSIGNED NOT NULL,

  montant DECIMAL(10,2) NOT NULL,
  moyen VARCHAR(30) NOT NULL DEFAULT 'manual',
  statut ENUM('pending','paid','failed') NOT NULL DEFAULT 'pending',
  transaction_id VARCHAR(190) NULL,

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_payments_parcel (parcel_id),
  CONSTRAINT fk_payments_parcel FOREIGN KEY (parcel_id)
    REFERENCES parcels(id) ON DELETE CASCADE
) ENGINE=InnoDB;
