-- =========================================================
-- IMS Delivery Tracker - NON-DESTRUCTIVE PATCH
-- Will NOT drop tables. Safe to run on an existing DB.
-- =========================================================

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

USE ims_delivery;

SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------
-- helper procedure: add column if missing
-- ---------------------------
DROP PROCEDURE IF EXISTS add_column_if_missing;
DELIMITER $$
CREATE PROCEDURE add_column_if_missing(
  IN p_table VARCHAR(64),
  IN p_column VARCHAR(64),
  IN p_definition TEXT
)
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = p_table
      AND COLUMN_NAME = p_column
  ) THEN
    SET @sql = CONCAT('ALTER TABLE `', p_table, '` ADD COLUMN ', p_definition);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END$$
DELIMITER ;

-- ---------------------------
-- helper procedure: add index if missing
-- ---------------------------
DROP PROCEDURE IF EXISTS add_index_if_missing;
DELIMITER $$
CREATE PROCEDURE add_index_if_missing(
  IN p_table VARCHAR(64),
  IN p_index VARCHAR(64),
  IN p_sql TEXT
)
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = p_table
      AND INDEX_NAME = p_index
  ) THEN
    SET @sql = p_sql;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END$$
DELIMITER ;

-- =========================================================
-- Tables (create only if missing)
-- =========================================================

CREATE TABLE IF NOT EXISTS roles (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS 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;

CREATE TABLE IF NOT EXISTS suppliers (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  supplier_code VARCHAR(50) NULL,
  name VARCHAR(150) NOT NULL,
  city VARCHAR(120) NULL,
  country VARCHAR(120) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  contact_person VARCHAR(120) NULL,
  phone VARCHAR(40) NULL,
  email VARCHAR(120) NULL,
  address TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS 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
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS product_categories (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS products (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  category_id INT UNSIGNED NULL,
  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
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS 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,
  INDEX idx_movements_product_date (product_id, created_at),
  INDEX idx_movements_ref (reference_type, reference_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS 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,
  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,
  INDEX idx_deliveries_status (status),
  INDEX idx_deliveries_driver (assigned_driver_id),
  INDEX idx_deliveries_created_at (created_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS 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,
  unit_price DECIMAL(12,2) NULL,
  UNIQUE KEY uniq_delivery_product (delivery_id, product_id),
  INDEX idx_delivery_items_delivery (delivery_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS 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,
  INDEX idx_locations_delivery_time (delivery_id, recorded_at),
  INDEX idx_locations_driver_time (driver_id, recorded_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS 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,
  INDEX idx_audit_user_date (user_id, created_at),
  INDEX idx_audit_entity (entity, entity_id)
) ENGINE=InnoDB;

-- =========================================================
-- Add missing columns safely (if your tables existed earlier)
-- =========================================================

CALL add_column_if_missing('suppliers','supplier_code','`supplier_code` VARCHAR(50) NULL AFTER `id`');
CALL add_column_if_missing('suppliers','city','`city` VARCHAR(120) NULL AFTER `name`');
CALL add_column_if_missing('suppliers','country','`country` VARCHAR(120) NULL AFTER `city`');
CALL add_column_if_missing('suppliers','is_active','`is_active` TINYINT(1) NOT NULL DEFAULT 1 AFTER `country`');

CALL add_column_if_missing('products','supplier_id','`supplier_id` INT UNSIGNED NULL AFTER `category_id`');

-- =========================================================
-- Add indexes safely
-- =========================================================
CALL add_index_if_missing('suppliers','uq_supplier_code','ALTER TABLE suppliers ADD UNIQUE KEY uq_supplier_code (supplier_code)');
CALL add_index_if_missing('suppliers','idx_suppliers_name','ALTER TABLE suppliers ADD INDEX idx_suppliers_name (name)');
CALL add_index_if_missing('suppliers','idx_suppliers_active','ALTER TABLE suppliers ADD INDEX idx_suppliers_active (is_active)');

CALL add_index_if_missing('products','idx_products_supplier','ALTER TABLE products ADD INDEX idx_products_supplier (supplier_id)');

-- =========================================================
-- Add foreign keys if missing (best-effort)
-- NOTE: MySQL doesn't support IF NOT EXISTS for FK,
-- so only run these if you confirm they don't exist.
-- =========================================================

-- Products -> Suppliers FK (uncomment if missing)
-- ALTER TABLE products
--   ADD CONSTRAINT fk_products_supplier
--   FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
--   ON DELETE SET NULL
--   ON UPDATE CASCADE;

SET FOREIGN_KEY_CHECKS = 1;