-- VBOOST Elojob Platform - Database Schema
-- Run: mysql -u root -p < database/schema.sql

CREATE DATABASE IF NOT EXISTS vboost CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE vboost;

-- ============================================================
-- ORDERS
-- ============================================================
CREATE TABLE IF NOT EXISTS orders (
  id VARCHAR(36) PRIMARY KEY,
  client_id INT NOT NULL,
  booster_id INT DEFAULT NULL,
  service_type ENUM('elojob', 'duoboost', 'coaching', 'md5', 'md10') NOT NULL,
  current_rank VARCHAR(50) DEFAULT NULL,
  desired_rank VARCHAR(50) DEFAULT NULL,
  extras JSON DEFAULT NULL,
  chosen_booster VARCHAR(100) DEFAULT NULL,
  hours INT DEFAULT NULL,
  wins INT DEFAULT NULL,
  price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
  status ENUM('pending_payment', 'paid', 'available', 'in_progress', 'completed', 'cancelled') NOT NULL DEFAULT 'pending_payment',
  mp_preference_id VARCHAR(255) DEFAULT NULL,
  mp_payment_id VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (booster_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_orders_client (client_id),
  INDEX idx_orders_booster (booster_id),
  INDEX idx_orders_status (status),
  INDEX idx_orders_created (created_at)
) ENGINE=InnoDB;

-- ============================================================
-- CHAT MESSAGES
-- ============================================================
CREATE TABLE IF NOT EXISTS chat_messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id VARCHAR(36) NOT NULL,
  sender_id INT NOT NULL,
  message TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,
  is_read TINYINT(1) NOT NULL DEFAULT 0,
  INDEX idx_chat_order (order_id),
  INDEX idx_chat_created (created_at)
) ENGINE=InnoDB;

-- ============================================================
-- PRICE TABLE (per-tier pricing for elojob/duoboost)
-- ============================================================
CREATE TABLE IF NOT EXISTS price_table (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tier INT NOT NULL UNIQUE,
  rank_name VARCHAR(50) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  INDEX idx_price_tier (tier)
) ENGINE=InnoDB;

-- ============================================================
-- EXTRAS CONFIG
-- ============================================================
CREATE TABLE IF NOT EXISTS extras_config (
  id INT AUTO_INCREMENT PRIMARY KEY,
  extra_key VARCHAR(50) NOT NULL UNIQUE,
  label VARCHAR(100) NOT NULL,
  pct DECIMAL(5, 2) NOT NULL DEFAULT 0.00,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  INDEX idx_extras_key (extra_key)
) ENGINE=InnoDB;

-- ============================================================
-- COACHING PRICES
-- ============================================================
CREATE TABLE IF NOT EXISTS coaching_prices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  hours INT NOT NULL UNIQUE,
  price DECIMAL(10, 2) NOT NULL
) ENGINE=InnoDB;

-- ============================================================
-- SITE CONFIG (key-value store for admin)
-- ============================================================
CREATE TABLE IF NOT EXISTS site_config (
  id INT AUTO_INCREMENT PRIMARY KEY,
  config_key VARCHAR(100) NOT NULL UNIQUE,
  config_value TEXT NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_config_key (config_key)
) ENGINE=InnoDB;

-- Price table (per tier, matching pricing.js PPD)
INSERT INTO price_table (tier, rank_name, price) VALUES
(1, 'Ferro 1', 5.00),
(2, 'Ferro 2', 5.00),
(3, 'Ferro 3', 5.00),
(4, 'Bronze 1', 8.00),
(5, 'Bronze 2', 8.00),
(6, 'Bronze 3', 8.00),
(7, 'Prata 1', 12.00),
(8, 'Prata 2', 12.00),
(9, 'Prata 3', 12.00),
(10, 'Ouro 1', 20.00),
(11, 'Ouro 2', 20.00),
(12, 'Ouro 3', 20.00),
(13, 'Platina 1', 35.00),
(14, 'Platina 2', 35.00),
(15, 'Platina 3', 35.00),
(16, 'Diamante 1', 55.00),
(17, 'Diamante 2', 55.00),
(18, 'Diamante 3', 55.00),
(19, 'Ascendente 1', 85.00),
(20, 'Ascendente 2', 85.00),
(21, 'Ascendente 3', 85.00),
(22, 'Imortal 1', 130.00),
(23, 'Imortal 2', 130.00),
(24, 'Imortal 3', 130.00),
(25, 'Radiante', 200.00);

