-- Step 29: Media equipment asset inventory and usage history.
-- Run after step28_cameraman_supervisor_access.sql.

CREATE TABLE IF NOT EXISTS media_equipment_assets (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  asset_code VARCHAR(80) NOT NULL,
  asset_name VARCHAR(160) NOT NULL,
  category VARCHAR(100) NOT NULL,
  brand_model VARCHAR(160) DEFAULT NULL,
  serial_no VARCHAR(160) DEFAULT NULL,
  purchase_date DATE DEFAULT NULL,
  purchase_session VARCHAR(120) DEFAULT NULL,
  purchase_cost DECIMAL(12,2) DEFAULT NULL,
  expiry_date DATE DEFAULT NULL,
  status ENUM('Available','Assigned','In Use','Repair','Damaged','Expired','Retired') NOT NULL DEFAULT 'Available',
  assigned_cameraman_id INT UNSIGNED DEFAULT NULL,
  current_location VARCHAR(160) DEFAULT NULL,
  notes TEXT DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_media_equipment_assets_code (asset_code),
  KEY idx_media_equipment_assets_status (status),
  KEY idx_media_equipment_assets_cameraman (assigned_cameraman_id),
  KEY idx_media_equipment_assets_expiry (expiry_date),
  CONSTRAINT fk_media_equipment_assets_cameraman
    FOREIGN KEY (assigned_cameraman_id) REFERENCES cameramen(id)
    ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS media_equipment_asset_events (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  asset_id INT UNSIGNED NOT NULL,
  cameraman_id INT UNSIGNED DEFAULT NULL,
  event_type ENUM('Purchased','Assigned','Returned','In Use','Damaged','Repair Sent','Repair Returned','Expired','Retired','Note') NOT NULL,
  event_date DATE NOT NULL,
  session_info VARCHAR(160) DEFAULT NULL,
  reason TEXT DEFAULT NULL,
  lab_info VARCHAR(180) DEFAULT NULL,
  repair_cost DECIMAL(12,2) DEFAULT NULL,
  created_by INT UNSIGNED DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_media_equipment_events_asset (asset_id, event_date),
  KEY idx_media_equipment_events_cameraman (cameraman_id, event_type),
  KEY idx_media_equipment_events_type_date (event_type, event_date),
  CONSTRAINT fk_media_equipment_events_asset
    FOREIGN KEY (asset_id) REFERENCES media_equipment_assets(id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_media_equipment_events_cameraman
    FOREIGN KEY (cameraman_id) REFERENCES cameramen(id)
    ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT fk_media_equipment_events_user
    FOREIGN KEY (created_by) REFERENCES users(id)
    ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO automation_rules (rule_key, rule_name, is_enabled, description, updated_at) VALUES
('equipment_asset_damaged', 'Equipment Asset Damaged', 1, 'Notify operations when a media equipment asset is marked damaged.', NOW()),
('equipment_asset_repair', 'Equipment Asset Repair', 1, 'Log repair events and repair costs for media equipment assets.', NOW()),
('equipment_asset_expired', 'Equipment Asset Expired', 1, 'Notify operations when a media equipment asset is marked expired.', NOW());