-- Schema database per GeoCantieri PHP demo

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  role ENUM('ADMIN','DL','CSE','ASSISTANT') NOT NULL DEFAULT 'DL',
  password_hash VARCHAR(255) NOT NULL,
  avatar VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sites (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  region VARCHAR(100),
  city VARCHAR(100),
  address VARCHAR(255),
  lat DECIMAL(10,7) NULL,
  lng DECIMAL(10,7) NULL,
  start_date DATE,
  end_date DATE,
  progress TINYINT UNSIGNED DEFAULT 0,
  status ENUM('PLANNED','ACTIVE','ON_HOLD','COMPLETED') NOT NULL DEFAULT 'PLANNED',
  manager VARCHAR(255),
  budget DECIMAL(15,2),
  description TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS reports (
  id INT AUTO_INCREMENT PRIMARY KEY,
  site_id INT NOT NULL,
  date DATE NOT NULL,
  type ENUM('SOPRALLUOGO','VERBALE_DL','VERBALE_CSE','ALTRO') NOT NULL,
  author VARCHAR(255) NOT NULL,
  content MEDIUMTEXT NOT NULL,
  weather VARCHAR(255),
  attendees TEXT,
  status ENUM('Draft','Signed','Sent') NOT NULL DEFAULT 'Draft',
  created_by INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_reports_site FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE,
  CONSTRAINT fk_reports_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Utente admin di esempio (password: admin123)
INSERT INTO users (name, email, role, password_hash)
VALUES ('Admin', 'admin@example.com', 'ADMIN',
        '$2y$10$xJQlzjo9Eu7mL/OhmkUzUe9v5d3Rdmj9FliLqosoHt.wRFkFUx.iC');