-- Extras config
INSERT INTO extras_config (extra_key, label, pct, is_active) VALUES
('nickname', 'Nickname Oculto', 0.00, 1),
('agents', 'Agentes Específicos', 0.10, 1),
('stream', 'Stream dos Jogos', 0.25, 1),
('express', 'Entrega Expressa', 0.25, 1),
('tiktok', 'Cortes TikTok/Reels', 0.30, 1),
('extrawin', '+1 Vitória Extra', 0.25, 1),
('booster', 'Escolher Booster', 0.10, 1);

-- Coaching prices
INSERT INTO coaching_prices (hours, price) VALUES
(1, 80.00),
(2, 150.00),
(3, 210.00),
(5, 325.00),
(10, 600.00);

-- Site config
INSERT INTO site_config (config_key, config_value) VALUES
('whatsapp_number', '5500000000000'),
('discord_url', 'https://discord.gg/'),
('instagram_url', 'https://instagram.com/'),
('promo_text', 'Suba de ELO com início imediato'),
('promo_discount', '15% OFF'),
('promo_services', 'em ELO e DUO BOOST'),
('booster_commission', '0.60'),
('site_name', 'VBOOST'),
('commission_elojob', '0.60'),
('commission_duoboost', '0.60'),
('commission_coaching', '0.95'),
('commission_md5', '0.60'),
('commission_md10', '0.60'),
('boost_price', '25.00'),
('boost_days', '7');

-- Sample orders
INSERT INTO orders (id, client_id, booster_id, service_type, current_rank, desired_rank, extras, price, status, created_at) VALUES
('ord-001-demo', 4, 2, 'elojob', 'Prata 3', 'Ouro 3', '["nickname","agents"]', 72.60, 'completed', '2026-02-10 14:30:00'),
('ord-002-demo', 4, 3, 'duoboost', 'Ouro 1', 'Platina 1', '["nickname","stream"]', 196.88, 'completed', '2026-02-15 10:00:00'),
('ord-003-demo', 5, 2, 'coaching', NULL, NULL, '[]', 150.00, 'completed', '2026-02-18 16:45:00'),
('ord-004-demo', 5, NULL, 'elojob', 'Bronze 1', 'Prata 3', '["nickname"]', 60.00, 'available', '2026-02-22 09:15:00'),
('ord-005-demo', 4, 2, 'md5', 'Diamante 1', NULL, '[]', 132.00, 'in_progress', '2026-02-23 11:30:00');

