-- Migration: create_subscription_logs.sql
-- Creates a table to log subscription changes per branch
CREATE TABLE IF NOT EXISTS subscription_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    branch_id BIGINT UNSIGNED NOT NULL,
    action VARCHAR(50) NOT NULL COMMENT 'subscription_change, downgrade, upgrade, etc.',
    previous_expiry DATETIME NULL COMMENT 'Previous subscription_ends_at value',
    new_expiry DATETIME NULL COMMENT 'New subscription_ends_at value',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_subscription_logs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_subscription_logs_branch FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;