-- ============================================================
-- GASFLOW v2.1 — Referral & Agent Reward System Migration
-- Adds: referral codes, agent role, referral rewards (10% on renewal),
--        agent dashboard data, withdrawal tracking
-- ============================================================


-- ── Add referral_code and referred_by to users ──────────────
ALTER TABLE `users`
    ADD COLUMN `referral_code`   VARCHAR(20)  DEFAULT NULL AFTER `email_verified_at`,
    ADD COLUMN `referred_by`     INT UNSIGNED DEFAULT NULL AFTER `referral_code`,
    ADD INDEX `idx_users_referral` (`referral_code`),
    ADD INDEX `idx_users_referred_by` (`referred_by`);

-- ── Add referred_by to shops (which agent referred this shop) ─
ALTER TABLE `shops`
    ADD COLUMN `referred_by_user_id` INT UNSIGNED DEFAULT NULL AFTER `settings`,
    ADD INDEX `idx_shops_referred_by` (`referred_by_user_id`);

-- ── Update user role ENUM to include 'agent' ────────────────
ALTER TABLE `users`
    MODIFY COLUMN `role` ENUM('super_admin','shop_admin','worker','agent') NOT NULL DEFAULT 'worker';

-- ── REFERRAL REWARDS ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `referral_rewards` (
    `id`              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `agent_user_id`   INT UNSIGNED    NOT NULL COMMENT 'The agent/referrer who earns the reward',
    `shop_id`         INT UNSIGNED    NOT NULL COMMENT 'The shop whose renewal triggered the reward',
    `payment_log_id`  BIGINT UNSIGNED DEFAULT NULL COMMENT 'The payment that triggered this reward',
    `subscription_amount` DECIMAL(10,2) NOT NULL COMMENT 'Original subscription amount paid',
    `reward_percentage`   DECIMAL(5,2)  NOT NULL DEFAULT 10.00,
    `reward_amount`       DECIMAL(10,2) NOT NULL COMMENT 'Calculated reward (10% of subscription)',
    `status`          ENUM('pending','approved','paid','cancelled') NOT NULL DEFAULT 'pending',
    `approved_by`     INT UNSIGNED    DEFAULT NULL,
    `approved_at`     DATETIME        DEFAULT NULL,
    `paid_at`         DATETIME        DEFAULT NULL,
    `notes`           TEXT            DEFAULT NULL,
    `created_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_reward_agent` (`agent_user_id`, `status`),
    INDEX `idx_reward_shop` (`shop_id`),
    INDEX `idx_reward_status` (`status`, `created_at`),
    INDEX `idx_reward_payment` (`payment_log_id`),
    CONSTRAINT `fk_reward_agent` FOREIGN KEY (`agent_user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_reward_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_reward_payment` FOREIGN KEY (`payment_log_id`) REFERENCES `payment_logs`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── AGENT WITHDRAWALS ───────────────────────────────────────
CREATE TABLE IF NOT EXISTS `agent_withdrawals` (
    `id`              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `agent_user_id`   INT UNSIGNED    NOT NULL,
    `amount`          DECIMAL(10,2)   NOT NULL,
    `bank_name`       VARCHAR(100)    DEFAULT NULL,
    `account_number`  VARCHAR(20)     DEFAULT NULL,
    `account_name`    VARCHAR(150)    DEFAULT NULL,
    `status`          ENUM('pending','approved','paid','rejected') NOT NULL DEFAULT 'pending',
    `processed_by`    INT UNSIGNED    DEFAULT NULL,
    `processed_at`    DATETIME        DEFAULT NULL,
    `reference`       VARCHAR(100)    DEFAULT NULL,
    `notes`           TEXT            DEFAULT NULL,
    `created_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_withdrawal_agent` (`agent_user_id`, `status`),
    INDEX `idx_withdrawal_status` (`status`, `created_at`),
    CONSTRAINT `fk_withdrawal_agent` FOREIGN KEY (`agent_user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Add referral reward percentage to platform settings ─────
INSERT INTO `platform_settings` (`key`, `value`, `label`, `group`) VALUES
('referral_reward_percentage', '10', 'Referral Reward (%)', 'referral'),
('min_withdrawal_amount', '1000', 'Min Withdrawal Amount (₦)', 'referral'),
('referral_enabled', '1', 'Enable Referral System', 'referral');

-- ── Generate referral codes for existing users (admins & agents) ─
UPDATE `users` SET `referral_code` = UPPER(CONCAT(
    SUBSTRING(MD5(CONCAT(id, uuid, RAND())), 1, 3),
    id,
    SUBSTRING(MD5(CONCAT(email, NOW())), 1, 3)
)) WHERE `role` IN ('shop_admin', 'super_admin') AND `referral_code` IS NULL;

