CREATE DATABASE IF NOT EXISTS incidencias_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE incidencias_db;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(120) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('mantenimiento','encargado') NOT NULL DEFAULT 'mantenimiento',
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE reports (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(20) NOT NULL UNIQUE,
  title VARCHAR(160) NOT NULL,
  description TEXT NOT NULL,
  area VARCHAR(80) NOT NULL,
  category ENUM('infraestructura','electricidad','limpieza','seguridad','otros') NOT NULL DEFAULT 'otros',
  priority ENUM('baja','media','alta','critica') NOT NULL DEFAULT 'media',
  status ENUM('pendiente','en_proceso','resuelto','cancelado') NOT NULL DEFAULT 'pendiente',
  reported_by INT NOT NULL,
  assigned_to INT NULL,
  photo_path VARCHAR(255) NULL,
  manager_comment TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  resolved_at TIMESTAMP NULL,
  CONSTRAINT fk_report_user FOREIGN KEY (reported_by) REFERENCES users(id),
  CONSTRAINT fk_assigned_user FOREIGN KEY (assigned_to) REFERENCES users(id)
);

CREATE INDEX idx_reports_status ON reports(status);
CREATE INDEX idx_reports_area ON reports(area);
CREATE INDEX idx_reports_created ON reports(created_at);
