-- Create subscription_payments table
-- Run this migration to add payment tracking for subscriptions

CREATE TABLE IF NOT EXISTS `subscription_payments` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `plan_id` BIGINT NOT NULL,
    `subscription_id` BIGINT,
    `amount` DECIMAL(12,2) NOT NULL,
    `payment_method` ENUM('cash', 'momo') DEFAULT 'cash',
    `momo_number` VARCHAR(20),
    `reference_number` VARCHAR(255),
    `payment_date` DATE NOT NULL,
    `status` ENUM('pending', 'confirmed', 'rejected') DEFAULT 'pending',
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_sub_payments_tenant` (`tenant_id`),
    INDEX `idx_sub_payments_date` (`payment_date`),
    INDEX `idx_sub_payments_status` (`status`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`plan_id`) REFERENCES `subscription_plans`(`id`),
    FOREIGN KEY (`subscription_id`) REFERENCES `tenant_subscriptions`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;