-- Schema for Cylinder Management
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  address VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS cylinders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  serial VARCHAR(100) UNIQUE NOT NULL,
  status ENUM('available','assigned') NOT NULL DEFAULT 'available',
  location VARCHAR(100) DEFAULT 'factory',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS movement_groups (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT,
  action ENUM('assign','return') NOT NULL,
  movement_date DATE,
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS movement_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  group_id INT,
  cylinder_id INT,
  FOREIGN KEY (group_id) REFERENCES movement_groups(id) ON DELETE CASCADE,
  FOREIGN KEY (cylinder_id) REFERENCES cylinders(id) ON DELETE CASCADE
);