SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admins (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(150) NOT NULL,
  username VARCHAR(100) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS organizations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(190) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
INSERT INTO organizations (name) SELECT 'دانشگاه' WHERE NOT EXISTS (SELECT 1 FROM organizations);

CREATE TABLE IF NOT EXISTS academic_terms (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  organization_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(100) NOT NULL,
  code VARCHAR(40) NOT NULL,
  starts_on DATE NULL,
  ends_on DATE NULL,
  is_current TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_term_org_code (organization_id, code),
  CONSTRAINT fk_terms_org FOREIGN KEY (organization_id) REFERENCES organizations(id)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS students (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  organization_id BIGINT UNSIGNED NOT NULL,
  national_code CHAR(10) NOT NULL,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  mobile VARCHAR(20) NULL,
  student_number VARCHAR(50) NULL,
  birth_year SMALLINT UNSIGNED NULL,
  gender VARCHAR(20) NULL,
  city VARCHAR(100) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  source_import_batch_id BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_student_org_national_code (organization_id, national_code),
  KEY idx_students_name (last_name, first_name),
  KEY idx_students_mobile (mobile),
  CONSTRAINT fk_students_org FOREIGN KEY (organization_id) REFERENCES organizations(id)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS student_enrollments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  student_id BIGINT UNSIGNED NOT NULL,
  term_id BIGINT UNSIGNED NOT NULL,
  degree VARCHAR(80) NOT NULL,
  major VARCHAR(150) NOT NULL,
  admission_year SMALLINT UNSIGNED NULL,
  student_status ENUM('active','graduated','withdrawn','suspended','transferred') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_student_term (student_id, term_id),
  KEY idx_enrollment_major (major),
  KEY idx_enrollment_degree (degree),
  CONSTRAINT fk_enrollment_student FOREIGN KEY (student_id) REFERENCES students(id),
  CONSTRAINT fk_enrollment_term FOREIGN KEY (term_id) REFERENCES academic_terms(id)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS import_batches (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  organization_id BIGINT UNSIGNED NOT NULL,
  term_id BIGINT UNSIGNED NOT NULL,
  original_filename VARCHAR(255) NOT NULL,
  status ENUM('previewed','completed','failed') NOT NULL DEFAULT 'previewed',
  total_rows INT UNSIGNED NOT NULL DEFAULT 0,
  valid_rows INT UNSIGNED NOT NULL DEFAULT 0,
  error_rows INT UNSIGNED NOT NULL DEFAULT 0,
  inserted_rows INT UNSIGNED NOT NULL DEFAULT 0,
  updated_rows INT UNSIGNED NOT NULL DEFAULT 0,
  imported_by BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at TIMESTAMP NULL,
  CONSTRAINT fk_batch_org FOREIGN KEY (organization_id) REFERENCES organizations(id),
  CONSTRAINT fk_batch_term FOREIGN KEY (term_id) REFERENCES academic_terms(id),
  CONSTRAINT fk_batch_admin FOREIGN KEY (imported_by) REFERENCES admins(id)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS import_errors (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  batch_id BIGINT UNSIGNED NOT NULL,
  row_number INT UNSIGNED NOT NULL,
  national_code VARCHAR(30) NULL,
  error_message VARCHAR(500) NOT NULL,
  row_data JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_import_errors_batch (batch_id),
  CONSTRAINT fk_import_error_batch FOREIGN KEY (batch_id) REFERENCES import_batches(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS student_profiles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  student_id BIGINT UNSIGNED NOT NULL UNIQUE,
  birth_year SMALLINT UNSIGNED NULL,
  city VARCHAR(100) NULL,
  form_status ENUM('not_started','draft','submitted','needs_revision','approved') NOT NULL DEFAULT 'not_started',
  completion_percent TINYINT UNSIGNED NOT NULL DEFAULT 0,
  submitted_at DATETIME NULL,
  approved_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_profile_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Version 2: student talent, employment, awards and documents
-- Run this migration on the existing university_talent database.
-- submitted_at already exists in v1. Add approved_at only once if it is not present.

CREATE TABLE IF NOT EXISTS student_login_codes (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 student_id BIGINT UNSIGNED NOT NULL,
 code_hash VARCHAR(255) NOT NULL,
 expires_at DATETIME NOT NULL,
 attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
 consumed_at DATETIME NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 KEY idx_login_student (student_id, created_at),
 CONSTRAINT fk_login_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS talent_categories (
 id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 section ENUM('sport','cultural','art') NOT NULL,
 title VARCHAR(120) NOT NULL,
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 sort_order SMALLINT NOT NULL DEFAULT 0,
 UNIQUE KEY uq_talent_section_title(section,title)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

INSERT IGNORE INTO talent_categories(section,title,sort_order) VALUES
('sport','والیبال',1),('sport','فوتبال',2),('sport','فوتسال',3),('sport','بسکتبال',4),('sport','هندبال',5),('sport','شطرنج',6),('sport','آمادگی جسمانی',7),('sport','تنیس روی میز',8),('sport','تنیس',9),('sport','شنا',10),('sport','ورزش‌های رزمی',11),('sport','دارت',12),('sport','تیراندازی',13),('sport','کوهنوردی',14),('sport','دوومیدانی',15),('sport','بدمینتون',16),('sport','سایر',99),
('cultural','قرائت قرآن',1),('cultural','حفظ قرآن',2),('cultural','تواشیح',3),('cultural','مداحی',4),('cultural','پژوهش',5),('cultural','تحقیقات اجتماعی',6),('cultural','تبلیغات اجتماعی',7),('cultural','فعالیت جهادی',8),('cultural','فعالیت خیریه',9),('cultural','سایر',99),
('art','بازیگری',1),('art','تدوین فیلم',2),('art','نوازندگی',3),('art','مجری‌گری',4),('art','عکاسی',5),('art','فیلم‌برداری',6),('art','خوانندگی',7),('art','دوبله',8),('art','طراحی گرافیک',9),('art','نویسندگی',10),('art','نقاشی',11),('art','سایر',99);

CREATE TABLE IF NOT EXISTS student_talents (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 student_id BIGINT UNSIGNED NOT NULL,
 category_id SMALLINT UNSIGNED NOT NULL,
 skill_level ENUM('interested','amateur','semi_pro','professional') NOT NULL DEFAULT 'interested',
 experience_years TINYINT UNSIGNED NULL,
 organization_name VARCHAR(190) NULL,
 achievement TEXT NULL,
 description TEXT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_student_talent (student_id,category_id),
 CONSTRAINT fk_talent_student FOREIGN KEY(student_id) REFERENCES students(id) ON DELETE CASCADE,
 CONSTRAINT fk_talent_category FOREIGN KEY(category_id) REFERENCES talent_categories(id)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS student_employment (
 student_id BIGINT UNSIGNED PRIMARY KEY,
 is_employed TINYINT(1) NOT NULL DEFAULT 0,
 employed_before_university TINYINT(1) NOT NULL DEFAULT 0,
 employment_type VARCHAR(60) NULL,
 job_title VARCHAR(190) NULL,
 workplace VARCHAR(190) NULL,
 job_sector VARCHAR(120) NULL,
 job_major_match ENUM('full','partial','none','unknown') NOT NULL DEFAULT 'unknown',
 experience_range VARCHAR(40) NULL,
 is_self_employed TINYINT(1) NOT NULL DEFAULT 0,
 business_type VARCHAR(190) NULL,
 business_sector VARCHAR(120) NULL,
 employee_count SMALLINT UNSIGNED NULL,
 business_url VARCHAR(255) NULL,
 after_graduation_plan VARCHAR(80) NULL,
 description TEXT NULL,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_employment_student FOREIGN KEY(student_id) REFERENCES students(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS student_honors (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 student_id BIGINT UNSIGNED NOT NULL,
 domain ENUM('sport','cultural','art','research','academic','other') NOT NULL,
 title VARCHAR(190) NOT NULL,
 organization VARCHAR(190) NULL,
 award_year SMALLINT UNSIGNED NULL,
 description TEXT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 KEY idx_honor_student(student_id),
 CONSTRAINT fk_honor_student FOREIGN KEY(student_id) REFERENCES students(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS student_documents (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 student_id BIGINT UNSIGNED NOT NULL,
 honor_id BIGINT UNSIGNED NULL,
 original_name VARCHAR(255) NOT NULL,
 stored_name VARCHAR(255) NOT NULL,
 mime_type VARCHAR(100) NOT NULL,
 file_size INT UNSIGNED NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_doc_student FOREIGN KEY(student_id) REFERENCES students(id) ON DELETE CASCADE,
 CONSTRAINT fk_doc_honor FOREIGN KEY(honor_id) REFERENCES student_honors(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;