CREATE DATABASE IF NOT EXISTS smart_order_management CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE smart_order_management;

CREATE TABLE users (
  user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  full_name VARCHAR(200) NOT NULL,
  role_name VARCHAR(50) NOT NULL DEFAULT 'Staff',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL
);

CREATE TABLE permissions (
  permission_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  permission_name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE user_permissions (
  user_id INT UNSIGNED NOT NULL,
  permission_id INT UNSIGNED NOT NULL,
  can_access TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (user_id, permission_id),
  CONSTRAINT fk_up_user FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
  CONSTRAINT fk_up_permission FOREIGN KEY (permission_id) REFERENCES permissions(permission_id) ON DELETE CASCADE
);

CREATE TABLE customers (
  customer_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_name VARCHAR(250) NOT NULL,
  address VARCHAR(500) NULL,
  phone VARCHAR(50) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  INDEX idx_customer_name(customer_name),
  INDEX idx_customer_phone(phone)
);

CREATE TABLE items (
  item_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  item_name VARCHAR(250) NOT NULL,
  item_type VARCHAR(100) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  INDEX idx_item_name(item_name)
);

CREATE TABLE orders (
  order_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_no VARCHAR(50) NOT NULL UNIQUE,
  customer_id INT UNSIGNED NOT NULL,
  order_date DATE NOT NULL,
  delivery_date DATE NULL,
  created_by_user_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_order_customer FOREIGN KEY(customer_id) REFERENCES customers(customer_id),
  CONSTRAINT fk_order_user FOREIGN KEY(created_by_user_id) REFERENCES users(user_id) ON DELETE SET NULL,
  INDEX idx_order_date(order_date),
  INDEX idx_delivery_date(delivery_date)
);

CREATE TABLE order_details (
  order_detail_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id INT UNSIGNED NOT NULL,
  item_id INT UNSIGNED NOT NULL,
  kg DECIMAL(18,2) NOT NULL DEFAULT 0,
  bag DECIMAL(18,2) NOT NULL DEFAULT 0,
  CONSTRAINT fk_detail_order FOREIGN KEY(order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
  CONSTRAINT fk_detail_item FOREIGN KEY(item_id) REFERENCES items(item_id)
);

CREATE TABLE system_settings (
  setting_key VARCHAR(100) PRIMARY KEY,
  setting_value TEXT NULL
);

CREATE TABLE backup_logs (
  backup_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  file_name VARCHAR(255) NOT NULL,
  backup_type VARCHAR(30) NOT NULL,
  created_by_user_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_backup_user FOREIGN KEY(created_by_user_id) REFERENCES users(user_id) ON DELETE SET NULL
);

INSERT INTO permissions(permission_name) VALUES
('Dashboard'),('New Order'),('Customer Registration'),('Item Registration'),
('Order Reprint'),('Reports'),('User Management'),('User Access'),
('Change Password'),('Backup'),('Restore'),('System Settings'),('Subscription Management');