-- Minimal schema for parcel tracking
CREATE DATABASE IF NOT EXISTS parcel_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE parcel_db;

CREATE TABLE admins (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  full_name VARCHAR(100) DEFAULT NULL,
  role ENUM('super','staff') DEFAULT 'staff',
  force_password_change TINYINT(1) DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE parcels (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tracking_number VARCHAR(100) NOT NULL UNIQUE,
  receiver_name VARCHAR(255) DEFAULT NULL,
  sender_name VARCHAR(255) DEFAULT NULL,
  received_image VARCHAR(255) DEFAULT NULL,
  proof_image VARCHAR(255) DEFAULT NULL,
  status ENUM('received','in_storage','ready_pickup','collected') DEFAULT 'received',
  created_by VARCHAR(100) DEFAULT NULL,
  updated_by VARCHAR(100) DEFAULT NULL,
  date_received DATETIME DEFAULT CURRENT_TIMESTAMP,
  date_collected DATETIME DEFAULT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE parcel_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  parcel_id INT NOT NULL,
  status VARCHAR(100) NOT NULL,
  note TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (parcel_id) REFERENCES parcels(id) ON DELETE CASCADE
);