-- ============================================================
-- VALORANT AGENTS (unique per booster)
-- ============================================================
CREATE TABLE IF NOT EXISTS valorant_agents (
  id VARCHAR(36) PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  icon_url VARCHAR(500) NOT NULL,
  is_available TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB;

-- Seed agents
INSERT INTO valorant_agents (id, name, icon_url) VALUES
('add6443a-41bd-e414-f6ad-e58d267f4e95', 'Jett', 'https://media.valorant-api.com/agents/add6443a-41bd-e414-f6ad-e58d267f4e95/displayicon.png'),
('a3bfb853-43b2-7238-a4f1-ad90e9e46bcc', 'Reyna', 'https://media.valorant-api.com/agents/a3bfb853-43b2-7238-a4f1-ad90e9e46bcc/displayicon.png'),
('8e253930-4c05-31dd-1b6c-968525494517', 'Omen', 'https://media.valorant-api.com/agents/8e253930-4c05-31dd-1b6c-968525494517/displayicon.png'),
('569fdd95-4d10-43ab-ca70-79becc718b46', 'Sage', 'https://media.valorant-api.com/agents/569fdd95-4d10-43ab-ca70-79becc718b46/displayicon.png'),
('eb93336a-449b-9c1b-0a54-a891f7921d69', 'Phoenix', 'https://media.valorant-api.com/agents/eb93336a-449b-9c1b-0a54-a891f7921d69/displayicon.png'),
('f94c3b30-42be-e959-889c-5aa313dba261', 'Raze', 'https://media.valorant-api.com/agents/f94c3b30-42be-e959-889c-5aa313dba261/displayicon.png'),
('1dbf2edd-4729-0984-3115-daa5eed44993', 'Clove', 'https://media.valorant-api.com/agents/1dbf2edd-4729-0984-3115-daa5eed44993/displayicon.png'),
('7f94d92c-4234-0a36-9646-3a87eb8b5c89', 'Yoru', 'https://media.valorant-api.com/agents/7f94d92c-4234-0a36-9646-3a87eb8b5c89/displayicon.png'),
('6f2a04ca-43e0-be17-7f36-b3908627744d', 'Skye', 'https://media.valorant-api.com/agents/6f2a04ca-43e0-be17-7f36-b3908627744d/displayicon.png'),
('117ed9e3-49f3-6512-3ccf-0cada7e3823b', 'Cypher', 'https://media.valorant-api.com/agents/117ed9e3-49f3-6512-3ccf-0cada7e3823b/displayicon.png'),
('320b2a48-4d9b-a075-30f1-1f93a9b638fa', 'Sova', 'https://media.valorant-api.com/agents/320b2a48-4d9b-a075-30f1-1f93a9b638fa/displayicon.png'),
('601dbbe7-43ce-be57-2a40-4abd24953621', 'KAY/O', 'https://media.valorant-api.com/agents/601dbbe7-43ce-be57-2a40-4abd24953621/displayicon.png'),
('41fb69c1-4189-7b37-f117-bcaf1e96f1bf', 'Astra', 'https://media.valorant-api.com/agents/41fb69c1-4189-7b37-f117-bcaf1e96f1bf/displayicon.png'),
('bb2a4828-46eb-8cd1-e765-15848195d751', 'Neon', 'https://media.valorant-api.com/agents/bb2a4828-46eb-8cd1-e765-15848195d751/displayicon.png'),
('dade69b4-4f5a-8528-247b-219e5a1facd6', 'Fade', 'https://media.valorant-api.com/agents/dade69b4-4f5a-8528-247b-219e5a1facd6/displayicon.png'),
('e370fa57-4757-3604-3648-499e1f642d3f', 'Gekko', 'https://media.valorant-api.com/agents/e370fa57-4757-3604-3648-499e1f642d3f/displayicon.png'),
('cc8b64c8-4b25-4ff9-6e7f-37b4da43d235', 'Deadlock', 'https://media.valorant-api.com/agents/cc8b64c8-4b25-4ff9-6e7f-37b4da43d235/displayicon.png'),
('0e38b510-41a8-5780-5e8f-568b2a4f2d6c', 'Iso', 'https://media.valorant-api.com/agents/0e38b510-41a8-5780-5e8f-568b2a4f2d6c/displayicon.png'),
('1e58de9c-4950-5125-93e9-a0aee9f98746', 'Killjoy', 'https://media.valorant-api.com/agents/1e58de9c-4950-5125-93e9-a0aee9f98746/displayicon.png'),
('95b78ed7-4637-86d9-7e41-71ba8c293152', 'Harbor', 'https://media.valorant-api.com/agents/95b78ed7-4637-86d9-7e41-71ba8c293152/displayicon.png'),
('22697a3d-45bf-8dd7-4fec-84a9e28c69d7', 'Chamber', 'https://media.valorant-api.com/agents/22697a3d-45bf-8dd7-4fec-84a9e28c69d7/displayicon.png'),
('707eab51-4836-f488-046a-cda6bf494859', 'Viper', 'https://media.valorant-api.com/agents/707eab51-4836-f488-046a-cda6bf494859/displayicon.png'),
('5f8d3a7f-467b-97f3-062c-13acf203c006', 'Breach', 'https://media.valorant-api.com/agents/5f8d3a7f-467b-97f3-062c-13acf203c006/displayicon.png'),
('9f0d8ba9-4140-b941-57d3-a7ad57c6b417', 'Brimstone', 'https://media.valorant-api.com/agents/9f0d8ba9-4140-b941-57d3-a7ad57c6b417/displayicon.png');

-- ============================================================
-- BOOSTERS CONFIG (admin-editable booster profiles)
-- ============================================================
CREATE TABLE IF NOT EXISTS boosters_config (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL UNIQUE,
  display_name VARCHAR(100) NOT NULL,
  avatar_url VARCHAR(500) DEFAULT NULL,
  rank_name VARCHAR(50) DEFAULT 'Radiante',
  winrate VARCHAR(10) DEFAULT '90%',
  role_desc VARCHAR(100) DEFAULT 'Duelista',
  bio TEXT,
  tags JSON,
  specialties JSON,
  agents JSON,
  maps JSON,
  peak_rank VARCHAR(50) DEFAULT 'Radiante',
  kda_avg VARCHAR(10) DEFAULT '8.2',
  mvp_rate VARCHAR(10) DEFAULT '87%',
  satisfaction VARCHAR(10) DEFAULT '100%',
  reviews_visible TINYINT(1) NOT NULL DEFAULT 1,
  is_featured TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  agent_id VARCHAR(36) DEFAULT NULL UNIQUE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- REVIEWS
-- ============================================================
CREATE TABLE IF NOT EXISTS reviews (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id VARCHAR(36) NOT NULL UNIQUE,
  client_id INT NOT NULL,
  booster_id INT NOT NULL,
  rating INT NOT NULL,
  comment TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  FOREIGN KEY (client_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (booster_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_reviews_booster (booster_id)
) ENGINE=InnoDB;

-- ============================================================
-- WITHDRAWALS (booster payout requests)
-- ============================================================
CREATE TABLE IF NOT EXISTS withdrawals (
  id INT AUTO_INCREMENT PRIMARY KEY,
  booster_id INT NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  pix_key VARCHAR(255) NOT NULL,
  pix_type ENUM('cpf', 'email', 'telefone', 'aleatoria') NOT NULL DEFAULT 'cpf',
  status ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
  admin_note TEXT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (booster_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_withdrawals_booster (booster_id),
  INDEX idx_withdrawals_status (status)
) ENGINE=InnoDB;

-- ============================================================
-- PROFILE BOOSTS (booster profile highlighting)
-- ============================================================
CREATE TABLE IF NOT EXISTS profile_boosts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  booster_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  days INT NOT NULL,
  starts_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  expires_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (booster_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_boost_booster (booster_id),
  INDEX idx_boost_expires (expires_at)
) ENGINE=InnoDB;

-- Seed boosters_config
INSERT INTO boosters_config (user_id, display_name, avatar_url, rank_name, winrate, role_desc, bio, tags, specialties, agents, maps, peak_rank, kda_avg, mvp_rate, satisfaction, reviews_visible, is_featured, sort_order, agent_id) VALUES
(2, 'Clove', 'https://media.valorant-api.com/agents/1dbf2edd-4729-0984-3115-daa5eed44993/displayicon.png', 'Radiante', '92%', 'Controlador', 'Booster profissional com mais de 500 serviços completados. Especialista em controladores.', '["Radiante","Verificado","Top 100"]', '["Elojob Solo","Duo Boost","Coaching"]', '["Clove","Omen","Astra"]', '["Ascent","Haven","Bind"]', 'Radiante #45', '8.5', '87%', '100%', 1, 1, 1, '1dbf2edd-4729-0984-3115-daa5eed44993'),
(3, 'Raze', 'https://media.valorant-api.com/agents/f94c3b30-42be-e959-889c-5aa313dba261/displayicon.png', 'Radiante', '89%', 'Duelista', 'Jogador agressivo com foco em duelistas. Alta taxa de vitória em todos os elos.', '["Radiante","Verificado"]', '["Elojob Solo","Duo Boost"]', '["Raze","Jett","Reyna"]', '["Split","Icebox","Lotus"]', 'Radiante #120', '7.8', '82%', '100%', 1, 1, 2, 'f94c3b30-42be-e959-889c-5aa313dba261');

-- Seed reviews
INSERT INTO reviews (order_id, client_id, booster_id, rating, comment) VALUES
('ord-001-demo', 4, 2, 5, 'Excelente serviço! Subiu meu elo rapidíssimo e com winrate absurdo.'),
('ord-002-demo', 4, 3, 4, 'Muito bom o duo boost, aprendi bastante jogando junto.');

-- Sample chat messages
INSERT INTO chat_messages (order_id, sender_id, message, created_at) VALUES
('ord-005-demo', 4, 'Oi! Pode começar quando quiser', '2026-02-23 11:35:00'),
('ord-005-demo', 2, 'Beleza, vou começar agora! VPN ativada.', '2026-02-23 11:36:00'),
('ord-005-demo', 4, 'Show, boa sorte!', '2026-02-23 11:37:00');
