SET @email_verified_exists = (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'lead_contacts'
      AND COLUMN_NAME = 'email_verified'
);
SET @email_verified_sql = IF(
    @email_verified_exists = 0,
    'ALTER TABLE lead_contacts ADD COLUMN email_verified TINYINT(1) NOT NULL DEFAULT 0',
    'SELECT 1'
);
PREPARE hive_stmt FROM @email_verified_sql;
EXECUTE hive_stmt;
DEALLOCATE PREPARE hive_stmt;

SET @email_verified_at_exists = (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'lead_contacts'
      AND COLUMN_NAME = 'email_verified_at'
);
SET @email_verified_at_sql = IF(
    @email_verified_at_exists = 0,
    'ALTER TABLE lead_contacts ADD COLUMN email_verified_at DATETIME DEFAULT NULL',
    'SELECT 1'
);
PREPARE hive_stmt FROM @email_verified_at_sql;
EXECUTE hive_stmt;
DEALLOCATE PREPARE hive_stmt;

CREATE TABLE IF NOT EXISTS hive_points_balances (
    lead_contact_id BIGINT UNSIGNED NOT NULL,
    current_points INT NOT NULL DEFAULT 0,
    lifetime_earned INT NOT NULL DEFAULT 0,
    lifetime_redeemed INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_event_at DATETIME DEFAULT NULL,
    PRIMARY KEY (lead_contact_id),
    CONSTRAINT fk_hive_points_balances_contact_id
        FOREIGN KEY (lead_contact_id) REFERENCES lead_contacts(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hive_points_ledger (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    lead_contact_id BIGINT UNSIGNED NOT NULL,
    event_type VARCHAR(64) NOT NULL,
    event_key VARCHAR(191) NOT NULL,
    points_delta INT NOT NULL,
    event_meta JSON DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_hive_points_event_key (event_key),
    KEY idx_hive_points_ledger_contact_id (lead_contact_id),
    KEY idx_hive_points_ledger_event_type (event_type),
    CONSTRAINT fk_hive_points_ledger_contact_id
        FOREIGN KEY (lead_contact_id) REFERENCES lead_contacts(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lead_email_verifications (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    lead_contact_id BIGINT UNSIGNED NOT NULL,
    email VARCHAR(255) NOT NULL,
    verification_token CHAR(64) NOT NULL,
    expires_at DATETIME NOT NULL,
    verified_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_lead_email_verifications_token (verification_token),
    KEY idx_lead_email_verifications_contact_id (lead_contact_id),
    CONSTRAINT fk_lead_email_verifications_contact_id
        FOREIGN KEY (lead_contact_id) REFERENCES lead_contacts(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hive_member_access_tokens (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    lead_contact_id BIGINT UNSIGNED NOT NULL,
    token_hash CHAR(64) NOT NULL,
    expires_at DATETIME NOT NULL,
    last_used_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_hive_member_access_token_hash (token_hash),
    KEY idx_hive_member_access_tokens_contact_id (lead_contact_id),
    KEY idx_hive_member_access_tokens_expires_at (expires_at),
    CONSTRAINT fk_hive_member_access_tokens_contact_id
        FOREIGN KEY (lead_contact_id) REFERENCES lead_contacts(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hive_rewards_catalog (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    reward_key VARCHAR(100) NOT NULL,
    title VARCHAR(190) NOT NULL,
    description TEXT DEFAULT NULL,
    reward_type VARCHAR(64) NOT NULL DEFAULT 'perk',
    points_cost INT NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_hive_rewards_catalog_key (reward_key),
    KEY idx_hive_rewards_catalog_active_cost (is_active, points_cost, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO hive_rewards_catalog (reward_key, title, description, reward_type, points_cost, is_active, sort_order)
VALUES
    ('weekly_drop_entry', 'Weekly Hive Drop Entry', 'Unlock one bonus entry into the weekly Hive Drop.', 'bonus_entry', 50, 1, 10),
    ('premium_money_guide', 'Premium Money Moves Guide', 'Get the premium market, savings, and side-income guide.', 'digital_bonus', 100, 1, 20),
    ('vip_offer_priority', 'VIP Offer Priority Access', 'Move into the higher-intent queue for priority partner drops.', 'priority_access', 250, 1, 30),
    ('concierge_match_pack', 'Concierge Match Pack', 'Unlock a curated bundle of premium offer matches.', 'concierge', 500, 1, 40)
ON DUPLICATE KEY UPDATE
    title = VALUES(title),
    description = VALUES(description),
    reward_type = VALUES(reward_type),
    points_cost = VALUES(points_cost),
    sort_order = VALUES(sort_order);

CREATE TABLE IF NOT EXISTS hive_reward_redemptions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    lead_contact_id BIGINT UNSIGNED NOT NULL,
    reward_catalog_id BIGINT UNSIGNED NOT NULL,
    reward_key VARCHAR(100) NOT NULL,
    points_cost INT NOT NULL,
    fulfillment_status VARCHAR(32) NOT NULL DEFAULT 'pending',
    reward_meta JSON DEFAULT NULL,
    redeemed_at DATETIME NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_hive_reward_redemption_member_reward (lead_contact_id, reward_catalog_id),
    KEY idx_hive_reward_redemptions_status (fulfillment_status),
    KEY idx_hive_reward_redemptions_redeemed_at (redeemed_at),
    CONSTRAINT fk_hive_reward_redemptions_contact_id
        FOREIGN KEY (lead_contact_id) REFERENCES lead_contacts(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_hive_reward_redemptions_reward_id
        FOREIGN KEY (reward_catalog_id) REFERENCES hive_rewards_catalog(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hive_lead_routing_log (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    lead_contact_id BIGINT UNSIGNED NOT NULL,
    action_type VARCHAR(32) NOT NULL,
    status_from VARCHAR(32) NOT NULL,
    status_to VARCHAR(32) NOT NULL,
    destination_key VARCHAR(128) DEFAULT NULL,
    segment_key VARCHAR(190) DEFAULT NULL,
    export_batch_key CHAR(36) DEFAULT NULL,
    admin_username VARCHAR(120) DEFAULT NULL,
    notes TEXT DEFAULT NULL,
    action_meta JSON DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_hive_lead_routing_log_contact_id (lead_contact_id),
    KEY idx_hive_lead_routing_log_action_type (action_type),
    KEY idx_hive_lead_routing_log_status_to (status_to),
    KEY idx_hive_lead_routing_log_batch_key (export_batch_key),
    CONSTRAINT fk_hive_lead_routing_log_contact_id
        FOREIGN KEY (lead_contact_id) REFERENCES lead_contacts(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
