-- Graduate Refinary - Phase 7: Payment Integration
-- Run this script on your MySQL server to add the transactions table.
-- 
-- Note: If the `credits` column doesn't exist on `users`, run the ALTER TABLE
-- statements at the bottom.

-- ─── Transactions Table ───────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `transactions` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id`           INT UNSIGNED NOT NULL,
    `campay_reference`  VARCHAR(100)  DEFAULT NULL,
    `amount`            INT UNSIGNED NOT NULL COMMENT 'Amount in XAF',
    `currency`          VARCHAR(10)   NOT NULL DEFAULT 'XAF',
    `type`              ENUM('pay_per_use', 'subscription_monthly', 'subscription_yearly') NOT NULL,
    `status`            ENUM('pending', 'successful', 'failed') NOT NULL DEFAULT 'pending',
    `created_at`        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_user`      (`user_id`),
    INDEX `idx_reference` (`campay_reference`),
    INDEX `idx_status`    (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── Add credits column to users (if not already present) ───────────────────
ALTER TABLE `users`
    ADD COLUMN IF NOT EXISTS `credits`              INT UNSIGNED NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS `subscription_credits` INT UNSIGNED NOT NULL DEFAULT 0;

-- ─── Verify ───────────────────────────────────────────────────────────────────
SELECT 'Migration complete.' AS result;
