-- ============================================================================
-- SaaS Job Portal Platform — Full Database Schema
-- MySQL 8.0+, InnoDB, utf8mb4
-- Naming: snake_case tables, singular FK columns (*_id), timestamps everywhere
-- ============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================================
-- SECTION 1: IDENTITY, TENANCY, ACCESS CONTROL
-- ============================================================================

CREATE TABLE roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    slug VARCHAR(50) NOT NULL UNIQUE,        -- super_admin, agency_owner, recruiter, candidate, support_staff
    description VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    module VARCHAR(50) NOT NULL,             -- jobs, candidates, agencies, billing, reports...
    name VARCHAR(100) NOT NULL,              -- jobs.create, jobs.edit, candidates.unlock...
    slug VARCHAR(150) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE role_permissions (
    role_id BIGINT UNSIGNED NOT NULL,
    permission_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE agencies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_name VARCHAR(150) NOT NULL,
    slug VARCHAR(160) NOT NULL UNIQUE,
    logo_path VARCHAR(255) NULL,
    website VARCHAR(255) NULL,
    email VARCHAR(150) NOT NULL,
    phone VARCHAR(30) NULL,
    industry VARCHAR(100) NULL,
    company_size VARCHAR(30) NULL,           -- 1-10, 11-50, 51-200...
    description TEXT NULL,
    registration_number VARCHAR(100) NULL,
    tax_number VARCHAR(100) NULL,
    address TEXT NULL,
    verification_status ENUM('pending','under_review','verified','rejected') NOT NULL DEFAULT 'pending',
    status ENUM('active','suspended','trial_expired','cancelled') NOT NULL DEFAULT 'active',
    owner_user_id BIGINT UNSIGNED NULL,      -- FK added after users table
    timezone VARCHAR(50) DEFAULT 'Asia/Karachi',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    INDEX idx_agency_status (status),
    INDEX idx_agency_verification (verification_status)
) ENGINE=InnoDB;

CREATE TABLE agency_verification_documents (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    agency_id BIGINT UNSIGNED NOT NULL,
    doc_type ENUM('business_registration','tax_document','utility_bill','other') NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    reviewed_by BIGINT UNSIGNED NULL,
    reviewed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (agency_id) REFERENCES agencies(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    agency_id BIGINT UNSIGNED NULL,          -- NULL for candidates & super admin
    role_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    phone VARCHAR(30) NULL,
    password_hash VARCHAR(255) NOT NULL,
    status ENUM('active','inactive','banned','pending_verification') NOT NULL DEFAULT 'pending_verification',
    two_factor_enabled TINYINT(1) NOT NULL DEFAULT 0,
    two_factor_secret VARCHAR(100) NULL,
    email_verified_at TIMESTAMP NULL,
    last_login_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    FOREIGN KEY (role_id) REFERENCES roles(id),
    FOREIGN KEY (agency_id) REFERENCES agencies(id) ON DELETE CASCADE,
    INDEX idx_users_agency (agency_id),
    INDEX idx_users_role (role_id),
    INDEX idx_users_status (status)
) ENGINE=InnoDB;

ALTER TABLE agencies ADD CONSTRAINT fk_agency_owner FOREIGN KEY (owner_user_id) REFERENCES users(id);

CREATE TABLE user_devices (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    device_fingerprint VARCHAR(255) NOT NULL,
    device_name VARCHAR(150) NULL,
    last_seen_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_trusted TINYINT(1) DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE login_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    device VARCHAR(150) NULL,
    browser VARCHAR(150) NULL,
    status ENUM('success','failed') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_login_user_date (user_id, created_at)
) ENGINE=InnoDB;

CREATE TABLE password_resets (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(150) NOT NULL,
    token VARCHAR(255) NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_pwreset_email (email)
) ENGINE=InnoDB;

-- ============================================================================
-- SECTION 2: CANDIDATE PROFILE DOMAIN
-- ============================================================================

CREATE TABLE candidate_profiles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL UNIQUE,
    profile_picture_path VARCHAR(255) NULL,
    cover_letter TEXT NULL,
    headline VARCHAR(150) NULL,
    salary_expectation_min DECIMAL(12,2) NULL,
    salary_expectation_max DECIMAL(12,2) NULL,
    salary_currency VARCHAR(10) DEFAULT 'PKR',
    preferred_locations JSON NULL,
    availability_status ENUM('open_to_work','actively_looking','not_looking','available_immediately') DEFAULT 'not_looking',
    notice_period_end DATE NULL,
    is_public TINYINT(1) DEFAULT 1,
    hide_email TINYINT(1) DEFAULT 0,
    hide_mobile TINYINT(1) DEFAULT 0,
    hide_employer TINYINT(1) DEFAULT 0,
    profile_completion_pct TINYINT UNSIGNED DEFAULT 0,
    last_active_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_candidate_availability (availability_status),
    FULLTEXT INDEX ft_candidate_headline (headline)
) ENGINE=InnoDB;

