-- OFFICE-GENIUS Refer2Earn database foundation
-- Target: MySQL / MariaDB with InnoDB and utf8mb4 (typical cPanel hosting).
-- Import this only into a new, dedicated database after reviewing the setup guide.
-- No demo records, default accounts, or passwords are created here.

CREATE TABLE IF NOT EXISTS og_refer_users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    email VARCHAR(254) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(20) NOT NULL COMMENT 'admin or referrer; authorization is enforced by the backend',
    status VARCHAR(24) NOT NULL DEFAULT 'pending_email' COMMENT 'pending_email, active, suspended, closed',
    email_verified_at DATETIME NULL,
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_og_refer_users_email (email),
    KEY ix_og_refer_users_role_status (role, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS og_refer_profiles (
    user_id BIGINT UNSIGNED NOT NULL,
    public_referrer_id VARCHAR(40) NOT NULL,
    share_token_hash CHAR(64) NULL COMMENT 'SHA-256 of an opaque token; never store the share token itself',
    verified_identity_hash CHAR(64) NULL COMMENT 'HMAC from a server-only key; supports one account per verified person',
    payout_method VARCHAR(24) NULL COMMENT 'GCash, Maya, or Bank transfer',
    payout_details_ciphertext LONGTEXT NULL COMMENT 'Encrypt sensitive payout details in PHP; encryption key must stay outside webroot and DB',
    profile_data JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id),
    UNIQUE KEY uq_og_refer_profiles_public_id (public_referrer_id),
    UNIQUE KEY uq_og_refer_profiles_share_hash (share_token_hash),
    UNIQUE KEY uq_og_refer_profiles_identity_hash (verified_identity_hash),
    CONSTRAINT fk_og_refer_profiles_user FOREIGN KEY (user_id) REFERENCES og_refer_users (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS og_refer_candidates (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    protected_identity_hash CHAR(64) NULL COMMENT 'HMAC of normalized identity data using a server-only key; not a plain hash',
    full_name VARCHAR(200) NOT NULL,
    email VARCHAR(254) NOT NULL,
    mobile VARCHAR(40) NULL,
    birth_date DATE NULL,
    former_employee_status VARCHAR(24) NOT NULL DEFAULT 'unknown' COMMENT 'unknown, no, yes, under_review',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY ix_og_refer_candidates_identity (protected_identity_hash),
    KEY ix_og_refer_candidates_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS og_refer_applications (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    reference_code VARCHAR(40) NOT NULL,
    candidate_id BIGINT UNSIGNED NOT NULL,
    job_id VARCHAR(100) NOT NULL,
    partner_name VARCHAR(120) NOT NULL,
    preferred_site VARCHAR(200) NULL,
    application_status VARCHAR(24) NOT NULL DEFAULT 'new',
    privacy_notice_version VARCHAR(40) NOT NULL,
    consent_recorded_at DATETIME NOT NULL,
    referrer_user_id BIGINT UNSIGNED NULL COMMENT 'Resolved server-side from a valid referrer share token',
    referrer_token_hash CHAR(64) NULL,
    resume_storage_key VARCHAR(500) NULL COMMENT 'Private storage path/key; never a public URL',
    application_payload JSON NULL COMMENT 'Only fields needed for recruitment; do not store unnecessary sensitive data',
    applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_og_refer_applications_reference (reference_code),
    KEY ix_og_refer_applications_candidate_partner_date (candidate_id, partner_name, applied_at),
    KEY ix_og_refer_applications_referrer_date (referrer_user_id, applied_at),
    CONSTRAINT fk_og_refer_applications_candidate FOREIGN KEY (candidate_id) REFERENCES og_refer_candidates (id) ON DELETE RESTRICT,
    CONSTRAINT fk_og_refer_applications_referrer FOREIGN KEY (referrer_user_id) REFERENCES og_refer_users (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS og_refer_referrals (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    public_referral_id VARCHAR(40) NOT NULL,
    referrer_user_id BIGINT UNSIGNED NOT NULL,
    candidate_id BIGINT UNSIGNED NOT NULL,
    application_id BIGINT UNSIGNED NOT NULL,
    job_id VARCHAR(100) NOT NULL,
    partner_name VARCHAR(120) NOT NULL,
    referred_at DATETIME NOT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'submitted' COMMENT 'Values defined in REFER2EARN-WORKFLOW-SPEC.md',
    final_outcome VARCHAR(24) NULL COMMENT 'pending, passed, failed, declined, withdrawn',
    final_outcome_at DATETIME NULL,
    official_start_date DATE NULL,
    portability_consent TINYINT(1) NOT NULL DEFAULT 0,
    closed_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_og_refer_referrals_public_id (public_referral_id),
    UNIQUE KEY uq_og_refer_referrals_application (application_id),
    KEY ix_og_refer_referrals_owner_status (referrer_user_id, status),
    KEY ix_og_refer_referrals_candidate_partner_date (candidate_id, partner_name, referred_at),
    CONSTRAINT fk_og_refer_referrals_user FOREIGN KEY (referrer_user_id) REFERENCES og_refer_users (id) ON DELETE RESTRICT,
    CONSTRAINT fk_og_refer_referrals_candidate FOREIGN KEY (candidate_id) REFERENCES og_refer_candidates (id) ON DELETE RESTRICT,
    CONSTRAINT fk_og_refer_referrals_application FOREIGN KEY (application_id) REFERENCES og_refer_applications (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS og_refer_milestones (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    referral_id BIGINT UNSIGNED NOT NULL,
    milestone_key VARCHAR(24) NOT NULL COMMENT 'outcome, first_day, day30, day90, day180',
    state VARCHAR(24) NOT NULL DEFAULT 'pending' COMMENT 'pending, approved, forfeited, failed, not_eligible',
    amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    eligibility_code VARCHAR(48) NULL,
    verified_by BIGINT UNSIGNED NULL,
    verified_at DATETIME NULL,
    evidence_reference VARCHAR(500) NULL COMMENT 'Private evidence reference; avoid putting sensitive evidence in audit text',
    decision_note TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_og_refer_milestones_referral_key (referral_id, milestone_key),
    KEY ix_og_refer_milestones_state (state, milestone_key),
    CONSTRAINT fk_og_refer_milestones_referral FOREIGN KEY (referral_id) REFERENCES og_refer_referrals (id) ON DELETE RESTRICT,
    CONSTRAINT fk_og_refer_milestones_verifier FOREIGN KEY (verified_by) REFERENCES og_refer_users (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS og_refer_one_time_tokens (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    purpose VARCHAR(24) NOT NULL COMMENT 'email_verify, password_reset',
    token_hash CHAR(64) NOT NULL,
    expires_at DATETIME NOT NULL,
    used_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_og_refer_tokens_hash (token_hash),
    KEY ix_og_refer_tokens_user_purpose (user_id, purpose, expires_at),
    CONSTRAINT fk_og_refer_tokens_user FOREIGN KEY (user_id) REFERENCES og_refer_users (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS og_refer_audit_events (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    actor_user_id BIGINT UNSIGNED NULL,
    actor_role VARCHAR(20) NOT NULL,
    action VARCHAR(80) NOT NULL,
    record_type VARCHAR(40) NOT NULL,
    record_id VARCHAR(80) NULL,
    reason TEXT NULL,
    before_data JSON NULL,
    after_data JSON NULL,
    request_id VARCHAR(80) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY ix_og_refer_audit_actor_date (actor_user_id, created_at),
    KEY ix_og_refer_audit_record (record_type, record_id, created_at),
    CONSTRAINT fk_og_refer_audit_actor FOREIGN KEY (actor_user_id) REFERENCES og_refer_users (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS og_refer_schema_version (
    version INT UNSIGNED NOT NULL,
    applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (version)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO og_refer_schema_version (version) VALUES (1);