-- Job Mate Global candidate management module
-- Select qgfuivft_jobs before importing this file.

CREATE TABLE IF NOT EXISTS candidates (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(160) NOT NULL,
    phone VARCHAR(30) NOT NULL,
    email VARCHAR(190) NOT NULL,
    job_role VARCHAR(160) DEFAULT NULL,
    experience VARCHAR(80) DEFAULT NULL,
    current_salary VARCHAR(80) DEFAULT NULL,
    expected_salary VARCHAR(80) DEFAULT NULL,
    notice_period VARCHAR(80) DEFAULT NULL,
    current_city VARCHAR(120) DEFAULT NULL,
    cities_to_apply TEXT DEFAULT NULL,
    resume_path VARCHAR(255) DEFAULT NULL,
    package_amount INT NOT NULL DEFAULT 20,
    payment_status ENUM('Pending','Paid') NOT NULL DEFAULT 'Pending',
    candidate_code CHAR(6) DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME DEFAULT NULL,
    UNIQUE KEY uq_candidate_code (candidate_code),
    KEY idx_candidate_email (email),
    KEY idx_candidate_phone (phone),
    KEY idx_candidate_created (created_at),
    KEY idx_candidate_payment (payment_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS candidate_email_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    candidate_id BIGINT UNSIGNED NOT NULL,
    email_to VARCHAR(190) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    email_type VARCHAR(60) NOT NULL,
    send_status ENUM('Sent','Failed') NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_email_candidate (candidate_id),
    CONSTRAINT fk_email_candidate FOREIGN KEY (candidate_id)
      REFERENCES candidates(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
