-- Cylinder Management DB Schema
CREATE DATABASE IF NOT EXISTS cylinder_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE cylinder_db;
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  role ENUM('admin','operator') NOT NULL DEFAULT 'operator',
  name VARCHAR(150) DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  contact VARCHAR(100),
  address TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS cylinders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  serial VARCHAR(100) UNIQUE NOT NULL,
  model VARCHAR(100) DEFAULT NULL,
  status ENUM('factory','assigned','returned','in_transit','maintenance') NOT NULL DEFAULT 'factory',
  current_location VARCHAR(255) DEFAULT 'Factory',
  assigned_to INT DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (assigned_to) REFERENCES customers(id) ON DELETE SET NULL
);
CREATE TABLE IF NOT EXISTS movements (
  id INT AUTO_INCREMENT PRIMARY KEY,
  cylinder_id INT NOT NULL,
  action ENUM('assign','return','transfer','maintenance') NOT NULL,
  from_location VARCHAR(255) NULL,
  to_location VARCHAR(255) NULL,
  customer_id INT NULL,
  note TEXT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (cylinder_id) REFERENCES cylinders(id) ON DELETE CASCADE,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
);
CREATE TABLE IF NOT EXISTS orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  order_date DATE NOT NULL,
  qty INT NOT NULL,
  note TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS order_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  cylinder_id INT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  FOREIGN KEY (cylinder_id) REFERENCES cylinders(id) ON DELETE CASCADE
);