-- ============================================================================
-- GymFlow Mobile App — Database Migrations (Phase 0)
-- ============================================================================
-- Scope: MVP only, per docs/GymFlow_Mobile_App_Complete_Specification_FINAL.md
--        (Member/Trainer/Admin MVP, §50). Excludes: partner finder, comments,
--        video posts, in-app chat, online checkout — deferred to a later phase.
--
-- IMPORTANT — read before running:
--   1. This script is NOT auto-executed by any application code. Run it
--      manually against a COPY of the database first.
--   2. New tables use `CREATE TABLE IF NOT EXISTS` (safe to re-run — verified
--      against the live server, MySQL 8.0.31). The ALTER TABLE statements in
--      section 1 do NOT use `IF NOT EXISTS` on ADD COLUMN/INDEX/PRIMARY KEY:
--      that clause was tested directly against this server and rejected —
--      Oracle MySQL 8.0 does not support it on ALTER TABLE (it's a
--      MariaDB-only extension, easy to assume incorrectly). The ALTERs are
--      therefore a ONE-TIME script: running section 1 twice will error with
--      "Duplicate column name" on the second run. That is expected — do not
--      "fix" it by re-adding IF NOT EXISTS.
--   3. Nothing in this file ever stores, duplicates, or exposes
--      `branches.branchApiKey`. Branch keys are resolved only inside the
--      PHP repository layer, server-side, per spec §2.3 / §51.
--   4. Verified against the LIVE schema on 2026-07-30 (not just gf.sql,
--      which was found to be stale in places — see plan notes). Table/column
--      names below match what actually exists in the `gf` database.
-- ============================================================================

SET NAMES utf8mb4;

-- ----------------------------------------------------------------------------
-- 1. Existing-table extensions
-- ----------------------------------------------------------------------------

-- 1.1 staff: stable identity key for merging duplicate trainer rows across
--     branches, and a normalized contact number for OTP/matching.
--     NOTE: staff_role has no "Administrator" role. Mobile admin eligibility
--     is staff_role.role = 'Administration' (roleType 'Operational Staff').
--     Mobile trainer eligibility is staff_role.roleType = 'Service Staff'
--     (note the space — the original spec draft assumed 'ServiceStaff').
ALTER TABLE staff
    ADD COLUMN person_identity_key VARCHAR(100) NULL AFTER staffContact,
    ADD COLUMN normalized_contact VARCHAR(20) NULL AFTER person_identity_key;

ALTER TABLE staff
    ADD INDEX idx_staff_person_identity (person_identity_key),
    ADD INDEX idx_staff_normalized_contact (normalized_contact);

-- 1.2 members: normalized contact number for OTP/matching across branches.
ALTER TABLE members
    ADD COLUMN normalized_contact VARCHAR(20) NULL AFTER memberContact;

ALTER TABLE members
    ADD INDEX idx_members_normalized_contact (normalized_contact);

-- 1.3 accounts: fields needed to safely surface a subset of accounts as
--     "where to pay" info in the renewal-request flow, without exposing
--     internal cash-account details by default (show_in_mobile_app = 0).
ALTER TABLE accounts
    ADD COLUMN show_in_mobile_app TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN mobile_display_name VARCHAR(100) NULL,
    ADD COLUMN account_holder_name VARCHAR(120) NULL,
    ADD COLUMN bank_name VARCHAR(120) NULL,
    ADD COLUMN iban VARCHAR(50) NULL,
    ADD COLUMN payment_instructions VARCHAR(500) NULL;

-- 1.4 products: mobile store catalogue display fields.
--     NOTE: products.category is free text (not a FK to product_category.id)
--     in the live schema — the store API must filter/join by name, not id.
ALTER TABLE products
    ADD COLUMN image_url VARCHAR(700) NULL,
    ADD COLUMN show_in_mobile_app TINYINT(1) NOT NULL DEFAULT 1,
    ADD COLUMN mobile_sort_order INT NOT NULL DEFAULT 0,
    ADD COLUMN mobile_is_available TINYINT(1) NOT NULL DEFAULT 1;

