-- ============================================================
-- JobConnect - Full Database Schema
-- PHP 8+ / MySQL (MariaDB compatible) / cPanel ready
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;

-- ------------------------------------------------------------
-- 1. STATES
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS states (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    status TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_state_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 2. CITIES
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS cities (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    state_id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    status TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_city_state (state_id),
    CONSTRAINT fk_city_state FOREIGN KEY (state_id) REFERENCES states(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 3. ADMIN_USERS (admin panel login - separate from app users)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS admin_users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('super_admin','sub_admin') NOT NULL DEFAULT 'sub_admin',
    photo VARCHAR(255) DEFAULT NULL,
    status TINYINT(1) NOT NULL DEFAULT 1,
    last_login TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_admin_username (username),
    UNIQUE KEY uq_admin_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 4. USERS (single account type - job seeker or recruiter, same table)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    mobile_number VARCHAR(15) NOT NULL,
    email VARCHAR(150) DEFAULT NULL,
    password VARCHAR(255) NOT NULL,
    otp VARCHAR(6) DEFAULT NULL,
    otp_expires_at DATETIME DEFAULT NULL,
    mobile_verified TINYINT(1) NOT NULL DEFAULT 0,
    fcm_token VARCHAR(255) DEFAULT NULL,
    status ENUM('active','blocked') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_user_mobile (mobile_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 5. USER_PROFILES
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS user_profiles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    profile_photo VARCHAR(255) DEFAULT NULL,
    dob DATE DEFAULT NULL,
    gender ENUM('male','female','other') DEFAULT NULL,
    address VARCHAR(255) DEFAULT NULL,
    city_id INT UNSIGNED DEFAULT NULL,
    state_id INT UNSIGNED DEFAULT NULL,
    pincode VARCHAR(10) DEFAULT NULL,
    education VARCHAR(150) DEFAULT NULL,
    experience VARCHAR(100) DEFAULT NULL,
    current_company VARCHAR(150) DEFAULT NULL,
    skills TEXT DEFAULT NULL,
    languages VARCHAR(255) DEFAULT NULL,
    about TEXT DEFAULT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_profile_user (user_id),
    KEY idx_profile_city (city_id),
    KEY idx_profile_state (state_id),
    CONSTRAINT fk_profile_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_profile_city FOREIGN KEY (city_id) REFERENCES cities(id) ON DELETE SET NULL,
    CONSTRAINT fk_profile_state FOREIGN KEY (state_id) REFERENCES states(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 6. RESUMES
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS resumes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    file_type VARCHAR(10) NOT NULL,
    file_size INT UNSIGNED DEFAULT NULL,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_resume_user (user_id),
    CONSTRAINT fk_resume_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 7. JOB_CATEGORIES
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS job_categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(120) NOT NULL,
    icon VARCHAR(100) DEFAULT NULL,
    status TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_category_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 8. JOBS
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS jobs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    posted_by INT UNSIGNED NOT NULL,
    company_name VARCHAR(150) DEFAULT NULL,
    company_logo VARCHAR(255) DEFAULT NULL,
    recruiter_name VARCHAR(150) DEFAULT NULL,
    mobile_number VARCHAR(15) DEFAULT NULL,
    email VARCHAR(150) DEFAULT NULL,
    job_title VARCHAR(150) DEFAULT NULL,
    category_id INT UNSIGNED DEFAULT NULL,
    department VARCHAR(100) DEFAULT NULL,
    vacancies INT UNSIGNED NOT NULL DEFAULT 1,
    job_location VARCHAR(255) DEFAULT NULL,
    state_id INT UNSIGNED DEFAULT NULL,
    city_id INT UNSIGNED DEFAULT NULL,
    pincode VARCHAR(10) DEFAULT NULL,
    salary_min INT UNSIGNED DEFAULT NULL,
    salary_max INT UNSIGNED DEFAULT NULL,
    incentive VARCHAR(150) DEFAULT NULL,
    experience_required VARCHAR(100) DEFAULT NULL,
    education_required VARCHAR(150) DEFAULT NULL,
    gender ENUM('any','male','female') NOT NULL DEFAULT 'any',
    age_limit VARCHAR(50) DEFAULT NULL,
    job_type ENUM('full_time','part_time','contract','internship','walk_in') NOT NULL DEFAULT 'full_time',
    shift_timing VARCHAR(100) DEFAULT NULL,
    working_days VARCHAR(100) DEFAULT NULL,
    interview_date DATE DEFAULT NULL,
    interview_time TIME DEFAULT NULL,
    interview_address VARCHAR(255) DEFAULT NULL,
    skills_required TEXT DEFAULT NULL,
    description TEXT DEFAULT NULL,
    responsibilities TEXT DEFAULT NULL,
    benefits TEXT DEFAULT NULL,
    last_date_to_apply DATE DEFAULT NULL,
    company_website VARCHAR(255) DEFAULT NULL,
    attachment_pdf VARCHAR(255) DEFAULT NULL,
    status ENUM('draft','pending','approved','rejected','expired','closed') NOT NULL DEFAULT 'draft',
    is_featured TINYINT(1) NOT NULL DEFAULT 0,
    is_urgent TINYINT(1) NOT NULL DEFAULT 0,
    views_count INT UNSIGNED NOT NULL DEFAULT 0,
    rejection_reason VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_job_posted_by (posted_by),
    KEY idx_job_category (category_id),
    KEY idx_job_state (state_id),
    KEY idx_job_city (city_id),
    KEY idx_job_status (status),
    KEY idx_job_featured (is_featured),
    KEY idx_job_urgent (is_urgent),
    KEY idx_job_created (created_at),
    CONSTRAINT fk_job_user FOREIGN KEY (posted_by) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_job_category FOREIGN KEY (category_id) REFERENCES job_categories(id) ON DELETE RESTRICT,
    CONSTRAINT fk_job_state FOREIGN KEY (state_id) REFERENCES states(id) ON DELETE SET NULL,
    CONSTRAINT fk_job_city FOREIGN KEY (city_id) REFERENCES cities(id) ON DELETE SET NULL,
    FULLTEXT KEY ft_job_search (job_title, company_name, skills_required)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 9. JOB_IMAGES (extra job/company gallery images, optional)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS job_images (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    job_id INT UNSIGNED NOT NULL,
    image_path VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_jobimage_job (job_id),
    CONSTRAINT fk_jobimage_job FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 10. SAVED_JOBS
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS saved_jobs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    job_id INT UNSIGNED NOT NULL,
    saved_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_saved_user_job (user_id, job_id),
    CONSTRAINT fk_saved_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_saved_job FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 11. APPLICATIONS
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS applications (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    job_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    resume_id INT UNSIGNED DEFAULT NULL,
    status ENUM('applied','under_review','shortlisted','interview_scheduled','selected','rejected','withdrawn') NOT NULL DEFAULT 'applied',
    resume_viewed TINYINT(1) NOT NULL DEFAULT 0,
    applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_application_job_user (job_id, user_id),
    KEY idx_app_user (user_id),
    KEY idx_app_status (status),
    CONSTRAINT fk_app_job FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE,
    CONSTRAINT fk_app_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_app_resume FOREIGN KEY (resume_id) REFERENCES resumes(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 12. APPLICATION_STATUS (status change history/audit trail)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS application_status (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    application_id INT UNSIGNED NOT NULL,
    status ENUM('applied','under_review','shortlisted','interview_scheduled','selected','rejected','withdrawn') NOT NULL,
    remarks VARCHAR(255) DEFAULT NULL,
    changed_by INT UNSIGNED DEFAULT NULL,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_appstatus_app (application_id),
    CONSTRAINT fk_appstatus_app FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 13. NOTIFICATIONS
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS notifications (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED DEFAULT NULL COMMENT 'NULL = broadcast to all',
    title VARCHAR(150) NOT NULL,
    message TEXT NOT NULL,
    type VARCHAR(50) DEFAULT 'general',
    is_read TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_notif_user (user_id),
    CONSTRAINT fk_notif_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 13b. NOTIFICATION_READS (per-user read tracking for BROADCAST
-- notifications only, since a broadcast row is shared across users -
-- marking notifications.is_read directly would mark it read for everyone)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS notification_reads (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    notification_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    read_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_read_notif_user (notification_id, user_id),
    CONSTRAINT fk_read_notif FOREIGN KEY (notification_id) REFERENCES notifications(id) ON DELETE CASCADE,
    CONSTRAINT fk_read_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS banners (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    type ENUM('home','offer','recruitment') NOT NULL DEFAULT 'home',
    title VARCHAR(150) DEFAULT NULL,
    image_path VARCHAR(255) NOT NULL,
    link VARCHAR(255) DEFAULT NULL,
    status TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 15. CMS_PAGES
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS cms_pages (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(100) NOT NULL,
    title VARCHAR(150) NOT NULL,
    content LONGTEXT DEFAULT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_cms_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 16. SETTINGS (key-value store)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS settings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL,
    setting_value TEXT DEFAULT NULL,
    UNIQUE KEY uq_setting_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 17. ACTIVITY_LOGS
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS activity_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED DEFAULT NULL,
    admin_id INT UNSIGNED DEFAULT NULL,
    action VARCHAR(100) NOT NULL,
    description VARCHAR(255) DEFAULT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_log_user (user_id),
    KEY idx_log_admin (admin_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;