-- Fresh Migration Script for EduBridge
-- This script creates all tables as they would be created by Laravel migrations

-- 1. Create cache table
CREATE TABLE IF NOT EXISTS cache (
  `key` VARCHAR(255) NOT NULL,
  value MEDIUMTEXT NOT NULL,
  expiration INT NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Create cache_locks table
CREATE TABLE IF NOT EXISTS cache_locks (
  `key` VARCHAR(255) NOT NULL,
  owner VARCHAR(255) NOT NULL,
  expiration INT NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Create jobs table
CREATE TABLE IF NOT EXISTS jobs (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  queue VARCHAR(255) NOT NULL,
  payload LONGTEXT NOT NULL,
  attempts TINYINT(3) UNSIGNED NOT NULL,
  reserved_at INT(10) UNSIGNED DEFAULT NULL,
  available_at INT(10) UNSIGNED NOT NULL,
  created_at INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  KEY jobs_queue_index (queue)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Create job_batches table
CREATE TABLE IF NOT EXISTS job_batches (
  id VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  total_jobs INT NOT NULL,
  pending_jobs INT NOT NULL,
  failed_jobs INT NOT NULL,
  failed_job_ids LONGTEXT NOT NULL,
  options MEDIUMTEXT DEFAULT NULL,
  cancelled_at INT DEFAULT NULL,
  created_at INT NOT NULL,
  finished_at INT DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Create failed_jobs table
CREATE TABLE IF NOT EXISTS failed_jobs (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid VARCHAR(255) NOT NULL,
  connection TEXT NOT NULL,
  queue TEXT NOT NULL,
  payload LONGTEXT NOT NULL,
  exception LONGTEXT NOT NULL,
  failed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY failed_jobs_uuid_unique (uuid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Create sessions table
CREATE TABLE IF NOT EXISTS sessions (
  id VARCHAR(255) NOT NULL,
  user_id BIGINT(20) UNSIGNED DEFAULT NULL,
  ip_address VARCHAR(45) DEFAULT NULL,
  user_agent TEXT DEFAULT NULL,
  payload LONGTEXT NOT NULL,
  last_activity INT NOT NULL,
  PRIMARY KEY (id),
  KEY sessions_user_id_index (user_id),
  KEY sessions_last_activity_index (last_activity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. Create password_reset_tokens table
CREATE TABLE IF NOT EXISTS password_reset_tokens (
  email VARCHAR(255) NOT NULL,
  token VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. Create users table
CREATE TABLE IF NOT EXISTS users (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  email_verified_at TIMESTAMP NULL DEFAULT NULL,
  password VARCHAR(255) NOT NULL,
  role VARCHAR(255) NOT NULL DEFAULT 'user',
  remember_token VARCHAR(100) DEFAULT NULL,
  university_id BIGINT(20) UNSIGNED DEFAULT NULL,
  partner_id BIGINT(20) UNSIGNED DEFAULT NULL,
  created_at TIMESTAMP NULL DEFAULT NULL,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY users_email_unique (email),
  KEY users_university_id_foreign (university_id),
  KEY users_partner_id_foreign (partner_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. Create universities table
CREATE TABLE IF NOT EXISTS universities (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  short_code VARCHAR(100) DEFAULT NULL,
  address TEXT DEFAULT NULL,
  contact_email VARCHAR(200) DEFAULT NULL,
  contact_phone VARCHAR(20) DEFAULT NULL,
  logo VARCHAR(255) DEFAULT NULL,
  description TEXT DEFAULT NULL,
  microsite_url VARCHAR(255) DEFAULT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  public_key VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NULL DEFAULT NULL,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY universities_public_key_unique (public_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 10. Create partners table
CREATE TABLE IF NOT EXISTS partners (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  contact_email VARCHAR(255) DEFAULT NULL,
  contact_phone VARCHAR(20) DEFAULT NULL,
  website VARCHAR(255) DEFAULT NULL,
  pancard VARCHAR(10) DEFAULT NULL,
  gst VARCHAR(15) DEFAULT NULL,
  state_id BIGINT(20) UNSIGNED DEFAULT NULL,
  address TEXT DEFAULT NULL,
  description TEXT DEFAULT NULL,
  created_at TIMESTAMP NULL DEFAULT NULL,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  KEY partners_state_id_foreign (state_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 11. Create courses table
CREATE TABLE IF NOT EXISTS courses (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  short_code VARCHAR(10) NOT NULL,
  created_at TIMESTAMP NULL DEFAULT NULL,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY courses_short_code_unique (short_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 12. Create specializations table
CREATE TABLE IF NOT EXISTS specializations (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  course_id BIGINT(20) UNSIGNED NOT NULL,
  name VARCHAR(255) NOT NULL,
  short_code VARCHAR(10) DEFAULT NULL,
  created_at TIMESTAMP NULL DEFAULT NULL,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  KEY specializations_course_id_foreign (course_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 13. Create states table
CREATE TABLE IF NOT EXISTS states (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  state_name VARCHAR(100) NOT NULL,
  state_code VARCHAR(10) DEFAULT NULL,
  state_type ENUM('State','Union Territory') NOT NULL DEFAULT 'State',
  created_at TIMESTAMP NULL DEFAULT NULL,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 14. Create districts table
CREATE TABLE IF NOT EXISTS districts (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  state_id BIGINT(20) UNSIGNED NOT NULL,
  district_name VARCHAR(150) NOT NULL,
  created_at TIMESTAMP NULL DEFAULT NULL,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  KEY districts_state_id_foreign (state_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 15. Create admission_sessions table
CREATE TABLE IF NOT EXISTS admission_sessions (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  university_id BIGINT(20) UNSIGNED NOT NULL,
  from_day INT(11) NOT NULL,
  from_month INT(11) NOT NULL,
  to_day INT(11) NOT NULL,
  to_month INT(11) NOT NULL,
  session VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NULL DEFAULT NULL,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  KEY admission_sessions_university_id_foreign (university_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 16. Create leads table
CREATE TABLE IF NOT EXISTS leads (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  partner_id BIGINT(20) UNSIGNED NOT NULL,
  university_id BIGINT(20) UNSIGNED NOT NULL,
  course_id BIGINT(20) UNSIGNED NOT NULL,
  specialization_id BIGINT(20) UNSIGNED DEFAULT NULL,
  state_id BIGINT(20) UNSIGNED DEFAULT NULL,
  district_id BIGINT(20) UNSIGNED DEFAULT NULL,
  full_name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  mobile VARCHAR(15) NOT NULL,
  country_code VARCHAR(5) NOT NULL DEFAULT '+91',
  date_of_birth DATE DEFAULT NULL,
  notes TEXT DEFAULT NULL,
  student_id VARCHAR(255) NOT NULL,
  password VARCHAR(255) NOT NULL,
  plain_password VARCHAR(255) DEFAULT NULL,
  credentials_sent_at TIMESTAMP NULL DEFAULT NULL,
  created_at TIMESTAMP NULL DEFAULT NULL,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY leads_student_id_unique (student_id),
  KEY leads_partner_id_foreign (partner_id),
  KEY leads_university_id_foreign (university_id),
  KEY leads_course_id_foreign (course_id),
  KEY leads_specialization_id_foreign (specialization_id),
  KEY leads_state_id_foreign (state_id),
  KEY leads_district_id_foreign (district_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 17. Create students table
CREATE TABLE IF NOT EXISTS students (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  lead_id BIGINT(20) UNSIGNED NOT NULL,
  partner_id BIGINT(20) UNSIGNED NOT NULL,
  university_id BIGINT(20) UNSIGNED NOT NULL,
  student_name VARCHAR(255) NOT NULL,
  father_name VARCHAR(255) NOT NULL,
  mother_name VARCHAR(255) NOT NULL,
  dob DATE NOT NULL,
  category VARCHAR(255) NOT NULL,
  gender ENUM('Male','Female','Other') NOT NULL,
  email VARCHAR(255) NOT NULL,
  whatsapp_number VARCHAR(255) DEFAULT NULL,
  aadhar_number VARCHAR(255) DEFAULT NULL,
  employment_status ENUM('employed','unemployed','self_employed','student','retired') DEFAULT NULL,
  guardian_email VARCHAR(255) DEFAULT NULL,
  guardian_mobile VARCHAR(255) DEFAULT NULL,
  counsellor_name VARCHAR(255) DEFAULT NULL,
  abc_id VARCHAR(255) DEFAULT NULL,
  deb_id VARCHAR(255) DEFAULT NULL,
  admission_session_id BIGINT(20) UNSIGNED DEFAULT NULL,
  admission_type VARCHAR(255) DEFAULT NULL,
  course_id BIGINT(20) UNSIGNED NOT NULL,
  specialization VARCHAR(255) DEFAULT NULL,
  semester INT(11) DEFAULT NULL,
  status ENUM('draft','submitted','under_review','approved','rejected') NOT NULL DEFAULT 'draft',
  submitted_at TIMESTAMP NULL DEFAULT NULL,
  created_at TIMESTAMP NULL DEFAULT NULL,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY students_lead_id_unique (lead_id),
  UNIQUE KEY students_email_unique (email),
  UNIQUE KEY students_aadhar_number_unique (aadhar_number),
  KEY students_aadhar_number_index (aadhar_number),
  KEY students_email_index (email),
  KEY students_admission_session_id_index (admission_session_id),
  KEY students_course_id_index (course_id),
  KEY students_status_index (status),
  KEY students_partner_id_index (partner_id),
  KEY students_university_id_index (university_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 18. Create student_academics table
CREATE TABLE IF NOT EXISTS student_academics (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  student_id BIGINT(20) UNSIGNED NOT NULL,
  examination VARCHAR(255) NOT NULL,
  subject VARCHAR(255) DEFAULT NULL,
  year YEAR(4) NOT NULL,
  board_university VARCHAR(255) NOT NULL,
  obtained_marks DECIMAL(8,2) DEFAULT NULL,
  maximum_marks DECIMAL(8,2) DEFAULT NULL,
  percentage_grade VARCHAR(255) DEFAULT NULL,
  marksheet_file VARCHAR(255) DEFAULT NULL,
  roll_number INT(11) DEFAULT NULL,
  institution_name VARCHAR(255) DEFAULT NULL,
  remarks TEXT DEFAULT NULL,
  created_at TIMESTAMP NULL DEFAULT NULL,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  KEY student_academics_student_id_index (student_id),
  KEY student_academics_examination_index (examination),
  KEY student_academics_year_index (year),
  KEY student_academics_board_university_index (board_university),
  KEY student_academics_student_id_examination_index (student_id,examination)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 19. Create student_documents table
CREATE TABLE IF NOT EXISTS student_documents (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  student_id BIGINT(20) UNSIGNED NOT NULL,
  document_type ENUM('passport_photo','signature','aadhar_card','experience_certificate','migration_certificate','marksheet_10th','marksheet_12th','graduation_marksheet','diploma_marksheet','character_certificate','caste_certificate','income_certificate','medical_certificate','other') NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  status ENUM('pending','done','verified','rejected') NOT NULL DEFAULT 'pending',
  rejection_reason TEXT DEFAULT NULL,
  verified_by BIGINT(20) UNSIGNED DEFAULT NULL,
  verified_at TIMESTAMP NULL DEFAULT NULL,
  sort_order INT(11) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NULL DEFAULT NULL,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  KEY student_documents_student_id_index (student_id),
  KEY student_documents_document_type_index (document_type),
  KEY student_documents_status_index (status),
  KEY student_documents_verified_by_index (verified_by),
  KEY student_documents_student_id_document_type_index (student_id,document_type),
  KEY student_documents_student_id_status_index (student_id,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 20. Create student_addresses table
CREATE TABLE IF NOT EXISTS student_addresses (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  student_id BIGINT(20) UNSIGNED NOT NULL,
  communication_address TEXT DEFAULT NULL,
  communication_pincode VARCHAR(255) DEFAULT NULL,
  communication_post_office VARCHAR(255) DEFAULT NULL,
  communication_district_id BIGINT(20) UNSIGNED DEFAULT NULL,
  communication_state_id BIGINT(20) UNSIGNED DEFAULT NULL,
  permanent_address TEXT DEFAULT NULL,
  permanent_pincode VARCHAR(255) DEFAULT NULL,
  permanent_post_office VARCHAR(255) DEFAULT NULL,
  permanent_district VARCHAR(255) DEFAULT NULL,
  permanent_state VARCHAR(255) DEFAULT NULL,
  is_same_as_correspondence TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NULL DEFAULT NULL,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  KEY student_addresses_student_id_index (student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 21. Create student_employment table
CREATE TABLE IF NOT EXISTS student_employment (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  student_id BIGINT(20) UNSIGNED NOT NULL,
  employment_status ENUM('government','private','other') DEFAULT NULL,
  organization_name VARCHAR(255) DEFAULT NULL,
  designation VARCHAR(255) DEFAULT NULL,
  working_since DATE DEFAULT NULL,
  employment_state_id BIGINT(20) UNSIGNED DEFAULT NULL,
  employment_district_id BIGINT(20) UNSIGNED DEFAULT NULL,
  linkedin_profile VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP NULL DEFAULT NULL,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  KEY student_employment_student_id_index (student_id),
  KEY student_employment_organization_name_index (organization_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 22. Create university_partner table
CREATE TABLE IF NOT EXISTS university_partner (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  university_id BIGINT(20) UNSIGNED NOT NULL,
  partner_id BIGINT(20) UNSIGNED NOT NULL,
  percentage_share DECIMAL(5,2) DEFAULT NULL,
  created_at TIMESTAMP NULL DEFAULT NULL,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY university_partner_university_id_partner_id_unique (university_id,partner_id),
  KEY university_partner_university_id_foreign (university_id),
  KEY university_partner_partner_id_foreign (partner_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 23. Create university_specialization table
CREATE TABLE IF NOT EXISTS university_specialization (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  university_id BIGINT(20) UNSIGNED NOT NULL,
  specialization_id BIGINT(20) UNSIGNED NOT NULL,
  created_at TIMESTAMP NULL DEFAULT NULL,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY university_specialization_university_id_specialization_id_unique (university_id,specialization_id),
  KEY university_specialization_university_id_foreign (university_id),
  KEY university_specialization_specialization_id_foreign (specialization_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 24. Create university_courses table
CREATE TABLE IF NOT EXISTS university_courses (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  university_id BIGINT(20) UNSIGNED NOT NULL,
  course_id BIGINT(20) UNSIGNED NOT NULL,
  total_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  num_semesters INT NOT NULL DEFAULT 1,
  created_at TIMESTAMP NULL DEFAULT NULL,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY university_courses_university_id_course_id_unique (university_id,course_id),
  KEY university_courses_university_id_foreign (university_id),
  KEY university_courses_course_id_foreign (course_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 25. Create course_fee_structures table
CREATE TABLE IF NOT EXISTS course_fee_structures (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  university_course_id BIGINT(20) UNSIGNED NOT NULL,
  semester_number INT NOT NULL,
  semester_fee DECIMAL(10,2) NOT NULL,
  partner_share DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  created_at TIMESTAMP NULL DEFAULT NULL,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY course_fee_structures_uc_id_semester_unique (university_course_id,semester_number),
  KEY course_fee_structures_university_course_id_foreign (university_course_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 26. Create personal_access_tokens table (for Sanctum)
CREATE TABLE IF NOT EXISTS personal_access_tokens (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  tokenable_type VARCHAR(255) NOT NULL,
  tokenable_id BIGINT(20) UNSIGNED NOT NULL,
  name VARCHAR(255) NOT NULL,
  token VARCHAR(64) NOT NULL,
  abilities TEXT DEFAULT NULL,
  last_used_at TIMESTAMP NULL DEFAULT NULL,
  expires_at TIMESTAMP NULL DEFAULT NULL,
  created_at TIMESTAMP NULL DEFAULT NULL,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY personal_access_tokens_token_unique (token),
  KEY personal_access_tokens_tokenable_type_tokenable_id_index (tokenable_type,tokenable_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add foreign key constraints
ALTER TABLE specializations
ADD CONSTRAINT specializations_course_id_foreign FOREIGN KEY (course_id) REFERENCES courses (id) ON DELETE CASCADE;

ALTER TABLE districts
ADD CONSTRAINT districts_state_id_foreign FOREIGN KEY (state_id) REFERENCES states (id) ON DELETE CASCADE;

ALTER TABLE partners
ADD CONSTRAINT partners_state_id_foreign FOREIGN KEY (state_id) REFERENCES states (id) ON DELETE SET NULL;

ALTER TABLE users
ADD CONSTRAINT users_university_id_foreign FOREIGN KEY (university_id) REFERENCES universities (id) ON DELETE CASCADE,
ADD CONSTRAINT users_partner_id_foreign FOREIGN KEY (partner_id) REFERENCES partners (id) ON DELETE SET NULL;

ALTER TABLE admission_sessions
ADD CONSTRAINT admission_sessions_university_id_foreign FOREIGN KEY (university_id) REFERENCES universities (id) ON DELETE CASCADE;

ALTER TABLE leads
ADD CONSTRAINT leads_partner_id_foreign FOREIGN KEY (partner_id) REFERENCES partners (id) ON DELETE CASCADE,
ADD CONSTRAINT leads_university_id_foreign FOREIGN KEY (university_id) REFERENCES universities (id) ON DELETE CASCADE,
ADD CONSTRAINT leads_course_id_foreign FOREIGN KEY (course_id) REFERENCES courses (id) ON DELETE CASCADE,
ADD CONSTRAINT leads_specialization_id_foreign FOREIGN KEY (specialization_id) REFERENCES specializations (id) ON DELETE CASCADE,
ADD CONSTRAINT leads_state_id_foreign FOREIGN KEY (state_id) REFERENCES states (id) ON DELETE CASCADE,
ADD CONSTRAINT leads_district_id_foreign FOREIGN KEY (district_id) REFERENCES districts (id) ON DELETE CASCADE;

ALTER TABLE students
ADD CONSTRAINT students_lead_id_foreign FOREIGN KEY (lead_id) REFERENCES leads (id),
ADD CONSTRAINT students_partner_id_foreign FOREIGN KEY (partner_id) REFERENCES partners (id),
ADD CONSTRAINT students_university_id_foreign FOREIGN KEY (university_id) REFERENCES universities (id),
ADD CONSTRAINT students_admission_session_id_foreign FOREIGN KEY (admission_session_id) REFERENCES admission_sessions (id) ON DELETE SET NULL,
ADD CONSTRAINT students_course_id_foreign FOREIGN KEY (course_id) REFERENCES courses (id);

ALTER TABLE student_academics
ADD CONSTRAINT student_academics_student_id_foreign FOREIGN KEY (student_id) REFERENCES students (id);

ALTER TABLE student_documents
ADD CONSTRAINT student_documents_student_id_foreign FOREIGN KEY (student_id) REFERENCES students (id);

ALTER TABLE student_addresses
ADD CONSTRAINT student_addresses_student_id_foreign FOREIGN KEY (student_id) REFERENCES students (id);

ALTER TABLE student_employment
ADD CONSTRAINT student_employment_student_id_foreign FOREIGN KEY (student_id) REFERENCES students (id);

ALTER TABLE university_partner
ADD CONSTRAINT university_partner_university_id_foreign FOREIGN KEY (university_id) REFERENCES universities (id) ON DELETE CASCADE,
ADD CONSTRAINT university_partner_partner_id_foreign FOREIGN KEY (partner_id) REFERENCES partners (id) ON DELETE CASCADE;

ALTER TABLE university_specialization
ADD CONSTRAINT university_specialization_university_id_foreign FOREIGN KEY (university_id) REFERENCES universities (id) ON DELETE CASCADE,
ADD CONSTRAINT university_specialization_specialization_id_foreign FOREIGN KEY (specialization_id) REFERENCES specializations (id) ON DELETE CASCADE;

ALTER TABLE university_courses
ADD CONSTRAINT university_courses_university_id_foreign FOREIGN KEY (university_id) REFERENCES universities (id) ON DELETE CASCADE,
ADD CONSTRAINT university_courses_course_id_foreign FOREIGN KEY (course_id) REFERENCES courses (id) ON DELETE CASCADE;

ALTER TABLE course_fee_structures
ADD CONSTRAINT course_fee_structures_university_course_id_foreign FOREIGN KEY (university_course_id) REFERENCES university_courses (id) ON DELETE CASCADE;
