

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;
