-- LuckySt ls13 Migration SQL
-- Run inside the postgres container:
--   docker exec -i postgres psql -U <user> -d <db> < migrations_ls13.sql

-- 1. Add new columns to users table
ALTER TABLE users ADD COLUMN IF NOT EXISTS subscription_tier VARCHAR(20);
ALTER TABLE users ADD COLUMN IF NOT EXISTS stripe_customer_id VARCHAR(255);
ALTER TABLE users ADD COLUMN IF NOT EXISTS stripe_subscription_id VARCHAR(255);
ALTER TABLE users ADD COLUMN IF NOT EXISTS referral_code VARCHAR(50);
ALTER TABLE users ADD COLUMN IF NOT EXISTS referred_by VARCHAR(50);
ALTER TABLE users ADD COLUMN IF NOT EXISTS session_ttl_hours INTEGER NOT NULL DEFAULT 72;

-- 2. Index on referral_code for lookups
CREATE INDEX IF NOT EXISTS ix_users_referral_code ON users (referral_code);

-- 3. Add created_by_username to bot_presets (from earlier in this branch)
ALTER TABLE bot_presets ADD COLUMN IF NOT EXISTS created_by_username VARCHAR(100) NOT NULL DEFAULT 'unknown';

-- 4. Set npastrami as superuser (admin)
UPDATE users SET is_superuser = true WHERE username = 'npastrami';

-- 5. Assign referral codes to existing users
UPDATE users SET referral_code = 'doji' WHERE username = 'dojionbase';
UPDATE users SET referral_code = 'jake' WHERE username = 'jakeonbase';

-- 6. Create new user accounts (if they don't exist)

INSERT INTO users (username, email, hashed_password, is_active, is_superuser, failed_login_attempts, referral_code, session_ttl_hours)
SELECT 'august', 'august@luckyst.trade', '$2b$12$I0z4MS9rwYZl.8nyJGkhUu5Ii/CGHz52jeHy6tHai.iw5T3YJM4xC', true, false, 0, 'august', 72
WHERE NOT EXISTS (SELECT 1 FROM users WHERE username = 'august');

INSERT INTO users (username, email, hashed_password, is_active, is_superuser, failed_login_attempts, referral_code, session_ttl_hours)
SELECT 'eric', 'eric@luckyst.trade', '$2b$12$XK1KN6ovwPXh4N2s1xXefOO7lDQGetyt8uI07ARYYAvij/39Z79Ay', true, false, 0, 'swarm', 72
WHERE NOT EXISTS (SELECT 1 FROM users WHERE username = 'eric');

INSERT INTO users (username, email, hashed_password, is_active, is_superuser, failed_login_attempts, referral_code, session_ttl_hours)
SELECT 'harry', 'harry@luckyst.trade', '$2b$12$2IWHFEVNrC2K5i0RixWmz.yy5CUujmPOp3k/NdMm0Jjl0SEJLjLpe', true, false, 0, 'harry', 72
WHERE NOT EXISTS (SELECT 1 FROM users WHERE username = 'harry');

INSERT INTO users (username, email, hashed_password, is_active, is_superuser, failed_login_attempts, referral_code, session_ttl_hours)
SELECT 'woz', 'woz@luckyst.trade', '$2b$12$7r1MlobQfJcUXuruvNg5KeuhCUj.tecWJuX4eQotELlWRsyEEwTfe', true, false, 0, 'turbine', 72
WHERE NOT EXISTS (SELECT 1 FROM users WHERE username = 'woz');

-- 7. Grant founding members full access (API tier)
UPDATE users SET subscription_tier = 'api' WHERE username IN ('npastrami', 'dojionbase', 'jakeonbase', 'august', 'eric', 'harry', 'woz');

-- 8. Verify all accounts
SELECT username, is_superuser, subscription_tier, referral_code, session_ttl_hours FROM users ORDER BY id;
