-- Smart Project Database Schema

CREATE DATABASE IF NOT EXISTS smart_project CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE smart_project;

-- Projects table (created first to avoid circular dependency)
CREATE TABLE IF NOT EXISTS projects (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  created_by INT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_created_by (created_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Users table
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(512) NOT NULL,
  user_type ENUM('platform', 'project') NOT NULL DEFAULT 'project',
  role VARCHAR(32) NOT NULL,
  real_name VARCHAR(128) NULL,
  phone VARCHAR(32) NULL,
  project_id INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  INDEX idx_username (username),
  INDEX idx_project_id (project_id),
  INDEX idx_user_type (user_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add foreign key constraint to projects after users table exists
ALTER TABLE projects 
  ADD CONSTRAINT fk_projects_created_by 
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL;

-- OSS Files table
CREATE TABLE IF NOT EXISTS oss_files (
  id INT AUTO_INCREMENT PRIMARY KEY,
  project_id INT NOT NULL,
  file_key VARCHAR(500) NOT NULL,
  file_name VARCHAR(255) NOT NULL,
  file_size BIGINT,
  mime_type VARCHAR(100),
  uploaded_by INT NOT NULL,
  uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_project_id (project_id),
  INDEX idx_uploaded_by (uploaded_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Hazards table
CREATE TABLE IF NOT EXISTS hazards (
  id VARCHAR(36) PRIMARY KEY,
  project_id INT NOT NULL,
  category VARCHAR(100) NOT NULL,
  severity ENUM('general', 'serious', 'major') NOT NULL,
  description TEXT NOT NULL,
  photos JSON,
  gps_lat DECIMAL(10, 8),
  gps_lng DECIMAL(11, 8),
  status ENUM('pending', 'assigned', 'resolved') DEFAULT 'pending',
  reporter_id INT NOT NULL,
  reporter_role VARCHAR(32) NOT NULL,
  reported_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  assignee_id INT,
  assignee_role VARCHAR(32),
  assigned_at TIMESTAMP NULL DEFAULT NULL,
  resolver_id INT,
  resolver_role VARCHAR(32),
  resolve_note TEXT,
  resolved_at TIMESTAMP NULL DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  FOREIGN KEY (reporter_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (assignee_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (resolver_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_project_id (project_id),
  INDEX idx_status (status),
  INDEX idx_severity (severity),
  INDEX idx_reported_at (reported_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Construction logs table
CREATE TABLE IF NOT EXISTS construction_logs (
  id VARCHAR(64) PRIMARY KEY,
  project_id INT NOT NULL,
  date DATE NOT NULL,
  part VARCHAR(128) NOT NULL,
  content TEXT NOT NULL,
  workers INT NOT NULL,
  weather VARCHAR(32),
  equipment JSON,
  photos JSON,
  safety_note TEXT,
  note TEXT,
  author_id INT NOT NULL,
  author_role VARCHAR(32) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_logs_project_id (project_id),
  INDEX idx_logs_date (date DESC),
  INDEX idx_logs_author_id (author_id),
  INDEX idx_logs_created_at (created_at DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;