CREATE TABLE candidate_experiences (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    candidate_profile_id BIGINT UNSIGNED NOT NULL,
    job_title VARCHAR(150) NOT NULL,
    company_name VARCHAR(150) NOT NULL,
    location VARCHAR(150) NULL,
    start_date DATE NOT NULL,
    end_date DATE NULL,
    is_current TINYINT(1) DEFAULT 0,
    description TEXT NULL,
    FOREIGN KEY (candidate_profile_id) REFERENCES candidate_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE candidate_education (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    candidate_profile_id BIGINT UNSIGNED NOT NULL,
    institution VARCHAR(150) NOT NULL,
    degree VARCHAR(150) NOT NULL,
    field_of_study VARCHAR(150) NULL,
    start_year YEAR NULL,
    end_year YEAR NULL,
    grade VARCHAR(30) NULL,
    FOREIGN KEY (candidate_profile_id) REFERENCES candidate_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE candidate_certifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    candidate_profile_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(150) NOT NULL,
    issuing_org VARCHAR(150) NULL,
    issue_date DATE NULL,
    expiry_date DATE NULL,
    credential_url VARCHAR(255) NULL,
    FOREIGN KEY (candidate_profile_id) REFERENCES candidate_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE skills (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    category VARCHAR(100) NULL
) ENGINE=InnoDB;

CREATE TABLE candidate_skills (
    candidate_profile_id BIGINT UNSIGNED NOT NULL,
    skill_id BIGINT UNSIGNED NOT NULL,
    proficiency ENUM('beginner','intermediate','advanced','expert') DEFAULT 'intermediate',
    years_experience TINYINT UNSIGNED NULL,
    PRIMARY KEY (candidate_profile_id, skill_id),
    FOREIGN KEY (candidate_profile_id) REFERENCES candidate_profiles(id) ON DELETE CASCADE,
    FOREIGN KEY (skill_id) REFERENCES skills(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE candidate_languages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    candidate_profile_id BIGINT UNSIGNED NOT NULL,
    language VARCHAR(100) NOT NULL,
    proficiency ENUM('basic','conversational','fluent','native') DEFAULT 'conversational',
    FOREIGN KEY (candidate_profile_id) REFERENCES candidate_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE candidate_projects (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    candidate_profile_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(150) NOT NULL,
    description TEXT NULL,
    project_url VARCHAR(255) NULL,
    start_date DATE NULL,
    end_date DATE NULL,
    FOREIGN KEY (candidate_profile_id) REFERENCES candidate_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE candidate_portfolio_links (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    candidate_profile_id BIGINT UNSIGNED NOT NULL,
    label VARCHAR(100) NULL,
    url VARCHAR(255) NOT NULL,
    FOREIGN KEY (candidate_profile_id) REFERENCES candidate_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE candidate_references (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    candidate_profile_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    relationship VARCHAR(100) NULL,
    company VARCHAR(150) NULL,
    email VARCHAR(150) NULL,
    phone VARCHAR(30) NULL,
    FOREIGN KEY (candidate_profile_id) REFERENCES candidate_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE resumes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    candidate_profile_id BIGINT UNSIGNED NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    original_filename VARCHAR(255) NOT NULL,
    is_primary TINYINT(1) DEFAULT 0,
    download_count INT UNSIGNED DEFAULT 0,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (candidate_profile_id) REFERENCES candidate_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================================
-- SECTION 3: JOBS, APPLICATIONS, ATS
-- ============================================================================

CREATE TABLE job_categories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(120) NOT NULL UNIQUE,
    parent_id BIGINT UNSIGNED NULL,
    FOREIGN KEY (parent_id) REFERENCES job_categories(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE jobs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    agency_id BIGINT UNSIGNED NOT NULL,
    posted_by_user_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT NOT NULL,
    department VARCHAR(100) NULL,
    category_id BIGINT UNSIGNED NULL,
    experience_level ENUM('entry','mid','senior','lead','executive') DEFAULT 'mid',
    salary_min DECIMAL(12,2) NULL,
    salary_max DECIMAL(12,2) NULL,
    salary_currency VARCHAR(10) DEFAULT 'PKR',
    is_salary_visible TINYINT(1) DEFAULT 1,
    location VARCHAR(150) NULL,
    is_remote TINYINT(1) DEFAULT 0,
    is_hybrid TINYINT(1) DEFAULT 0,
    required_skills JSON NULL,
    employment_type ENUM('full_time','part_time','contract','internship','freelance') DEFAULT 'full_time',
    education_requirement VARCHAR(150) NULL,
    benefits TEXT NULL,
    status ENUM('draft','published','expired','closed') DEFAULT 'draft',
    is_featured TINYINT(1) DEFAULT 0,
    is_urgent TINYINT(1) DEFAULT 0,
    views_count INT UNSIGNED DEFAULT 0,
    expires_at DATE NULL,
    seo_slug VARCHAR(220) NOT NULL UNIQUE,
    meta_title VARCHAR(200) NULL,
    meta_description VARCHAR(320) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    FOREIGN KEY (agency_id) REFERENCES agencies(id) ON DELETE CASCADE,
    FOREIGN KEY (posted_by_user_id) REFERENCES users(id),
    FOREIGN KEY (category_id) REFERENCES job_categories(id) ON DELETE SET NULL,
    INDEX idx_jobs_agency (agency_id),
    INDEX idx_jobs_status_expiry (status, expires_at),
    INDEX idx_jobs_location (location),
    FULLTEXT INDEX ft_jobs_title_desc (title, description)
) ENGINE=InnoDB;

CREATE TABLE job_applications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    job_id BIGINT UNSIGNED NOT NULL,
    candidate_profile_id BIGINT UNSIGNED NOT NULL,
    resume_id BIGINT UNSIGNED NULL,
    cover_note TEXT NULL,
    status ENUM('applied','under_review','shortlisted','interview_scheduled','interview_completed','rejected','hired') DEFAULT 'applied',
    assigned_recruiter_id BIGINT UNSIGNED NULL,
    applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_job_candidate (job_id, candidate_profile_id),
    FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE,
    FOREIGN KEY (candidate_profile_id) REFERENCES candidate_profiles(id) ON DELETE CASCADE,
    FOREIGN KEY (resume_id) REFERENCES resumes(id) ON DELETE SET NULL,
    FOREIGN KEY (assigned_recruiter_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_app_status (status),
    INDEX idx_app_job (job_id)
) ENGINE=InnoDB;

CREATE TABLE application_status_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    job_application_id BIGINT UNSIGNED NOT NULL,
    from_status VARCHAR(30) NULL,
    to_status VARCHAR(30) NOT NULL,
    changed_by_user_id BIGINT UNSIGNED NOT NULL,
    note VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (job_application_id) REFERENCES job_applications(id) ON DELETE CASCADE,
    FOREIGN KEY (changed_by_user_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE application_notes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    job_application_id BIGINT UNSIGNED NOT NULL,
    author_user_id BIGINT UNSIGNED NOT NULL,
    note TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (job_application_id) REFERENCES job_applications(id) ON DELETE CASCADE,
    FOREIGN KEY (author_user_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE candidate_tags (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    agency_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(60) NOT NULL,
    color VARCHAR(20) DEFAULT '#6c757d',
    FOREIGN KEY (agency_id) REFERENCES agencies(id) ON DELETE CASCADE,
    UNIQUE KEY uq_agency_tag (agency_id, name)
) ENGINE=InnoDB;

CREATE TABLE candidate_tag_map (
    tag_id BIGINT UNSIGNED NOT NULL,
    candidate_profile_id BIGINT UNSIGNED NOT NULL,
    agency_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (tag_id, candidate_profile_id),
    FOREIGN KEY (tag_id) REFERENCES candidate_tags(id) ON DELETE CASCADE,
    FOREIGN KEY (candidate_profile_id) REFERENCES candidate_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE saved_candidates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    agency_id BIGINT UNSIGNED NOT NULL,
    recruiter_id BIGINT UNSIGNED NOT NULL,
    candidate_profile_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (agency_id) REFERENCES agencies(id) ON DELETE CASCADE,
    FOREIGN KEY (recruiter_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (candidate_profile_id) REFERENCES candidate_profiles(id) ON DELETE CASCADE,
    UNIQUE KEY uq_saved (recruiter_id, candidate_profile_id)
) ENGINE=InnoDB;

CREATE TABLE shortlists (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    agency_id BIGINT UNSIGNED NOT NULL,
    created_by_user_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    job_id BIGINT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (agency_id) REFERENCES agencies(id) ON DELETE CASCADE,
    FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE shortlist_candidates (
    shortlist_id BIGINT UNSIGNED NOT NULL,
    candidate_profile_id BIGINT UNSIGNED NOT NULL,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (shortlist_id, candidate_profile_id),
    FOREIGN KEY (shortlist_id) REFERENCES shortlists(id) ON DELETE CASCADE,
    FOREIGN KEY (candidate_profile_id) REFERENCES candidate_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE saved_searches (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    agency_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    search_type ENUM('candidate','job') NOT NULL,
    filters JSON NOT NULL,
    alert_enabled TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (agency_id) REFERENCES agencies(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE candidate_unlocks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    agency_id BIGINT UNSIGNED NOT NULL,
    recruiter_id BIGINT UNSIGNED NOT NULL,
    candidate_profile_id BIGINT UNSIGNED NOT NULL,
    unlock_type ENUM('contact_reveal','resume_download','full_profile') NOT NULL,
    credits_spent INT UNSIGNED DEFAULT 0,
    unlocked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (agency_id) REFERENCES agencies(id) ON DELETE CASCADE,
    FOREIGN KEY (recruiter_id) REFERENCES users(id),
    FOREIGN KEY (candidate_profile_id) REFERENCES candidate_profiles(id) ON DELETE CASCADE,
    INDEX idx_unlock_agency_candidate (agency_id, candidate_profile_id)
) ENGINE=InnoDB;

-- ============================================================================
-- SECTION 4: INTERVIEWS
-- ============================================================================

CREATE TABLE interviews (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    job_application_id BIGINT UNSIGNED NOT NULL,
    scheduled_by_user_id BIGINT UNSIGNED NOT NULL,
    scheduled_at DATETIME NOT NULL,
    duration_minutes SMALLINT UNSIGNED DEFAULT 30,
    mode ENUM('zoom','google_meet','onsite','phone') DEFAULT 'zoom',
    meeting_link VARCHAR(255) NULL,
    location TEXT NULL,
    status ENUM('scheduled','rescheduled','cancelled','completed') DEFAULT 'scheduled',
    feedback TEXT NULL,
    feedback_rating TINYINT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (job_application_id) REFERENCES job_applications(id) ON DELETE CASCADE,
    FOREIGN KEY (scheduled_by_user_id) REFERENCES users(id),
    INDEX idx_interview_datetime (scheduled_at)
) ENGINE=InnoDB;

-- ============================================================================
-- SECTION 5: MESSAGING
-- ============================================================================

CREATE TABLE conversations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    job_application_id BIGINT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (job_application_id) REFERENCES job_applications(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE conversation_participants (
    conversation_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (conversation_id, user_id),
    FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE messages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    conversation_id BIGINT UNSIGNED NOT NULL,
    sender_id BIGINT UNSIGNED NOT NULL,
    body TEXT NOT NULL,
    is_read TINYINT(1) DEFAULT 0,
    read_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
    FOREIGN KEY (sender_id) REFERENCES users(id),
    INDEX idx_msg_conv (conversation_id, created_at)
) ENGINE=InnoDB;

CREATE TABLE message_attachments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    message_id BIGINT UNSIGNED NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    original_filename VARCHAR(255) NOT NULL,
    file_size_kb INT UNSIGNED NULL,
    FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================================
-- SECTION 6: SAAS BILLING — SUBSCRIPTIONS, CREDITS, WALLET, PAYMENTS
-- ============================================================================

CREATE TABLE subscription_plans (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,              -- Free, Basic, Professional, Enterprise
    slug VARCHAR(100) NOT NULL UNIQUE,
    price_monthly DECIMAL(10,2) DEFAULT 0,
    price_yearly DECIMAL(10,2) DEFAULT 0,
    candidate_view_limit INT DEFAULT 0,      -- -1 = unlimited
    contact_reveal_limit INT DEFAULT 0,
    resume_download_limit INT DEFAULT 0,
    recruiter_limit INT DEFAULT 1,
    job_posting_limit INT DEFAULT 1,
    features JSON NULL,
    is_active TINYINT(1) DEFAULT 1,
    sort_order TINYINT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE agency_subscriptions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    agency_id BIGINT UNSIGNED NOT NULL,
    plan_id BIGINT UNSIGNED NOT NULL,
    billing_cycle ENUM('monthly','yearly') DEFAULT 'monthly',
    status ENUM('trialing','active','past_due','cancelled','expired') DEFAULT 'trialing',
    current_period_start DATE NOT NULL,
    current_period_end DATE NOT NULL,
    auto_renew TINYINT(1) DEFAULT 1,
    cancelled_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (agency_id) REFERENCES agencies(id) ON DELETE CASCADE,
    FOREIGN KEY (plan_id) REFERENCES subscription_plans(id),
    INDEX idx_agency_sub_status (status, current_period_end)
) ENGINE=InnoDB;

-- Tracks per-period consumption against plan limits (resets each billing cycle)
CREATE TABLE subscription_usage (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    agency_subscription_id BIGINT UNSIGNED NOT NULL,
    candidate_views_used INT UNSIGNED DEFAULT 0,
    contact_reveals_used INT UNSIGNED DEFAULT 0,
    resume_downloads_used INT UNSIGNED DEFAULT 0,
    jobs_posted_used INT UNSIGNED DEFAULT 0,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    FOREIGN KEY (agency_subscription_id) REFERENCES agency_subscriptions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE credit_costs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    action ENUM('contact_reveal','resume_download','candidate_unlock','messaging') NOT NULL UNIQUE,
    credit_cost INT UNSIGNED NOT NULL DEFAULT 1,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE credit_balances (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    agency_id BIGINT UNSIGNED NOT NULL UNIQUE,
    balance INT NOT NULL DEFAULT 0,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (agency_id) REFERENCES agencies(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE credit_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    agency_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NULL,
    type ENUM('purchase','usage','refund','expiry','bonus') NOT NULL,
    amount INT NOT NULL,                     -- positive=credit, negative=debit
    balance_after INT NOT NULL,
    reference_type VARCHAR(50) NULL,         -- e.g. candidate_unlocks
    reference_id BIGINT UNSIGNED NULL,
    expires_at DATE NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (agency_id) REFERENCES agencies(id) ON DELETE CASCADE,
    INDEX idx_credit_tx_agency (agency_id, created_at)
) ENGINE=InnoDB;

CREATE TABLE wallets (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    agency_id BIGINT UNSIGNED NOT NULL UNIQUE,
    balance DECIMAL(12,2) NOT NULL DEFAULT 0,
    currency VARCHAR(10) DEFAULT 'PKR',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (agency_id) REFERENCES agencies(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE wallet_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    wallet_id BIGINT UNSIGNED NOT NULL,
    type ENUM('deposit','withdrawal','credit_purchase','refund','adjustment') NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    balance_after DECIMAL(12,2) NOT NULL,
    reference_type VARCHAR(50) NULL,
    reference_id BIGINT UNSIGNED NULL,
    note VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (wallet_id) REFERENCES wallets(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE invoices (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    agency_id BIGINT UNSIGNED NOT NULL,
    invoice_number VARCHAR(50) NOT NULL UNIQUE,
    subtotal DECIMAL(12,2) NOT NULL,
    tax_percentage DECIMAL(5,2) DEFAULT 0,
    tax_amount DECIMAL(12,2) DEFAULT 0,
    total DECIMAL(12,2) NOT NULL,
    currency VARCHAR(10) DEFAULT 'PKR',
    status ENUM('draft','sent','paid','overdue','void') DEFAULT 'draft',
    due_date DATE NULL,
    pdf_path VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (agency_id) REFERENCES agencies(id) ON DELETE CASCADE,
    INDEX idx_invoice_status (status)
) ENGINE=InnoDB;

CREATE TABLE invoice_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_id BIGINT UNSIGNED NOT NULL,
    description VARCHAR(255) NOT NULL,
    quantity INT UNSIGNED DEFAULT 1,
    unit_price DECIMAL(12,2) NOT NULL,
    line_total DECIMAL(12,2) NOT NULL,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE payments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    agency_id BIGINT UNSIGNED NOT NULL,
    invoice_id BIGINT UNSIGNED NULL,
    gateway ENUM('stripe','paypal','jazzcash','easypaisa','bank_transfer') NOT NULL,
    gateway_transaction_id VARCHAR(150) NULL,
    purpose ENUM('subscription','credit_purchase','wallet_topup') NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    currency VARCHAR(10) DEFAULT 'PKR',
    status ENUM('pending','success','failed','refunded') DEFAULT 'pending',
    gateway_response JSON NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (agency_id) REFERENCES agencies(id) ON DELETE CASCADE,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE SET NULL,
    INDEX idx_payment_status (status),
    INDEX idx_payment_gateway_txn (gateway_transaction_id)
) ENGINE=InnoDB;

CREATE TABLE refund_requests (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payment_id BIGINT UNSIGNED NOT NULL,
    requested_by_user_id BIGINT UNSIGNED NOT NULL,
    reason TEXT NOT NULL,
    status ENUM('pending','approved','rejected','processed') DEFAULT 'pending',
    processed_by BIGINT UNSIGNED NULL,
    processed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE CASCADE,
    FOREIGN KEY (requested_by_user_id) REFERENCES users(id)
) ENGINE=InnoDB;

-- ============================================================================
-- SECTION 7: NOTIFICATIONS
-- ============================================================================

CREATE TABLE notification_templates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    trigger_key VARCHAR(100) NOT NULL UNIQUE, -- registration, job_application, interview_invite, payment_success, subscription_expiry
    channel ENUM('email','sms','in_app','whatsapp') NOT NULL,
    subject VARCHAR(200) NULL,
    body TEXT NOT NULL,
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    type VARCHAR(100) NOT NULL,
    payload JSON NULL,
    channel ENUM('email','sms','in_app','whatsapp') NOT NULL,
    is_read TINYINT(1) DEFAULT 0,
    sent_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_notif_user_read (user_id, is_read)
) ENGINE=InnoDB;

-- ============================================================================
-- SECTION 8: ACTIVITY & AUDIT
-- ============================================================================

CREATE TABLE activity_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    agency_id BIGINT UNSIGNED NULL,
    role VARCHAR(50) NULL,
    action VARCHAR(100) NOT NULL,            -- login, logout, registration, profile_view, resume_view, resume_download, contact_reveal, job_apply, interview_schedule, payment, credit_usage
    ip_address VARCHAR(45) NULL,
    device VARCHAR(150) NULL,
    browser VARCHAR(150) NULL,
    metadata JSON NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_activity_user_date (user_id, created_at),
    INDEX idx_activity_action (action)
) ENGINE=InnoDB;

CREATE TABLE audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    module VARCHAR(100) NOT NULL,
    action VARCHAR(100) NOT NULL,            -- create, update, delete, status_change
    record_id BIGINT UNSIGNED NULL,
    old_value JSON NULL,
    new_value JSON NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_audit_module (module, record_id)
) ENGINE=InnoDB;

-- ============================================================================
-- SECTION 9: SETTINGS
-- ============================================================================

CREATE TABLE settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `group` VARCHAR(50) NOT NULL,             -- general, smtp, payment, credit, package, seo, notification
    `key` VARCHAR(100) NOT NULL,
    `value` TEXT NULL,
    is_encrypted TINYINT(1) DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_setting_group_key (`group`, `key`)
) ENGINE=InnoDB;

-- ============================================================================
-- SECTION 10: API TOKENS & RATE LIMITING
-- ============================================================================

CREATE TABLE api_tokens (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    token_hash VARCHAR(255) NOT NULL,
    refresh_token_hash VARCHAR(255) NULL,
    name VARCHAR(100) NULL,
    abilities JSON NULL,
    expires_at TIMESTAMP NULL,
    revoked_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_token_hash (token_hash)
) ENGINE=InnoDB;

CREATE TABLE rate_limit_hits (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    identifier VARCHAR(150) NOT NULL,        -- user_id / ip / api_key
    route VARCHAR(150) NOT NULL,
    hit_count INT UNSIGNED DEFAULT 1,
    window_start TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_rate_identifier_route (identifier, route)
) ENGINE=InnoDB;

-- ============================================================================
-- SECTION 11: COMPANY REVIEWS
-- ============================================================================

CREATE TABLE company_reviews (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    agency_id BIGINT UNSIGNED NOT NULL,
    reviewer_user_id BIGINT UNSIGNED NOT NULL,
    rating TINYINT UNSIGNED NOT NULL,        -- 1-5
    title VARCHAR(150) NULL,
    review_text TEXT NULL,
    status ENUM('pending','approved','rejected') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (agency_id) REFERENCES agencies(id) ON DELETE CASCADE,
    FOREIGN KEY (reviewer_user_id) REFERENCES users(id)
) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================================
-- SEED: Core roles, permissions, subscription plans, credit costs
-- ============================================================================

INSERT INTO roles (name, slug, description) VALUES
('Super Admin', 'super_admin', 'Platform owner, full access'),
('Agency Owner', 'agency_owner', 'Owns an agency tenant'),
('Recruiter', 'recruiter', 'Agency staff member'),
('Candidate', 'candidate', 'Job seeker'),
('Support Staff', 'support_staff', 'Optional agency support role');

INSERT INTO subscription_plans (name, slug, price_monthly, price_yearly, candidate_view_limit, contact_reveal_limit, resume_download_limit, recruiter_limit, job_posting_limit, sort_order) VALUES
('Free', 'free', 0, 0, 20, 5, 5, 1, 2, 1),
('Basic', 'basic', 4999, 49990, 100, 30, 30, 3, 10, 2),
('Professional', 'professional', 14999, 149990, 500, 150, 150, 10, 50, 3),
('Enterprise', 'enterprise', 39999, 399990, -1, -1, -1, -1, -1, 4);

INSERT INTO credit_costs (action, credit_cost) VALUES
('contact_reveal', 2),
('resume_download', 1),
('candidate_unlock', 3),
('messaging', 1);