-- Run this in phpMyAdmin if database already exists without new tables
USE job_portal_narowal;

CREATE TABLE IF NOT EXISTS interview_requests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  application_id INT NOT NULL,
  employer_id INT NOT NULL,
  seeker_id INT NOT NULL,
  job_id INT NOT NULL,
  interview_datetime DATETIME NOT NULL,
  message TEXT,
  location_name VARCHAR(255) NOT NULL,
  location_lat DECIMAL(10,8) NULL,
  location_lng DECIMAL(11,8) NULL,
  map_link TEXT NULL,
  status ENUM('pending','accepted','rejected') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE,
  FOREIGN KEY (employer_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (seeker_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS reports (
  id INT AUTO_INCREMENT PRIMARY KEY,
  reporter_id INT NOT NULL,
  reported_user_id INT NULL,
  reported_job_id INT NULL,
  report_type ENUM('user','job','application','other') NOT NULL DEFAULT 'other',
  reason VARCHAR(255) NOT NULL,
  details TEXT NOT NULL,
  status ENUM('pending','reviewed','dismissed','action_taken') NOT NULL DEFAULT 'pending',
  admin_action TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (reporter_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (reported_user_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (reported_job_id) REFERENCES jobs(id) ON DELETE SET NULL
) ENGINE=InnoDB;