-- =========================================================
-- Inventory Management System + Delivery Tracker (MySQL/MariaDB)
-- File: database.sql (COMPILED / UPDATED)
-- =========================================================

-- Create database
CREATE DATABASE IF NOT EXISTS ims_delivery
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE ims_delivery;

-- Safety: disable FK checks while dropping/creating
SET FOREIGN_KEY_CHECKS = 0;

-- =========================================================
-- DROP TABLES (order matters)
-- =========================================================
DROP TABLE IF EXISTS audit_logs;
DROP TABLE IF EXISTS delivery_locations;
DROP TABLE IF EXISTS delivery_items;
DROP TABLE IF EXISTS deliveries;
DROP TABLE IF EXISTS inventory_movements;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS product_categories;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS suppliers;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS roles;

-- =========================================================
-- 1) Roles & Users
-- =========================================================
CREATE TABLE roles (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  role_id INT UNSIGNED NOT NULL,
  full_name VARCHAR(120) NOT NULL,
  username VARCHAR(60) NOT NULL UNIQUE,
  email VARCHAR(120) NULL UNIQUE,
  phone VARCHAR(40) NULL,
  password_hash VARCHAR(255) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB;

-- Seed roles
INSERT INTO roles (name) VALUES
('admin'),
('staff'),
('driver');

-- =========================================================
-- 2) Suppliers & Customers
-- =========================================================
CREATE TABLE suppliers (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

  -- updated fields used by UI
  supplier_code VARCHAR(50) NULL,
  name VARCHAR(150) NOT NULL,
  city VARCHAR(120) NULL,
  country VARCHAR(120) NULL,

  -- optional details (we display these in supplier_view.php)
  contact_person VARCHAR(120) NULL,
  phone VARCHAR(40) NULL,
  email VARCHAR(120) NULL,
  address TEXT NULL,

  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  UNIQUE KEY uq_supplier_code (supplier_code),
  INDEX idx_suppliers_name (name),
  INDEX idx_suppliers_active (is_active)
) ENGINE=InnoDB;

CREATE TABLE customers (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  phone VARCHAR(40) NULL,
  email VARCHAR(120) NULL,
  address TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_customers_name (name)
) ENGINE=InnoDB;

-- =========================================================
-- 3) Products, Categories, Inventory Movements
-- =========================================================
CREATE TABLE product_categories (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE products (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  category_id INT UNSIGNED NULL,

  -- supplier integration (NEW)
  supplier_id INT UNSIGNED NULL,

  sku VARCHAR(60) NOT NULL UNIQUE,
  name VARCHAR(200) NOT NULL,
  description TEXT NULL,
  unit VARCHAR(30) NOT NULL DEFAULT 'pcs',
  cost_price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  sell_price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  reorder_level INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

  CONSTRAINT fk_products_category FOREIGN KEY (category_id)
    REFERENCES product_categories(id) ON DELETE SET NULL,

  CONSTRAINT fk_products_supplier FOREIGN KEY (supplier_id)
    REFERENCES suppliers(id) ON DELETE SET NULL ON UPDATE CASCADE,

  INDEX idx_products_category (category_id),
  INDEX idx_products_supplier (supplier_id),
  INDEX idx_products_active (is_active)
) ENGINE=InnoDB;

-- Every stock change goes here (IN / OUT / ADJUST)
CREATE TABLE inventory_movements (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  product_id INT UNSIGNED NOT NULL,
  movement_type ENUM('IN','OUT','ADJUST') NOT NULL,
  quantity INT NOT NULL,
  unit_cost DECIMAL(12,2) NULL,
  reference_type ENUM('PURCHASE','SALE','DELIVERY','MANUAL') NOT NULL DEFAULT 'MANUAL',
  reference_id BIGINT UNSIGNED NULL,
  notes VARCHAR(255) NULL,
  created_by INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_movements_product FOREIGN KEY (product_id) REFERENCES products(id),
  CONSTRAINT fk_movements_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,

  INDEX idx_movements_product_date (product_id, created_at),
  INDEX idx_movements_ref (reference_type, reference_id)
) ENGINE=InnoDB;

-- =========================================================
-- 4) Deliveries + Items + GPS Locations (Tracking)
-- =========================================================
CREATE TABLE deliveries (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

  delivery_code VARCHAR(50) NOT NULL UNIQUE,

  customer_id INT UNSIGNED NULL,
  assigned_driver_id INT UNSIGNED NULL,

  status ENUM('PENDING','ASSIGNED','PICKED_UP','IN_TRANSIT','DELIVERED','CANCELLED')
    NOT NULL DEFAULT 'PENDING',

  pickup_address TEXT NULL,
  dropoff_address TEXT NULL,

  -- Coordinates for mapping
  pickup_lat DECIMAL(10,7) NULL,
  pickup_lng DECIMAL(10,7) NULL,
  dropoff_lat DECIMAL(10,7) NULL,
  dropoff_lng DECIMAL(10,7) NULL,

  notes VARCHAR(255) NULL,

  scheduled_at DATETIME NULL,
  picked_up_at DATETIME NULL,
  delivered_at DATETIME NULL,

  created_by INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_deliveries_customer FOREIGN KEY (customer_id)
    REFERENCES customers(id) ON DELETE SET NULL,
  CONSTRAINT fk_deliveries_driver FOREIGN KEY (assigned_driver_id)
    REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_deliveries_created_by FOREIGN KEY (created_by)
    REFERENCES users(id) ON DELETE SET NULL,

  INDEX idx_deliveries_status (status),
  INDEX idx_deliveries_driver (assigned_driver_id),
  INDEX idx_deliveries_created_at (created_at)
) ENGINE=InnoDB;

