-- Drop existing tables (if recreating from scratch)
DROP TABLE IF EXISTS notifications;
DROP TABLE IF EXISTS applications;

CREATE TABLE applications (
  id VARCHAR(32) PRIMARY KEY,
  reference_number VARCHAR(20) UNIQUE,
  first_name VARCHAR(100),
  last_name VARCHAR(100),
  email VARCHAR(255),
  phone VARCHAR(50),
  country_of_origin VARCHAR(10),
  date_of_birth VARCHAR(20),
  education_level VARCHAR(50),
  years_experience INT DEFAULT 0,
  skills TEXT,
  cv_url VARCHAR(500),
  cv_filename VARCHAR(255),
  target_country VARCHAR(10),
  job_sector VARCHAR(50),
  locale VARCHAR(10),
  payment_status ENUM('pending','paid','failed') DEFAULT 'pending',
  application_status ENUM('new','reviewed','interview','accepted','rejected') DEFAULT 'new',
  stripe_session_id VARCHAR(255),
  amount_paid INT,
  currency VARCHAR(10),
  notes TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_target_country (target_country),
  INDEX idx_job_sector (job_sector),
  INDEX idx_payment_status (payment_status),
  INDEX idx_application_status (application_status),
  INDEX idx_created_at (created_at)
);

CREATE TABLE notifications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  application_id VARCHAR(32),
  template VARCHAR(50),
  custom_message TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE
);