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

CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  google_id VARCHAR(191) NOT NULL UNIQUE,
  email VARCHAR(191) NOT NULL UNIQUE,
  company_number VARCHAR(50) NULL,
  name VARCHAR(191) NOT NULL,
  avatar_url VARCHAR(500) NULL,
  role ENUM('admin','trainer','trainee') NOT NULL DEFAULT 'trainee',
  password_hash VARCHAR(255) NULL,
  created_by INT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_users_company_role (company_number, role)
) ENGINE=InnoDB;

CREATE TABLE courses (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  trainer_id INT UNSIGNED NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  location_type ENUM('inside_iraq','outside_iraq') NOT NULL DEFAULT 'inside_iraq',
  share_token CHAR(36) NOT NULL UNIQUE,
  status ENUM('active','closed') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_courses_trainer FOREIGN KEY (trainer_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE course_fields (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  course_id INT UNSIGNED NOT NULL,
  field_key VARCHAR(80) NOT NULL,
  field_label VARCHAR(191) NOT NULL,
  field_type ENUM('text','number','date','textarea') NOT NULL DEFAULT 'text',
  is_required TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  CONSTRAINT fk_fields_course FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE enrollments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  course_id INT UNSIGNED NOT NULL,
  trainee_user_id INT UNSIGNED NOT NULL,
  full_name VARCHAR(191) NOT NULL,
  company_number VARCHAR(100) NOT NULL,
  job_title VARCHAR(191) NOT NULL,
  formation VARCHAR(191) NULL,
  extra_data JSON NULL,
  enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY unique_enrollment (course_id, trainee_user_id),
  CONSTRAINT fk_enroll_course FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
  CONSTRAINT fk_enroll_user FOREIGN KEY (trainee_user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE attendance (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  course_id INT UNSIGNED NOT NULL,
  enrollment_id INT UNSIGNED NOT NULL,
  attendance_date DATE NOT NULL,
  status ENUM('present','absent') NOT NULL DEFAULT 'present',
  notes VARCHAR(500) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY unique_attendance (enrollment_id, attendance_date),
  CONSTRAINT fk_att_course FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
  CONSTRAINT fk_att_enrollment FOREIGN KEY (enrollment_id) REFERENCES enrollments(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE activity_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  actor_user_id INT UNSIGNED NULL,
  actor_name VARCHAR(191) NULL,
  actor_role VARCHAR(30) NULL,
  action_key VARCHAR(80) NOT NULL,
  action_label VARCHAR(255) NOT NULL,
  entity_type VARCHAR(80) NULL,
  entity_id INT UNSIGNED NULL,
  details TEXT NULL,
  ip_address VARCHAR(45) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_activity_created (created_at),
  INDEX idx_activity_actor (actor_user_id),
  CONSTRAINT fk_activity_actor FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE course_materials (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  course_id INT UNSIGNED NOT NULL,
  material_type ENUM('attachment','guidance','exam') NOT NULL,
  title VARCHAR(255) NOT NULL,
  content TEXT NULL,
  file_path VARCHAR(500) NULL,
  file_name VARCHAR(255) NULL,
  file_mime VARCHAR(120) NULL,
  file_size INT UNSIGNED NULL,
  created_by INT UNSIGNED NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_mat_course FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
  CONSTRAINT fk_mat_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_mat_course (course_id)
) ENGINE=InnoDB;

CREATE TABLE employees (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_number VARCHAR(50) NOT NULL UNIQUE,
  full_name VARCHAR(255) NOT NULL,
  job_title VARCHAR(191) NULL,
  formation VARCHAR(255) NULL,
  department VARCHAR(255) NULL,
  education VARCHAR(191) NULL,
  position_title VARCHAR(255) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_emp_name (full_name)
) ENGINE=InnoDB;

CREATE TABLE permissions (
  perm_key VARCHAR(80) PRIMARY KEY,
  label VARCHAR(191) NOT NULL,
  group_name VARCHAR(80) NOT NULL DEFAULT 'عام'
) ENGINE=InnoDB;

CREATE TABLE role_permissions (
  role ENUM('admin','trainer','trainee') NOT NULL,
  perm_key VARCHAR(80) NOT NULL,
  PRIMARY KEY (role, perm_key),
  CONSTRAINT fk_rp_perm FOREIGN KEY (perm_key) REFERENCES permissions(perm_key) ON DELETE CASCADE
) ENGINE=InnoDB;