-- sql/schema.sql v1.1
CREATE DATABASE IF NOT EXISTS osman CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE osman;

CREATE TABLE IF NOT EXISTS equipments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  code VARCHAR(60) DEFAULT NULL,
  hour_meter INT NOT NULL DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS parts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sku VARCHAR(60) DEFAULT NULL,
  name VARCHAR(160) NOT NULL,
  unit VARCHAR(20) NOT NULL DEFAULT 'un',
  min_stock INT NOT NULL DEFAULT 0,
  stock_qty DECIMAL(12,2) NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS maintenance_plans (
  id INT AUTO_INCREMENT PRIMARY KEY,
  equipment_id INT NOT NULL,
  part_id INT DEFAULT NULL,
  description VARCHAR(255) DEFAULT NULL,
  interval_hours INT NOT NULL,
  next_due_at_hours INT NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  FOREIGN KEY (equipment_id) REFERENCES equipments(id) ON DELETE CASCADE,
  FOREIGN KEY (part_id) REFERENCES parts(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS work_orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  equipment_id INT NOT NULL,
  opened_at DATETIME NOT NULL,
  opened_by VARCHAR(120) DEFAULT NULL,
  description TEXT NOT NULL,
  priority ENUM('NORMAL','URGENTE','EMERGENCIAL') NOT NULL DEFAULT 'NORMAL',
  status ENUM('open','in_progress','closed') NOT NULL DEFAULT 'open',
  start_meter INT NOT NULL DEFAULT 0,
  end_meter INT DEFAULT NULL,
  closed_at DATETIME DEFAULT NULL,
  notes TEXT DEFAULT NULL,
  FOREIGN KEY (equipment_id) REFERENCES equipments(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS work_order_parts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  work_order_id INT NOT NULL,
  part_id INT NOT NULL,
  qty DECIMAL(12,2) NOT NULL,
  FOREIGN KEY (work_order_id) REFERENCES work_orders(id) ON DELETE CASCADE,
  FOREIGN KEY (part_id) REFERENCES parts(id) ON DELETE RESTRICT
);

CREATE TABLE IF NOT EXISTS equipment_meters_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  equipment_id INT NOT NULL,
  reading_at DATETIME NOT NULL,
  meter INT NOT NULL,
  source ENUM('manual','os_close') NOT NULL DEFAULT 'manual',
  FOREIGN KEY (equipment_id) REFERENCES equipments(id) ON DELETE CASCADE
);

CREATE INDEX idx_worders_equipment ON work_orders(equipment_id);
CREATE INDEX idx_worders_status ON work_orders(status);
CREATE INDEX idx_mplans_equipment ON maintenance_plans(equipment_id);