-- 1.5 mobileapptheme: migrate the existing single-row theme table (which the
--     legacy apis/getAppTheme.php already queries with columns that DO NOT
--     currently exist — themeType, successClr, warningClr, errorClr, cardClr,
--     inputClr — meaning that endpoint currently errors if ever called) into
--     a proper light/dark, multi-row design. We keep the table name
--     `mobileapptheme` (singular) rather than introduce the spec's proposed
--     `mobileappthemes` — this is the real, already-referenced table.
ALTER TABLE mobileapptheme
    ADD COLUMN id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
    ADD PRIMARY KEY (id);

ALTER TABLE mobileapptheme
    ADD COLUMN themeType ENUM('light','dark') NOT NULL DEFAULT 'light' AFTER id,
    ADD COLUMN cardClr VARCHAR(10) NULL,
    ADD COLUMN inputClr VARCHAR(10) NULL,
    ADD COLUMN successClr VARCHAR(10) NULL DEFAULT '#2E7D32',
    ADD COLUMN warningClr VARCHAR(10) NULL DEFAULT '#F9A825',
    ADD COLUMN errorClr VARCHAR(10) NULL DEFAULT '#C62828',
    ADD COLUMN splashPath VARCHAR(200) NULL,
    ADD COLUMN loginBackgroundPath VARCHAR(200) NULL,
    ADD COLUMN updatedAt DATETIME NULL;

-- The single existing row becomes the 'light' row. A 'dark' row must be
-- inserted manually by an admin (via the web portal theme screen, once built)
-- or seeded here with a reasonable default — left to the web portal step,
-- not this migration, since color choices are a business decision.

-- 1.6 Keep normalized_contact self-maintaining regardless of which existing
--     PHP page writes to members/staff (members.php, new-membership.php,
--     etc. are untouched — this is a DB-level trigger instead, matching the
--     `trg_on_membership_history` pattern already used on `membership`).
--     Verified working against this exact MySQL server before running here.
DELIMITER $$
CREATE FUNCTION normalize_gym_contact(rawContact VARCHAR(30), defaultCountryCode VARCHAR(5))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    DECLARE digits VARCHAR(30);
    SET digits = REGEXP_REPLACE(rawContact, '[^0-9]', '');
    IF digits LIKE '00%' THEN
        SET digits = SUBSTRING(digits, 3);
    END IF;
    IF digits LIKE '0%' THEN
        SET digits = CONCAT(defaultCountryCode, SUBSTRING(digits, 2));
    ELSEIF digits NOT LIKE CONCAT(defaultCountryCode, '%') THEN
        SET digits = CONCAT(defaultCountryCode, digits);
    END IF;
    RETURN digits;
END$$

CREATE TRIGGER trg_members_normalize_contact
BEFORE INSERT ON members
FOR EACH ROW
BEGIN
    SET NEW.normalized_contact = normalize_gym_contact(NEW.memberContact, '92');
END$$

CREATE TRIGGER trg_members_normalize_contact_upd
BEFORE UPDATE ON members
FOR EACH ROW
BEGIN
    SET NEW.normalized_contact = normalize_gym_contact(NEW.memberContact, '92');
END$$

CREATE TRIGGER trg_staff_normalize_contact
BEFORE INSERT ON staff
FOR EACH ROW
BEGIN
    SET NEW.normalized_contact = normalize_gym_contact(NEW.staffContact, '92');
END$$

CREATE TRIGGER trg_staff_normalize_contact_upd
BEFORE UPDATE ON staff
FOR EACH ROW
BEGIN
    SET NEW.normalized_contact = normalize_gym_contact(NEW.staffContact, '92');
END$$
DELIMITER ;

-- Backfill existing rows (triggers above only cover future writes).
UPDATE members SET normalized_contact = normalize_gym_contact(memberContact, '92') WHERE memberContact IS NOT NULL;
UPDATE staff SET normalized_contact = normalize_gym_contact(staffContact, '92') WHERE staffContact IS NOT NULL;

-- NOTE: '92' is hardcoded here rather than read from mobile_app_settings.
-- default_country_code because triggers can't easily reference another
-- table's single settings row without risking recursive-lock edge cases on
-- every member/staff write. If a gym's default country code is ever not 92,
-- update the two trigger bodies (and re-run the backfill) for that gym's
-- database directly — this is a per-gym-database deployment model already
-- (spec §1), so this is a one-line edit per install, not a shared codebase
-- constraint.

