-- ============================================================================
-- Migration v11: Custom Mess ID / Member ID formats, configurable free trial,
-- and reversible subscription-payment approve/reject.
-- Run this ONCE on your existing database (phpMyAdmin > SQL tab).
-- Safe to run more than once.
-- ============================================================================

ALTER TABLE mess_accounts
    ADD COLUMN IF NOT EXISTS mess_code VARCHAR(12) DEFAULT NULL AFTER referral_code;

ALTER TABLE members
    ADD COLUMN IF NOT EXISTS member_code VARCHAR(14) DEFAULT NULL AFTER mess_id;

-- Backfill mess_code for existing accounts that don't have one yet, using each
-- account's own creation month (YYMM) + a running sequence number based on
-- row order (id ASC), so older accounts get earlier numbers.
SET @seq = 0;
UPDATE mess_accounts
SET mess_code = CONCAT(
    DATE_FORMAT(created_at, '%y%m'),
    LPAD((@seq := @seq + 1), 6, '0')
)
WHERE mess_code IS NULL
ORDER BY id ASC;

-- Backfill member_code the same way, using the last digit of each member's
-- own mess's mess_code.
SET @mseq = 0;
UPDATE members m
JOIN mess_accounts ma ON m.mess_id = ma.id
SET m.member_code = CONCAT(
    DATE_FORMAT(m.created_at, '%y%m'),
    RIGHT(ma.mess_code, 1),
    LPAD((@mseq := @mseq + 1), 5, '0')
)
WHERE m.member_code IS NULL
ORDER BY m.id ASC;

-- Add uniqueness constraints now that every row has a value. If you run this
-- migration a second time, MariaDB will show one harmless "Duplicate key
-- name" error for these two lines specifically — that just means they're
-- already there; every other statement in this file still runs normally.
ALTER TABLE mess_accounts ADD UNIQUE KEY uniq_mess_code (mess_code);
ALTER TABLE members ADD UNIQUE KEY uniq_member_code (member_code);

-- Subscription settings: configurable free trial length
ALTER TABLE subscription_settings
    ADD COLUMN IF NOT EXISTS free_trial_months INT NOT NULL DEFAULT 0 AFTER enforcement_start_date;

-- Allow a subscription grant to originate from a free trial too
ALTER TABLE mess_subscriptions MODIFY COLUMN source ENUM('payment','admin_grant','free_trial') NOT NULL DEFAULT 'payment';

-- Track which specific subscription period a payment activated, so an
-- approved payment can be cleanly reversed if later switched to rejected
ALTER TABLE subscription_payments
    ADD COLUMN IF NOT EXISTS activated_subscription_id INT DEFAULT NULL AFTER reviewed_by;