-- SUPERFAX HELPDESK ENTERPRISE
-- Upgrade V4 - Painel Operacional do Prontuário do Equipamento
-- Execute no banco suporte pelo phpMyAdmin.

ALTER TABLE equipment
  ADD COLUMN IF NOT EXISTS photo_path VARCHAR(255) NULL AFTER contract_id,
  ADD COLUMN IF NOT EXISTS hostname VARCHAR(120) NULL AFTER mac_address,
  ADD COLUMN IF NOT EXISTS gateway VARCHAR(120) NULL AFTER hostname,
  ADD COLUMN IF NOT EXISTS subnet_mask VARCHAR(80) NULL AFTER gateway,
  ADD COLUMN IF NOT EXISTS firmware_version VARCHAR(120) NULL AFTER subnet_mask,
  ADD COLUMN IF NOT EXISTS acquisition_date DATE NULL AFTER firmware_version,
  ADD COLUMN IF NOT EXISTS expected_life_pages INT NULL AFTER acquisition_date,
  ADD COLUMN IF NOT EXISTS monthly_allowance_bw INT NULL AFTER expected_life_pages,
  ADD COLUMN IF NOT EXISTS monthly_allowance_color INT NULL AFTER monthly_allowance_bw,
  ADD COLUMN IF NOT EXISTS monthly_contract_value DECIMAL(10,2) NULL AFTER monthly_allowance_color,
  ADD COLUMN IF NOT EXISTS responsible_technician VARCHAR(160) NULL AFTER monthly_contract_value,
  ADD COLUMN IF NOT EXISTS next_preventive_at DATE NULL AFTER responsible_technician,
  ADD COLUMN IF NOT EXISTS notes TEXT NULL AFTER next_preventive_at;

CREATE TABLE IF NOT EXISTS equipment_counter_history (
  id INT AUTO_INCREMENT PRIMARY KEY,
  equipment_id INT NOT NULL,
  counter_bw INT NOT NULL DEFAULT 0,
  counter_color INT NOT NULL DEFAULT 0,
  reading_date DATE NOT NULL,
  notes TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_equipment_counter_equipment (equipment_id),
  INDEX idx_equipment_counter_date (reading_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS equipment_parts_history (
  id INT AUTO_INCREMENT PRIMARY KEY,
  equipment_id INT NOT NULL,
  part_name VARCHAR(160) NOT NULL,
  serial_number VARCHAR(120) NULL,
  part_code VARCHAR(80) NULL,
  quantity INT NOT NULL DEFAULT 1,
  warranty_until DATE NULL,
  technician_name VARCHAR(160) NULL,
  cost DECIMAL(10,2) NULL,
  notes TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_equipment_parts_equipment (equipment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS equipment_photos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  equipment_id INT NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  category VARCHAR(80) NULL,
  caption VARCHAR(160) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_equipment_photos_equipment (equipment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS equipment_operational_events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  equipment_id INT NOT NULL,
  event_type VARCHAR(80) NOT NULL,
  title VARCHAR(180) NOT NULL,
  description TEXT NULL,
  event_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  user_name VARCHAR(160) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_equipment_events_equipment (equipment_id),
  INDEX idx_equipment_events_date (event_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE maintenance_history
  ADD COLUMN IF NOT EXISTS technician_name VARCHAR(160) NULL AFTER description,
  ADD COLUMN IF NOT EXISTS cost DECIMAL(10,2) NULL AFTER technician_name;

ALTER TABLE toner_history
  ADD COLUMN IF NOT EXISTS notes TEXT NULL AFTER action;