Server : Apache/2 System : Linux server-15-235-50-60 5.15.0-164-generic #174-Ubuntu SMP Fri Nov 14 20:25:16 UTC 2025 x86_64 User : gositeme ( 1004) PHP Version : 8.2.29 Disable Function : exec,system,passthru,shell_exec,proc_close,proc_open,dl,popen,show_source,posix_kill,posix_mkfifo,posix_getpwuid,posix_setpgid,posix_setsid,posix_setuid,posix_setgid,posix_seteuid,posix_setegid,posix_uname Directory :  /home/gositeme/domains/soundstudiopro.com/private_html/database/

Upload File : current_dir [ Writeable ] document_root [ Writeable ]

-- Grace Period Migration for SoundStudioPro
-- Adds commercial rights tracking and grace period eligibility to music_tracks
-- Run this migration to enable the 30-day upgrade grace period feature

-- ============================================================
-- 1. Add commercial_rights column to music_tracks
-- Values: 'none' (free tier), 'full' (paid tier), 'grace' (converted via grace period)
-- ============================================================
ALTER TABLE music_tracks 
ADD COLUMN IF NOT EXISTS commercial_rights ENUM('none', 'full', 'grace') DEFAULT 'none'
AFTER is_vip_sample;

-- ============================================================
-- 2. Add grace_period_eligible flag
-- TRUE if track was created within 30 days of user account creation
-- ============================================================
ALTER TABLE music_tracks 
ADD COLUMN IF NOT EXISTS grace_period_eligible BOOLEAN DEFAULT FALSE
AFTER commercial_rights;

-- ============================================================
-- 3. Add grace_period_converted_at timestamp
-- Records when a track was converted via grace period upgrade
-- ============================================================
ALTER TABLE music_tracks 
ADD COLUMN IF NOT EXISTS grace_period_converted_at TIMESTAMP NULL DEFAULT NULL
AFTER grace_period_eligible;

-- ============================================================
-- 4. Add index for efficient grace period queries
-- ============================================================
CREATE INDEX IF NOT EXISTS idx_grace_period ON music_tracks (user_id, grace_period_eligible, commercial_rights);

-- ============================================================
-- 5. Backfill existing tracks with proper commercial_rights values
-- Based on user plan at time of creation (approximation for existing data)
-- ============================================================

-- Mark tracks from currently paid users as having full rights
-- Note: This is an approximation - ideally track creation should log the plan
UPDATE music_tracks mt
JOIN users u ON mt.user_id = u.id
SET mt.commercial_rights = 'full'
WHERE u.plan != 'free' 
AND mt.commercial_rights = 'none';

-- ============================================================
-- 6. Backfill grace_period_eligible for recent tracks
-- Mark tracks created within 30 days of user account creation
-- ============================================================
UPDATE music_tracks mt
JOIN users u ON mt.user_id = u.id
SET mt.grace_period_eligible = TRUE
WHERE mt.created_at <= DATE_ADD(u.created_at, INTERVAL 30 DAY)
AND mt.commercial_rights = 'none';

-- ============================================================
-- 7. Add user_plan_at_creation column to track which plan was active
-- This helps with future disputes and auditing
-- ============================================================
ALTER TABLE music_tracks 
ADD COLUMN IF NOT EXISTS user_plan_at_creation VARCHAR(50) DEFAULT 'free'
AFTER grace_period_converted_at;

-- Backfill with current plan (approximation)
UPDATE music_tracks mt
JOIN users u ON mt.user_id = u.id
SET mt.user_plan_at_creation = u.plan
WHERE mt.user_plan_at_creation = 'free' OR mt.user_plan_at_creation IS NULL;

-- ============================================================
-- VERIFICATION QUERIES (run manually to verify migration)
-- ============================================================
-- SELECT COUNT(*) as total_tracks, commercial_rights FROM music_tracks GROUP BY commercial_rights;
-- SELECT COUNT(*) as grace_eligible FROM music_tracks WHERE grace_period_eligible = TRUE;
-- SELECT u.name, u.created_at as user_created, mt.title, mt.created_at as track_created, mt.grace_period_eligible 
--        FROM music_tracks mt JOIN users u ON mt.user_id = u.id 
--        WHERE mt.grace_period_eligible = TRUE LIMIT 10;