

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;

-- End of schema_no_create.sql