-- Schema without CREATE DATABASE (for limited privilege users)
-- Usage:
--   1. Ensure the target database already exists (created by admin or hosting panel)
--   2. Import with:  mysql -u <user> -p <your_db_name> < schema_no_create.sql
--   3. Or uncomment the USE line below if your import environment allows it.

-- OPTIONAL: set active database (uncomment and adjust if needed)
-- USE `inventory`;

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  role ENUM('admin','staff') NOT NULL DEFAULT 'staff',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  phone VARCHAR(20),
  membership_no VARCHAR(50),
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX (phone),
  INDEX (membership_no)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS storages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  brand VARCHAR(100) NOT NULL,
  quantity INT NOT NULL,
  store_date DATE NOT NULL,
  expire_date DATE,
  status ENUM('stored','taken','expired') NOT NULL DEFAULT 'stored',
  is_spirit TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_storage_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS transactions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  storage_id INT NOT NULL,
  staff_id INT NOT NULL,
  action ENUM('store','take','update') NOT NULL,
  quantity INT NOT NULL,
  action_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_tx_storage FOREIGN KEY (storage_id) REFERENCES storages(id) ON DELETE CASCADE,
  CONSTRAINT fk_tx_staff FOREIGN KEY (staff_id) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS customer_access (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  token VARCHAR(255) NOT NULL UNIQUE,
  expire_link DATETIME,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_access_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS system_config (
  id INT AUTO_INCREMENT PRIMARY KEY,
  `key` VARCHAR(50) NOT NULL UNIQUE,
  `value` TEXT NOT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Seed admin user only if not exists
INSERT INTO users (username,password,role)
SELECT 'admin', '$2y$10$4K4gAbXo8nP0mX1JvZCvHOi6uqQ3M4u9JD0sEzg4tLz0gQvF3nZxG', 'admin'
WHERE NOT EXISTS (SELECT 1 FROM users WHERE username='admin');

-- End of schema_no_create.sql