-- Add zone-specific flip tracking columns to crypto_market_social_coins
-- Tracks first entry into each rank zone (10, 50, 100, 150, 200) from >200
-- Enables filtering coins by specific zone entry times and ranks

ALTER TABLE crypto_market_social_coins 
ADD COLUMN altrank_flip_time_10 DATETIME DEFAULT NULL COMMENT 'First time crossed from >200 to ≤10' AFTER altrank_flip_rank,
ADD COLUMN altrank_flip_rank_10 INT(11) DEFAULT NULL COMMENT 'Rank when first crossed into top 10 zone' AFTER altrank_flip_time_10,
ADD COLUMN altrank_flip_time_50 DATETIME DEFAULT NULL COMMENT 'First time crossed from >200 to ≤50' AFTER altrank_flip_rank_10,
ADD COLUMN altrank_flip_rank_50 INT(11) DEFAULT NULL COMMENT 'Rank when first crossed into top 50 zone' AFTER altrank_flip_time_50,
ADD COLUMN altrank_flip_time_100 DATETIME DEFAULT NULL COMMENT 'First time crossed from >200 to ≤100' AFTER altrank_flip_rank_50,
ADD COLUMN altrank_flip_rank_100 INT(11) DEFAULT NULL COMMENT 'Rank when first crossed into top 100 zone' AFTER altrank_flip_time_100,
ADD COLUMN altrank_flip_time_150 DATETIME DEFAULT NULL COMMENT 'First time crossed from >200 to ≤150' AFTER altrank_flip_rank_100,
ADD COLUMN altrank_flip_rank_150 INT(11) DEFAULT NULL COMMENT 'Rank when first crossed into top 150 zone' AFTER altrank_flip_time_150,
ADD COLUMN altrank_flip_time_200 DATETIME DEFAULT NULL COMMENT 'First time crossed from >200 to ≤200' AFTER altrank_flip_rank_150,
ADD COLUMN altrank_flip_rank_200 INT(11) DEFAULT NULL COMMENT 'Rank when first crossed into top 200 zone' AFTER altrank_flip_time_200;

-- Add indexes for efficient filtering by zone entry times
CREATE INDEX idx_altrank_flip_time_10 ON crypto_market_social_coins(altrank_flip_time_10);
CREATE INDEX idx_altrank_flip_time_50 ON crypto_market_social_coins(altrank_flip_time_50);
CREATE INDEX idx_altrank_flip_time_100 ON crypto_market_social_coins(altrank_flip_time_100);
CREATE INDEX idx_altrank_flip_time_150 ON crypto_market_social_coins(altrank_flip_time_150);
CREATE INDEX idx_altrank_flip_time_200 ON crypto_market_social_coins(altrank_flip_time_200);

-- Show updated table structure
DESCRIBE crypto_market_social_coins;
