CREATE TABLE IF NOT EXISTS users (
    id BINARY(16) NOT NULL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    display_name VARCHAR(255) NOT NULL DEFAULT '',
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_users_email (email),
    KEY idx_users_deleted (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS refresh_tokens (
    id BINARY(16) NOT NULL PRIMARY KEY,
    user_id BINARY(16) NOT NULL,
    token_hash BINARY(32) NOT NULL,
    expires_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL,
    revoked_at DATETIME NULL,
    UNIQUE KEY uq_refresh_hash (token_hash),
    KEY idx_refresh_user (user_id),
    KEY idx_refresh_expiry (expires_at),
    CONSTRAINT fk_refresh_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 audit_log (
    id BINARY(16) NOT NULL PRIMARY KEY,
    user_id BINARY(16) NULL,
    event VARCHAR(80) NOT NULL,
    ip_address VARCHAR(64) NULL,
    metadata JSON NULL,
    created_at DATETIME NOT NULL,
    KEY idx_audit_user_time (user_id, created_at),
    CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rate_limits (
    action_key VARCHAR(120) NOT NULL,
    identity_key VARCHAR(190) NOT NULL,
    bucket_start DATETIME NOT NULL,
    hit_count INT NOT NULL DEFAULT 0,
    PRIMARY KEY (action_key, identity_key, bucket_start),
    KEY idx_rate_bucket (bucket_start)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS devices (
    id BINARY(16) NOT NULL PRIMARY KEY,
    af_id VARCHAR(255) NOT NULL,
    os_name VARCHAR(80) NULL,
    bundle_id VARCHAR(255) NULL,
    firebase_project_id VARCHAR(255) NULL,
    store_id VARCHAR(120) NULL,
    push_token TEXT NULL,
    locale VARCHAR(32) NULL,
    user_agent TEXT NULL,
    idfa VARCHAR(64) NULL,
    attribution_status VARCHAR(80) NULL,
    media_source VARCHAR(255) NULL,
    campaign VARCHAR(255) NULL,
    source_ip VARCHAR(64) NULL,
    launch_count INT NOT NULL DEFAULT 0,
    first_seen_at DATETIME NOT NULL,
    last_seen_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE KEY uq_devices_af_id (af_id),
    KEY idx_devices_updated (updated_at),
    KEY idx_devices_source_ip (source_ip)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS device_attributions (
    id BINARY(16) NOT NULL PRIMARY KEY,
    device_id BINARY(16) NOT NULL,
    payload_hash BINARY(32) NOT NULL,
    payload JSON NOT NULL,
    created_at DATETIME NOT NULL,
    UNIQUE KEY uq_attr_device_hash (device_id, payload_hash),
    KEY idx_attr_device_time (device_id, created_at),
    CONSTRAINT fk_attr_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS device_forward_logs (
    id BINARY(16) NOT NULL PRIMARY KEY,
    device_id BINARY(16) NOT NULL,
    endpoint_url TEXT NOT NULL,
    request_payload JSON NOT NULL,
    response_status INT NOT NULL DEFAULT 0,
    response_payload MEDIUMTEXT NULL,
    error_text TEXT NULL,
    created_at DATETIME NOT NULL,
    KEY idx_forward_device_time (device_id, created_at),
    CONSTRAINT fk_forward_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_devices (
    user_id BINARY(16) NOT NULL,
    device_id BINARY(16) NOT NULL,
    linked_at DATETIME NOT NULL,
    PRIMARY KEY (user_id, device_id),
    CONSTRAINT fk_user_devices_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_devices_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wallets (
    id BINARY(16) NOT NULL,
    user_id BINARY(16) NOT NULL,
    payload JSON NOT NULL,
    version INT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    deleted_at DATETIME NULL,
    PRIMARY KEY (user_id, id),
    KEY idx_wallets_updated (user_id, updated_at),
    CONSTRAINT fk_wallets_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 cash_transactions (
    id BINARY(16) NOT NULL,
    user_id BINARY(16) NOT NULL,
    payload JSON NOT NULL,
    version INT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    deleted_at DATETIME NULL,
    PRIMARY KEY (user_id, id),
    KEY idx_transactions_updated (user_id, updated_at),
    CONSTRAINT fk_transactions_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 envelopes (
    id BINARY(16) NOT NULL,
    user_id BINARY(16) NOT NULL,
    payload JSON NOT NULL,
    version INT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    deleted_at DATETIME NULL,
    PRIMARY KEY (user_id, id),
    KEY idx_envelopes_updated (user_id, updated_at),
    CONSTRAINT fk_envelopes_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 count_sessions (
    id BINARY(16) NOT NULL,
    user_id BINARY(16) NOT NULL,
    payload JSON NOT NULL,
    version INT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    deleted_at DATETIME NULL,
    PRIMARY KEY (user_id, id),
    KEY idx_count_sessions_updated (user_id, updated_at),
    CONSTRAINT fk_count_sessions_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 reminders (
    id BINARY(16) NOT NULL,
    user_id BINARY(16) NOT NULL,
    payload JSON NOT NULL,
    version INT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    deleted_at DATETIME NULL,
    PRIMARY KEY (user_id, id),
    KEY idx_reminders_updated (user_id, updated_at),
    CONSTRAINT fk_reminders_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 allocation_events (
    id BINARY(16) NOT NULL,
    user_id BINARY(16) NOT NULL,
    payload JSON NOT NULL,
    version INT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    deleted_at DATETIME NULL,
    PRIMARY KEY (user_id, id),
    KEY idx_allocations_updated (user_id, updated_at),
    CONSTRAINT fk_allocations_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 categories (
    id BINARY(16) NOT NULL,
    user_id BINARY(16) NOT NULL,
    payload JSON NOT NULL,
    version INT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    deleted_at DATETIME NULL,
    PRIMARY KEY (user_id, id),
    KEY idx_categories_updated (user_id, updated_at),
    CONSTRAINT fk_categories_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 app_state (
    id BINARY(16) NOT NULL,
    user_id BINARY(16) NOT NULL,
    payload JSON NOT NULL,
    version INT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    deleted_at DATETIME NULL,
    PRIMARY KEY (user_id, id),
    KEY idx_app_state_updated (user_id, updated_at),
    CONSTRAINT fk_app_state_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 drafts (
    id BINARY(16) NOT NULL,
    user_id BINARY(16) NOT NULL,
    payload JSON NOT NULL,
    version INT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    deleted_at DATETIME NULL,
    PRIMARY KEY (user_id, id),
    KEY idx_drafts_updated (user_id, updated_at),
    CONSTRAINT fk_drafts_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 attachments (
    id BINARY(16) NOT NULL,
    user_id BINARY(16) NOT NULL,
    payload JSON NOT NULL,
    version INT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    deleted_at DATETIME NULL,
    PRIMARY KEY (user_id, id),
    KEY idx_attachments_updated (user_id, updated_at),
    CONSTRAINT fk_attachments_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;