CREATE DATABASE IF NOT EXISTS `jobdashboard`;
USE `jobdashboard`;

CREATE TABLE IF NOT EXISTS `jobs` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `trade_type` VARCHAR(50) NOT NULL,
  `location` VARCHAR(10) NOT NULL,
  `date` DATE NOT NULL,
  `description` TEXT,
  `status` ENUM('active', 'completed') DEFAULT 'active', -- Added job status
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS `candidates` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(100) UNIQUE NOT NULL,
  `phone` VARCHAR(20) NULL DEFAULT NULL, -- Added phone number
  `trade_type` VARCHAR(50) NOT NULL,
  `postcode` VARCHAR(10) NOT NULL,
  `availability_status` ENUM('Available', 'Not Available', 'Available On') DEFAULT 'Available', -- Renamed for clarity with form
  `available_date` DATE NULL DEFAULT NULL, -- Added to store specific date
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Added created_at
  `last_updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Table to link Candidates to specific Jobs and store job-specific status
CREATE TABLE IF NOT EXISTS `job_applications` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `job_id` INT NOT NULL,
  `candidate_id` INT NOT NULL,
  `status` ENUM('Pending', 'Shortlisted', 'Confirmed', 'Rejected', 'OutOfRange', 'ApiError') DEFAULT 'Pending', -- Status specific to this job application
  `confirmation_token` VARCHAR(64) UNIQUE NULL DEFAULT NULL, -- Token for email confirmation
  `drive_time_minutes` INT NULL DEFAULT NULL, -- Drive time in minutes for this job
  `proximity_band` ENUM('Ideal Range', 'Acceptable', 'Too Far', 'OutOfRange', 'ApiError', 'Unknown') DEFAULT 'Unknown', -- Proximity band for this job
  `applied_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `status_updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE,
  FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
  UNIQUE KEY `uniq_job_candidate` (`job_id`, `candidate_id`) -- Ensure a candidate applies only once per job
);

CREATE TABLE IF NOT EXISTS `email_logs` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `candidate_id` INT,
  `job_id` INT,
  `sent_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `confirmed_at` TIMESTAMP NULL,
  FOREIGN KEY (candidate_id) REFERENCES candidates(id),
  FOREIGN KEY (job_id) REFERENCES jobs(id)
);

CREATE INDEX idx_trade_type ON candidates(trade_type);
CREATE INDEX idx_postcode ON candidates(postcode);