CREATE TABLE delivery_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  delivery_id BIGINT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  quantity INT NOT NULL,

  -- optional snapshot pricing
  unit_price DECIMAL(12,2) NULL,

  CONSTRAINT fk_delivery_items_delivery FOREIGN KEY (delivery_id)
    REFERENCES deliveries(id) ON DELETE CASCADE,
  CONSTRAINT fk_delivery_items_product FOREIGN KEY (product_id)
    REFERENCES products(id),

  UNIQUE KEY uniq_delivery_product (delivery_id, product_id),
  INDEX idx_delivery_items_delivery (delivery_id)
) ENGINE=InnoDB;

-- GPS history for a delivery (driver sends location every X seconds)
CREATE TABLE delivery_locations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  delivery_id BIGINT UNSIGNED NOT NULL,
  driver_id INT UNSIGNED NULL,

  lat DECIMAL(10,7) NOT NULL,
  lng DECIMAL(10,7) NOT NULL,
  accuracy_m DECIMAL(10,2) NULL,
  speed_mps DECIMAL(10,2) NULL,
  heading_deg DECIMAL(10,2) NULL,

  recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_locations_delivery FOREIGN KEY (delivery_id)
    REFERENCES deliveries(id) ON DELETE CASCADE,
  CONSTRAINT fk_locations_driver FOREIGN KEY (driver_id)
    REFERENCES users(id) ON DELETE SET NULL,

  INDEX idx_locations_delivery_time (delivery_id, recorded_at),
  INDEX idx_locations_driver_time (driver_id, recorded_at)
) ENGINE=InnoDB;

-- =========================================================
-- 5) Audit Logs
-- =========================================================
CREATE TABLE audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NULL,
  action VARCHAR(80) NOT NULL,
  entity VARCHAR(80) NOT NULL,
  entity_id VARCHAR(80) NULL,
  details TEXT NULL,
  ip_address VARCHAR(64) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_audit_user FOREIGN KEY (user_id)
    REFERENCES users(id) ON DELETE SET NULL,

  INDEX idx_audit_user_date (user_id, created_at),
  INDEX idx_audit_entity (entity, entity_id)
) ENGINE=InnoDB;

-- Re-enable FK checks
SET FOREIGN_KEY_CHECKS = 1;

-- =========================================================
-- Optional: create a default admin user
-- IMPORTANT: Replace password_hash with a real PHP password_hash() output.
-- Example in PHP: password_hash("admin123", PASSWORD_DEFAULT)
-- =========================================================
INSERT INTO users (role_id, full_name, username, email, password_hash)
VALUES (
  (SELECT id FROM roles WHERE name='admin' LIMIT 1),
  'System Admin',
  'admin',
  'admin@example.com',
  '$2y$10$REPLACE_THIS_WITH_REAL_HASH........................................'
);