-- Core PHP DHL Partner Agency database schema
-- Import with phpMyAdmin or MySQL CLI. Change database name if needed.
CREATE DATABASE IF NOT EXISTS agency_partner CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE agency_partner;

CREATE TABLE IF NOT EXISTS admin_users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  must_change_password TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS login_attempts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ip_address VARCHAR(45) NOT NULL,
  username_attempted VARCHAR(190) NOT NULL DEFAULT '',
  succeeded TINYINT(1) NOT NULL DEFAULT 0,
  attempted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_login_ip_time (ip_address, attempted_at),
  INDEX idx_login_username_time (username_attempted, attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS shipments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tracking_code VARCHAR(80) NOT NULL UNIQUE,
  recipient_name VARCHAR(190) NOT NULL,
  origin VARCHAR(190) DEFAULT NULL,
  destination VARCHAR(190) DEFAULT NULL,
  shipment_status VARCHAR(80) NOT NULL DEFAULT 'En préparation',
  estimated_delivery DATE DEFAULT NULL,
  last_location VARCHAR(190) DEFAULT NULL,
  details TEXT DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_shipments_status (shipment_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS shipment_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  shipment_id BIGINT UNSIGNED NOT NULL,
  event_date DATETIME NOT NULL,
  location VARCHAR(190) DEFAULT NULL,
  status VARCHAR(120) NOT NULL,
  description TEXT DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_events_shipment_date (shipment_id, event_date),
  CONSTRAINT fk_event_shipment FOREIGN KEY (shipment_id) REFERENCES shipments(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS shipment_display (
 shipment_id BIGINT UNSIGNED NOT NULL PRIMARY KEY, page_heading VARCHAR(190) DEFAULT 'Suivi de votre envoi', intro_text TEXT DEFAULT NULL,
 dispatched_date DATE DEFAULT NULL, delivered_at DATETIME DEFAULT NULL, timezone_label VARCHAR(40) DEFAULT 'UTC', active_step TINYINT UNSIGNED NOT NULL DEFAULT 1,
 pod_status VARCHAR(120) DEFAULT 'Preuve de livraison enregistrée', delivery_panel_title VARCHAR(190) DEFAULT 'STATUT DE VOTRE LIVRAISON', delivery_panel_message TEXT DEFAULT NULL,
 notification_title VARCHAR(190) DEFAULT 'Recevoir des notifications', notification_message TEXT DEFAULT NULL, support_title VARCHAR(190) DEFAULT 'Vous avez une question ?',
 support_message TEXT DEFAULT NULL, support_url VARCHAR(500) DEFAULT NULL, show_notifications TINYINT(1) NOT NULL DEFAULT 1, show_support TINYINT(1) NOT NULL DEFAULT 1,
 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_display_shipment FOREIGN KEY (shipment_id) REFERENCES shipments(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contact_messages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(190) NOT NULL,
  company VARCHAR(190) DEFAULT NULL,
  email VARCHAR(254) NOT NULL,
  phone VARCHAR(60) DEFAULT NULL,
  subject VARCHAR(190) NOT NULL,
  message TEXT NOT NULL,
  consent TINYINT(1) NOT NULL DEFAULT 0,
  ip_hash CHAR(64) DEFAULT NULL,
  status ENUM('nouveau','en_cours','traite','archive') NOT NULL DEFAULT 'nouveau',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_contact_created (created_at),
  INDEX idx_contact_status (status),
  INDEX idx_contact_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;