-- Create database
CREATE DATABASE IF NOT EXISTS candidate_management;
USE candidate_management;

-- Candidates table
CREATE TABLE candidates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(150) NOT NULL,
    phone VARCHAR(20),
    whatsapp VARCHAR(20),
    prometric_course ENUM('PROMETRIC', 'COURSE', 'DIPLOMA', '') DEFAULT NULL,
    specify VARCHAR(100), -- DHA, HAAD, SAUDI, etc.
    current_qualification VARCHAR(150),
    certificate VARCHAR(200),
    dream_date VARCHAR(50),
    contact_date VARCHAR(50),
    follow_up_date VARCHAR(50),
    status ENUM('HOT', 'WARM', 'COLD', 'EXAM PASS', 'DATAFLOW IN PROCESS', 
                'COACHING IN PROCESS', 'DEMO DONE', 'ONGOING', 'COMPLETED') DEFAULT 'COLD',
    sreeshma VARCHAR(100),
    reason TEXT,
    demo ENUM('DONE', 'WAIT', 'SCHEDULED', 'NOT DONE') DEFAULT 'NOT DONE',
    upsell ENUM('YES', 'NO', 'PENDING') DEFAULT 'PENDING',
    suggestion_m TEXT,
    marketing_source VARCHAR(100),
    assigned_to INT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (assigned_to) REFERENCES users(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- Payments table
CREATE TABLE payments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    candidate_id INT,
    amount DECIMAL(10,2),
    payment_date DATE,
    payment_type VARCHAR(50),
    description TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (candidate_id) REFERENCES candidates(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- Course information table
CREATE TABLE course_info (
    id INT PRIMARY KEY AUTO_INCREMENT,
    candidate_id INT,
    course_name VARCHAR(150),
    course_details TEXT,
    start_date VARCHAR(50),
    end_date VARCHAR(50),
    status VARCHAR(50),
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (candidate_id) REFERENCES candidates(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- Exam registration table
CREATE TABLE exam_registration (
    id INT PRIMARY KEY AUTO_INCREMENT,
    candidate_id INT,
    exam_type VARCHAR(100),
    exam_date VARCHAR(50),
    registration_date VARCHAR(50),
    status VARCHAR(50),
    notes TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (candidate_id) REFERENCES candidates(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- Dataflow table
CREATE TABLE dataflow (
    id INT PRIMARY KEY AUTO_INCREMENT,
    candidate_id INT,
    status VARCHAR(50),
    submitted_date VARCHAR(50),
    completed_date VARCHAR(50),
    notes TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (candidate_id) REFERENCES candidates(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- Documentation table
CREATE TABLE documentation (
    id INT PRIMARY KEY AUTO_INCREMENT,
    candidate_id INT,
    document_type VARCHAR(100),
    status VARCHAR(50),
    submitted_date VARCHAR(50),
    verified_date VARCHAR(50),
    notes TEXT,
    updated_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (candidate_id) REFERENCES candidates(id),
    FOREIGN KEY (updated_by) REFERENCES users(id)
);

-- Licensing table
CREATE TABLE licensing (
    id INT PRIMARY KEY AUTO_INCREMENT,
    candidate_id INT,
    license_type VARCHAR(100),
    application_date VARCHAR(50),
    status VARCHAR(50),
    issued_date VARCHAR(50),
    notes TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (candidate_id) REFERENCES candidates(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- Edit requests table (for admin approval)
CREATE TABLE edit_requests (
    id INT PRIMARY KEY AUTO_INCREMENT,
    candidate_id INT,
    field_name VARCHAR(100),
    old_value TEXT,
    new_value TEXT,
    requested_by INT,
    requested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    approved_by INT,
    approved_at TIMESTAMP NULL,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    comments TEXT,
    FOREIGN KEY (candidate_id) REFERENCES candidates(id),
    FOREIGN KEY (requested_by) REFERENCES users(id),
    FOREIGN KEY (approved_by) REFERENCES users(id)
);

-- Activity log
CREATE TABLE activity_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    action VARCHAR(255),
    table_name VARCHAR(50),
    record_id INT,
    details TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);