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

CREATE TABLE IF NOT EXISTS college_list (
  id INT AUTO_INCREMENT PRIMARY KEY,
  college_name VARCHAR(255) NOT NULL,
  city VARCHAR(120) NOT NULL,
  state VARCHAR(120) NOT NULL,
  website_url TEXT DEFAULT NULL,
  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 college_list (
  id INT AUTO_INCREMENT PRIMARY KEY,
  college_name VARCHAR(255) NOT NULL,
  city VARCHAR(120) NOT NULL,
  state VARCHAR(120) NOT NULL,
  website_url TEXT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_college_location (college_name, city, state)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS colleges (
  id INT AUTO_INCREMENT PRIMARY KEY,
  college_list_id INT DEFAULT NULL,
  college_list_id INT DEFAULT NULL,
  college_name VARCHAR(255) NOT NULL,
  city VARCHAR(120) DEFAULT NULL,
  state VARCHAR(120) DEFAULT NULL,
  website_url TEXT DEFAULT NULL,
  source_url TEXT DEFAULT NULL,
  address TEXT DEFAULT NULL,
  phone VARCHAR(255) DEFAULT NULL,
  mobile VARCHAR(255) DEFAULT NULL,
  email VARCHAR(255) DEFAULT NULL,
  courses LONGTEXT DEFAULT NULL,
  course LONGTEXT DEFAULT NULL,
  admission_info LONGTEXT DEFAULT NULL,
  fees_info LONGTEXT DEFAULT NULL,
  course_fee LONGTEXT DEFAULT NULL,
  placement_info LONGTEXT DEFAULT NULL,
  facilities LONGTEXT DEFAULT NULL,
  hostel_info LONGTEXT DEFAULT NULL,
  scholarship_info LONGTEXT DEFAULT NULL,
  approval_affiliation LONGTEXT DEFAULT NULL,
  more_information LONGTEXT DEFAULT NULL,
  social_media LONGTEXT DEFAULT NULL,
  field_sources LONGTEXT DEFAULT NULL,
  confidence_notes LONGTEXT DEFAULT NULL,
  about_college LONGTEXT DEFAULT NULL,
  validation_score INT DEFAULT 0,
  scrape_status ENUM('pending','success','failed','needs_review') DEFAULT 'pending',
  error_message TEXT DEFAULT NULL,
  raw_markdown LONGTEXT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_colleges_college_list_id (college_list_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE colleges
  ADD UNIQUE KEY uq_colleges_college_list_id (college_list_id);

CREATE TABLE IF NOT EXISTS scrape_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  college_id INT DEFAULT NULL,
  source_url TEXT DEFAULT NULL,
  scrape_response LONGTEXT DEFAULT NULL,
  status ENUM('success','failed','needs_review') DEFAULT 'success',
  error_message TEXT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX(college_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;