-- ----------------------------------------------------------------------------
-- 2. Mobile identity & auth
-- ----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS mobile_users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_type ENUM('MEMBER','TRAINER','ADMIN') NOT NULL,

    username VARCHAR(50) NOT NULL,
    normalized_username VARCHAR(50) NOT NULL,
    display_name VARCHAR(100) NULL,
    contact_number VARCHAR(20) NOT NULL,
    normalized_contact VARCHAR(20) NOT NULL,
    email VARCHAR(120) NULL,

    password_hash VARCHAR(255) NOT NULL,
    account_status ENUM(
        'PENDING_VERIFICATION',
        'ACTIVE',
        'LOCKED',
        'DEACTIVATED',
        'BLOCKED'
    ) NOT NULL DEFAULT 'PENDING_VERIFICATION',

    contact_verified_at DATETIME NULL,
    email_verified_at DATETIME NULL,
    password_changed_at DATETIME NULL,
    last_login_at DATETIME NULL,
    failed_login_attempts SMALLINT NOT NULL DEFAULT 0,
    locked_until DATETIME NULL,

    must_change_password TINYINT(1) NOT NULL DEFAULT 0,
    terms_accepted_at DATETIME NULL,
    privacy_accepted_at DATETIME NULL,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deactivated_at DATETIME NULL,
    deleted_at DATETIME NULL,

    UNIQUE KEY uq_mobile_username (normalized_username),
    UNIQUE KEY uq_mobile_identity (normalized_contact, user_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS mobile_user_links (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mobile_user_id BIGINT UNSIGNED NOT NULL,

    linked_entity_type ENUM('MEMBER','STAFF') NOT NULL,
    linked_entity_id INT NOT NULL,
    branch_id INT NOT NULL,

    mobile_role ENUM('MEMBER','TRAINER','ADMIN') NOT NULL,
    is_primary TINYINT(1) NOT NULL DEFAULT 0,
    link_status ENUM('ACTIVE','INACTIVE','REVOKED') NOT NULL DEFAULT 'ACTIVE',

    matched_by ENUM(
        'NORMALIZED_CONTACT',
        'EMAIL',
        'IDENTITY_KEY',
        'MANUAL_ADMIN'
    ) NOT NULL,
    verified_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,

    UNIQUE KEY uq_mobile_entity_link (mobile_user_id, linked_entity_type, linked_entity_id),
    KEY idx_mobile_link_branch (mobile_user_id, branch_id, link_status),
    KEY idx_mobile_link_entity (linked_entity_type, linked_entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Admin signup is invite-only (spec §24.3), since Admin = a specific staff
-- role (`staff_role.role = 'Administration'`) rather than an existing web
-- `users` account. A web admin generates a one-time token for that staff row.
CREATE TABLE IF NOT EXISTS mobile_account_invitations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    target_type ENUM('TRAINER','ADMIN') NOT NULL,
    staff_id INT NOT NULL,
    branch_id INT NOT NULL,
    invitation_token_hash VARCHAR(255) NOT NULL,
    expires_at DATETIME NOT NULL,
    used_at DATETIME NULL,
    created_by VARCHAR(50) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_invitation_staff (staff_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------------------------------------------------------
-- 3. OTP, sessions, devices, preferences
-- ----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS mobile_otp_requests (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    purpose ENUM('SIGNUP','FORGOT_PASSWORD','CHANGE_CONTACT','VERIFY_DEVICE') NOT NULL,
    target_type ENUM('MEMBER','STAFF') NOT NULL,
    target_id INT NOT NULL,
    contact_number VARCHAR(20) NOT NULL,
    otp_hash VARCHAR(255) NOT NULL,
    channel ENUM('WHATSAPP','SMS','EMAIL') NOT NULL DEFAULT 'SMS',
    expires_at DATETIME NOT NULL,
    verified_at DATETIME NULL,
    attempts SMALLINT NOT NULL DEFAULT 0,
    resend_count SMALLINT NOT NULL DEFAULT 0,
    ip_address VARCHAR(45) NULL,
    device_id VARCHAR(120) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    invalidated_at DATETIME NULL,
    KEY idx_otp_contact (contact_number),
    KEY idx_otp_target (target_type, target_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS mobile_message_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mobile_user_id BIGINT UNSIGNED NULL,
    contact_number VARCHAR(20) NOT NULL,
    channel ENUM('WHATSAPP','SMS','EMAIL','PUSH') NOT NULL,
    template_code VARCHAR(80) NULL,
    purpose VARCHAR(80) NULL,
    provider VARCHAR(50) NULL,
    provider_message_id VARCHAR(150) NULL,
    request_payload JSON NULL,
    response_payload JSON NULL,
    delivery_status VARCHAR(30) NULL,
    error_message TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    delivered_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS mobile_refresh_tokens (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mobile_user_id BIGINT UNSIGNED NOT NULL,
    device_id VARCHAR(120) NOT NULL,
    token_hash VARCHAR(255) NOT NULL,
    token_family VARCHAR(100) NOT NULL,
    issued_at DATETIME NOT NULL,
    expires_at DATETIME NOT NULL,
    last_used_at DATETIME NULL,
    revoked_at DATETIME NULL,
    revoked_reason VARCHAR(100) NULL,
    replaced_by_token_id BIGINT UNSIGNED NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    UNIQUE KEY uq_refresh_token_hash (token_hash),
    KEY idx_refresh_user_device (mobile_user_id, device_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS mobile_devices (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mobile_user_id BIGINT UNSIGNED NOT NULL,
    device_id VARCHAR(120) NOT NULL,
    platform ENUM('ANDROID','IOS') NOT NULL,
    device_name VARCHAR(120) NULL,
    app_version VARCHAR(30) NULL,
    os_version VARCHAR(30) NULL,
    push_token TEXT NULL,
    push_provider ENUM('FCM','APNS') NULL,
    push_enabled TINYINT(1) NOT NULL DEFAULT 1,
    attendance_notifications ENUM('NONE','MEMBERS','STAFF','BOTH') NOT NULL DEFAULT 'NONE',
    marketing_notifications TINYINT(1) NOT NULL DEFAULT 1,
    last_seen_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    revoked_at DATETIME NULL,
    UNIQUE KEY uq_mobile_device (mobile_user_id, device_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS mobile_user_preferences (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mobile_user_id BIGINT UNSIGNED NOT NULL,
    theme_mode ENUM('SYSTEM','LIGHT','DARK') NOT NULL DEFAULT 'SYSTEM',
    language_code VARCHAR(10) NOT NULL DEFAULT 'en',
    biometric_login_enabled TINYINT(1) NOT NULL DEFAULT 0,
    membership_reminders TINYINT(1) NOT NULL DEFAULT 1,
    feed_notifications TINYINT(1) NOT NULL DEFAULT 1,
    plan_notifications TINYINT(1) NOT NULL DEFAULT 1,
    payment_notifications TINYINT(1) NOT NULL DEFAULT 1,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_mobile_preferences_user (mobile_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------------------------------------------------------
-- 4. App configuration
-- ----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS mobile_app_settings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    branch_id INT NULL,

    mobile_app_enabled TINYINT(1) NOT NULL DEFAULT 0,
    member_signup_enabled TINYINT(1) NOT NULL DEFAULT 1,
    trainer_signup_enabled TINYINT(1) NOT NULL DEFAULT 1,
    admin_signup_enabled TINYINT(1) NOT NULL DEFAULT 0,

    feed_enabled TINYINT(1) NOT NULL DEFAULT 1,
    member_posting_enabled TINYINT(1) NOT NULL DEFAULT 1,
    trainer_posting_enabled TINYINT(1) NOT NULL DEFAULT 1,
    store_enabled TINYINT(1) NOT NULL DEFAULT 1,
    membership_renewal_request_enabled TINYINT(1) NOT NULL DEFAULT 1,
    measurements_enabled TINYINT(1) NOT NULL DEFAULT 1,
    plans_enabled TINYINT(1) NOT NULL DEFAULT 1,

    otp_channel ENUM('WHATSAPP','SMS','EMAIL') NOT NULL DEFAULT 'SMS',
    default_country_code VARCHAR(5) NOT NULL DEFAULT '92',
    support_whatsapp VARCHAR(20) NULL,
    support_email VARCHAR(120) NULL,

    privacy_policy_url VARCHAR(500) NULL,
    terms_url VARCHAR(500) NULL,
    minimum_android_version VARCHAR(20) NULL,
    minimum_ios_version VARCHAR(20) NULL,
    latest_android_version VARCHAR(20) NULL,
    latest_ios_version VARCHAR(20) NULL,
    force_update_android TINYINT(1) NOT NULL DEFAULT 0,
    force_update_ios TINYINT(1) NOT NULL DEFAULT 0,
    maintenance_mode TINYINT(1) NOT NULL DEFAULT 0,
    maintenance_message VARCHAR(500) NULL,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    UNIQUE KEY uq_mobile_app_setting_branch (branch_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Seed one global row (branch_id NULL) so /config/bootstrap always has a
-- row to read even before an admin visits the (future) web settings page.
INSERT INTO mobile_app_settings (branch_id, mobile_app_enabled)
SELECT NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM mobile_app_settings WHERE branch_id IS NULL);

CREATE TABLE IF NOT EXISTS mobile_feature_flags (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    branch_id INT NULL,
    feature_code VARCHAR(80) NOT NULL,
    enabled TINYINT(1) NOT NULL DEFAULT 0,
    config_json JSON NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_feature_branch (branch_id, feature_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------------------------------------------------------
-- 5. Feed (MVP: text/image posts + likes + reports; no comments/partner-finder)
-- ----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS mobile_posts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    author_mobile_user_id BIGINT UNSIGNED NULL,
    author_type ENUM('MEMBER','TRAINER','ADMIN','SYSTEM') NOT NULL,
    post_type ENUM('TEXT','IMAGE','NOTICE','ACHIEVEMENT','MEMBERSHIP_REMINDER') NOT NULL,
    caption TEXT NULL,
    visibility ENUM('ALL','MEMBERS','TRAINERS','ADMINS','BRANCH') NOT NULL DEFAULT 'ALL',
    branch_id INT NOT NULL,

    moderation_status ENUM('PUBLISHED','PENDING_REVIEW','HIDDEN','DELETED') NOT NULL DEFAULT 'PUBLISHED',

    like_count INT NOT NULL DEFAULT 0,
    comment_count INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    deleted_by_mobile_user_id BIGINT UNSIGNED NULL,

    KEY idx_posts_feed (branch_id, moderation_status, created_at),
    KEY idx_posts_author (author_mobile_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS mobile_post_media (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id BIGINT UNSIGNED NOT NULL,
    media_type ENUM('IMAGE') NOT NULL DEFAULT 'IMAGE',
    original_url VARCHAR(700) NOT NULL,
    thumbnail_url VARCHAR(700) NULL,
    width INT NULL,
    height INT NULL,
    size_bytes BIGINT NULL,
    sort_order SMALLINT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_post_media_post (post_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS mobile_post_likes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id BIGINT UNSIGNED NOT NULL,
    mobile_user_id BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_post_like (post_id, mobile_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS mobile_content_reports (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reporter_mobile_user_id BIGINT UNSIGNED NOT NULL,
    entity_type ENUM('POST','PROFILE') NOT NULL,
    entity_id BIGINT UNSIGNED NOT NULL,
    reason_code ENUM('SPAM','HARASSMENT','INAPPROPRIATE','PRIVACY','MISINFORMATION','OTHER') NOT NULL,
    notes VARCHAR(1000) NULL,
    status ENUM('OPEN','REVIEWING','RESOLVED','DISMISSED') NOT NULL DEFAULT 'OPEN',
    reviewed_by VARCHAR(50) NULL,
    reviewed_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------------------------------------------------------
-- 6. Member measurements / progress
-- ----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS member_measurements (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    member_id INT NOT NULL,
    branch_id INT NOT NULL,

    measurement_date DATE NOT NULL,
    height_cm DECIMAL(6,2) NULL,
    weight_kg DECIMAL(6,2) NULL,
    body_fat_percent DECIMAL(5,2) NULL,
    muscle_mass_kg DECIMAL(6,2) NULL,
    bmi DECIMAL(5,2) NULL,

    neck_cm DECIMAL(6,2) NULL,
    shoulders_cm DECIMAL(6,2) NULL,
    chest_cm DECIMAL(6,2) NULL,
    waist_cm DECIMAL(6,2) NULL,
    hips_cm DECIMAL(6,2) NULL,
    left_arm_cm DECIMAL(6,2) NULL,
    right_arm_cm DECIMAL(6,2) NULL,
    left_thigh_cm DECIMAL(6,2) NULL,
    right_thigh_cm DECIMAL(6,2) NULL,
    left_calf_cm DECIMAL(6,2) NULL,
    right_calf_cm DECIMAL(6,2) NULL,

    notes TEXT NULL,
    source ENUM('MEMBER_APP','TRAINER_APP','ADMIN_WEB') NOT NULL,
    entered_by_mobile_user_id BIGINT UNSIGNED NULL,
    entered_by_web_user_id INT NULL,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,

    KEY idx_measure_member_date (member_id, measurement_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------------------------------------------------------
-- 7. Diet / meal / training plans
-- ----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS fitness_plan_templates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    trainer_staff_id INT NOT NULL,
    branch_id INT NOT NULL,
    plan_type ENUM('DIET','MEAL','TRAINING') NOT NULL,
    title VARCHAR(150) NOT NULL,
    description TEXT NULL,
    duration_type ENUM('DAILY','WEEKLY','MONTHLY','CUSTOM') NOT NULL,
    duration_days INT NULL,
    visibility ENUM('PRIVATE','GYM_TEMPLATE') NOT NULL DEFAULT 'PRIVATE',
    status ENUM('ACTIVE','ARCHIVED') NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    KEY idx_plan_template_trainer (trainer_staff_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS fitness_plan_days (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    plan_template_id BIGINT UNSIGNED NOT NULL,
    day_number INT NOT NULL,
    day_label VARCHAR(50) NULL,
    notes TEXT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    KEY idx_plan_days_template (plan_template_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS fitness_plan_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    plan_day_id BIGINT UNSIGNED NOT NULL,
    item_type ENUM('MEAL','FOOD','EXERCISE','CARDIO','REST','NOTE') NOT NULL,
    title VARCHAR(150) NOT NULL,
    description TEXT NULL,
    quantity VARCHAR(100) NULL,
    sets VARCHAR(20) NULL,
    reps VARCHAR(50) NULL,
    duration_minutes INT NULL,
    rest_seconds INT NULL,
    media_url VARCHAR(700) NULL,
    sort_order INT NOT NULL DEFAULT 0,
    KEY idx_plan_items_day (plan_day_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS fitness_plan_assignments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    plan_template_id BIGINT UNSIGNED NOT NULL,
    member_id INT NOT NULL,
    membership_id INT NULL,
    assigned_by_staff_id INT NOT NULL,
    branch_id INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NULL,
    assignment_status ENUM('SCHEDULED','ACTIVE','COMPLETED','CANCELLED') NOT NULL DEFAULT 'ACTIVE',
    trainer_notes TEXT NULL,
    member_notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    KEY idx_plan_member_status (member_id, assignment_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------------------------------------------------------
-- 8. Trainer notes, profile, certificates
-- ----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS trainer_member_notes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    member_id INT NOT NULL,
    trainer_staff_id INT NOT NULL,
    branch_id INT NOT NULL,
    note_text TEXT NOT NULL,
    note_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    visibility ENUM('TRAINER_AND_ADMIN','ADMINS_ONLY') NOT NULL DEFAULT 'TRAINER_AND_ADMIN',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    KEY idx_trainer_notes_member (member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS trainer_profiles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    staff_id INT NOT NULL,
    bio TEXT NULL,
    expertise TEXT NULL,
    achievements TEXT NULL,
    profile_visibility ENUM('PUBLIC','MEMBERS_ONLY','PRIVATE') NOT NULL DEFAULT 'MEMBERS_ONLY',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_trainer_profile_staff (staff_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS trainer_certificates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    staff_id INT NOT NULL,
    title VARCHAR(150) NOT NULL,
    issuing_organization VARCHAR(150) NULL,
    issue_date DATE NULL,
    expiry_date DATE NULL,
    credential_id VARCHAR(120) NULL,
    certificate_file_url VARCHAR(700) NULL,
    verification_status ENUM('PENDING','VERIFIED','REJECTED') NOT NULL DEFAULT 'PENDING',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    KEY idx_trainer_certs_staff (staff_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------------------------------------------------------
-- 9. Membership renewal requests
-- ----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS membership_renewal_requests (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    member_id INT NOT NULL,
    branch_id INT NOT NULL,
    requested_package_id INT NOT NULL,
    current_membership_id INT NULL,

    payment_account_id INT NULL,
    declared_amount DECIMAL(12,2) NULL,
    payment_method ENUM('BANK_TRANSFER','MOBILE_WALLET','CASH_DEPOSIT','OTHER') NOT NULL,
    transaction_reference VARCHAR(150) NULL,
    payment_date DATE NULL,
    screenshot_url VARCHAR(700) NOT NULL,
    member_note VARCHAR(500) NULL,

    request_status ENUM('PENDING','UNDER_REVIEW','APPROVED','REJECTED','CANCELLED') NOT NULL DEFAULT 'PENDING',

    reviewed_by VARCHAR(50) NULL,
    reviewed_at DATETIME NULL,
    rejection_reason VARCHAR(500) NULL,
    generated_membership_id INT NULL,
    generated_payment_id INT NULL,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,

    KEY idx_renewal_status (branch_id, request_status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------------------------------------------------------
-- 10. Notifications
-- ----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS mobile_notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    branch_id INT NOT NULL,
    title VARCHAR(180) NOT NULL,
    body VARCHAR(1000) NOT NULL,
    notification_type VARCHAR(80) NOT NULL,
    entity_type VARCHAR(80) NULL,
    entity_id BIGINT UNSIGNED NULL,
    target_type ENUM('USER','ROLE','BRANCH','TOPIC') NOT NULL,
    target_value VARCHAR(150) NOT NULL,
    data_payload JSON NULL,
    scheduled_at DATETIME NULL,
    sent_at DATETIME NULL,
    status ENUM('DRAFT','SCHEDULED','PROCESSING','SENT','PARTIAL','FAILED','CANCELLED') NOT NULL DEFAULT 'DRAFT',
    success_count INT NOT NULL DEFAULT 0,
    failure_count INT NOT NULL DEFAULT 0,
    created_by VARCHAR(50) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS mobile_notification_deliveries (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    notification_id BIGINT UNSIGNED NOT NULL,
    mobile_user_id BIGINT UNSIGNED NOT NULL,
    mobile_device_id BIGINT UNSIGNED NULL,
    delivery_status ENUM('QUEUED','SENT','DELIVERED','OPENED','FAILED') NOT NULL DEFAULT 'QUEUED',
    provider_message_id VARCHAR(200) NULL,
    error_message VARCHAR(1000) NULL,
    sent_at DATETIME NULL,
    opened_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_delivery_notification (notification_id),
    KEY idx_delivery_user (mobile_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS mobile_event_outbox (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_type VARCHAR(100) NOT NULL,
    aggregate_type VARCHAR(100) NOT NULL,
    aggregate_id BIGINT UNSIGNED NOT NULL,
    payload JSON NOT NULL,
    status ENUM('PENDING','PROCESSING','DONE','FAILED') NOT NULL DEFAULT 'PENDING',
    attempts SMALLINT NOT NULL DEFAULT 0,
    next_attempt_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    processed_at DATETIME NULL,
    error_message TEXT NULL,
    KEY idx_outbox_status (status, next_attempt_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------------------------------------------------------
-- 11. Activity logging & idempotency
-- ----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS mobile_activity_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mobile_user_id BIGINT UNSIGNED NULL,
    actor_role ENUM('MEMBER','TRAINER','ADMIN','SYSTEM') NOT NULL,
    action_code VARCHAR(120) NOT NULL,
    entity_type VARCHAR(100) NULL,
    entity_id BIGINT UNSIGNED NULL,
    branch_id INT NULL,
    request_id VARCHAR(100) NULL,
    ip_address VARCHAR(45) NULL,
    device_id VARCHAR(120) NULL,
    old_values JSON NULL,
    new_values JSON NULL,
    metadata JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_activity_entity (entity_type, entity_id),
    KEY idx_activity_user (mobile_user_id, created_at),
    KEY idx_activity_branch (branch_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS mobile_idempotency_keys (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mobile_user_id BIGINT UNSIGNED NULL,
    idempotency_key VARCHAR(100) NOT NULL,
    endpoint VARCHAR(200) NOT NULL,
    request_hash VARCHAR(255) NOT NULL,
    response_status INT NULL,
    response_body JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL,
    UNIQUE KEY uq_idempotency (idempotency_key, endpoint)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================================================
-- End of migration. Deferred to a later phase (not in this file):
--   mobile_posts.post_type PARTNER_REQUEST, mobile_partner_responses,
--   mobile_post_comments — partner finder & comments are post-MVP per spec §50.
-- ============================================================================