-- BDG V11 Premium Admin - complete control-center add-on schema
-- Import this AFTER the original BDG legacy database. Existing tables/data are preserved.
SET time_zone = '+05:30';

CREATE TABLE IF NOT EXISTS app_settings (
  setting_key VARCHAR(100) NOT NULL, setting_value TEXT NOT NULL,
  group_name VARCHAR(80) NOT NULL DEFAULT 'General', updated_at DATETIME NOT NULL,
  PRIMARY KEY (setting_key), KEY idx_app_settings_group (group_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_payment_methods (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT, code VARCHAR(40) NOT NULL, name VARCHAR(100) NOT NULL,
  method_type VARCHAR(40) NOT NULL, enabled TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 0,
  min_amount DECIMAL(18,2) NOT NULL DEFAULT 100, max_amount DECIMAL(18,2) NOT NULL DEFAULT 50000,
  instructions TEXT NULL, config_json LONGTEXT NULL, updated_at DATETIME NOT NULL,
  PRIMARY KEY (id), UNIQUE KEY uq_app_payment_code (code), KEY idx_app_payment_enabled (enabled,sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_deposit_requests (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT NOT NULL, method_code VARCHAR(40) NOT NULL,
  amount DECIMAL(18,2) NOT NULL, reference_no VARCHAR(120) NOT NULL, receipt_path VARCHAR(255) NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending', admin_note VARCHAR(255) NULL, credited_at DATETIME NULL,
  created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL,
  PRIMARY KEY (id), UNIQUE KEY uq_app_deposit_reference (reference_no),
  KEY idx_app_deposit_user (user_id,created_at), KEY idx_app_deposit_status (status,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_chat_sessions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT NOT NULL, subject VARCHAR(190) NOT NULL DEFAULT 'Support',
  status VARCHAR(20) NOT NULL DEFAULT 'open', last_message_at DATETIME NOT NULL, closed_by VARCHAR(80) NULL,
  created_at DATETIME NOT NULL, closed_at DATETIME NULL,
  PRIMARY KEY (id), KEY idx_app_chat_user (user_id,status), KEY idx_app_chat_status (status,last_message_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_chat_messages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, session_id BIGINT UNSIGNED NOT NULL,
  sender_type VARCHAR(20) NOT NULL, sender_id VARCHAR(80) NOT NULL, message_type VARCHAR(20) NOT NULL DEFAULT 'text',
  message_text TEXT NULL, attachment_path VARCHAR(255) NULL, context_type VARCHAR(30) NULL,
  context_id VARCHAR(100) NULL, metadata_json LONGTEXT NULL, created_at DATETIME NOT NULL,
  PRIMARY KEY (id), KEY idx_app_chat_messages_session (session_id,id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_audit_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, admin_name VARCHAR(100) NOT NULL, action VARCHAR(100) NOT NULL,
  entity_type VARCHAR(80) NULL, entity_id VARCHAR(100) NULL, metadata_json LONGTEXT NULL,
  ip_hash CHAR(64) NULL, created_at DATETIME NOT NULL,
  PRIMARY KEY (id), KEY idx_app_audit_created (created_at), KEY idx_app_audit_admin (admin_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_admin_permissions (
  admin_id BIGINT NOT NULL, dashboard TINYINT(1) NOT NULL DEFAULT 0,
  users TINYINT(1) NOT NULL DEFAULT 0, wingomanager TINYINT(1) NOT NULL DEFAULT 0,
  trxwingomanager TINYINT(1) NOT NULL DEFAULT 0, k3manager TINYINT(1) NOT NULL DEFAULT 0,
  `5dmanager` TINYINT(1) NOT NULL DEFAULT 0, motoracingmanager TINYINT(1) NOT NULL DEFAULT 0,
  finance TINYINT(1) NOT NULL DEFAULT 0, support TINYINT(1) NOT NULL DEFAULT 0,
  managegame TINYINT(1) NOT NULL DEFAULT 0, updated_at DATETIME NOT NULL,
  PRIMARY KEY (admin_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_user_presence (
  user_id BIGINT NOT NULL, last_seen DATETIME NOT NULL, ip_hash CHAR(64) NULL,
  user_agent_hash CHAR(64) NULL, updated_at DATETIME NOT NULL,
  PRIMARY KEY (user_id), KEY idx_app_presence_last_seen (last_seen)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_user_preferences (
  user_id BIGINT NOT NULL, language VARCHAR(20) NULL, user_photo VARCHAR(20) NULL,
  guidelines_finished TINYINT(1) NOT NULL DEFAULT 0, updated_at DATETIME NOT NULL,
  PRIMARY KEY (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_system_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, event_type VARCHAR(80) NOT NULL,
  severity VARCHAR(20) NOT NULL DEFAULT 'info', message VARCHAR(255) NOT NULL,
  metadata_json LONGTEXT NULL, created_at DATETIME NOT NULL,
  PRIMARY KEY (id), KEY idx_app_events_created (created_at), KEY idx_app_events_type (event_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_support_tickets (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT NOT NULL, category VARCHAR(80) NOT NULL,
  subject VARCHAR(190) NOT NULL, message TEXT NULL, reference_no VARCHAR(120) NULL,
  amount DECIMAL(18,2) NULL, status VARCHAR(20) NOT NULL DEFAULT 'pending', priority VARCHAR(20) NOT NULL DEFAULT 'normal',
  admin_note TEXT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, closed_at DATETIME NULL,
  PRIMARY KEY (id), KEY idx_app_support_user (user_id,created_at),
  KEY idx_app_support_status (status,updated_at), KEY idx_app_support_category (category,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_game_control (
  game_code VARCHAR(40) NOT NULL, enabled TINYINT(1) NOT NULL DEFAULT 1,
  result_source VARCHAR(30) NOT NULL DEFAULT 'local_random', api_url TEXT NULL,
  api_timeout_seconds INT NOT NULL DEFAULT 5, lock_before_close_seconds INT NOT NULL DEFAULT 2,
  manual_override_enabled TINYINT(1) NOT NULL DEFAULT 1, updated_at DATETIME NOT NULL,
  PRIMARY KEY (game_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_bet_requests (
  request_key CHAR(64) NOT NULL, user_id BIGINT NOT NULL, game_type_id INT NOT NULL,
  issue_number VARCHAR(80) NOT NULL, bet_table VARCHAR(80) NOT NULL, bet_row_id BIGINT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'processing', created_at DATETIME NOT NULL,
  completed_at DATETIME NULL, PRIMARY KEY (request_key),
  KEY idx_app_bet_user (user_id,created_at),
  KEY idx_app_bet_issue (game_type_id,issue_number,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_turntable_draws (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT NOT NULL,
  prize_id INT NULL, prize_label VARCHAR(100) NOT NULL,
  prize_amount DECIMAL(18,2) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL,
  PRIMARY KEY (id), KEY idx_app_turntable_user (user_id,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_provider_transactions (
  tx_key CHAR(64) NOT NULL, provider_tx_id VARCHAR(120) NOT NULL,
  user_id BIGINT NOT NULL, operation VARCHAR(40) NOT NULL,
  amount DECIMAL(18,2) NOT NULL DEFAULT 0, status VARCHAR(20) NOT NULL DEFAULT 'processing',
  response_json LONGTEXT NULL, created_at DATETIME NOT NULL, completed_at DATETIME NULL,
  PRIMARY KEY (tx_key), KEY idx_provider_user (user_id,created_at),
  KEY idx_provider_reference (provider_tx_id,operation)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_site_messages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, title VARCHAR(190) NOT NULL, message TEXT NOT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 0,
  starts_at DATETIME NULL, ends_at DATETIME NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL,
  PRIMARY KEY (id), KEY idx_site_messages_active (enabled,sort_order), KEY idx_site_messages_window (starts_at,ends_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_notifications (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT NULL, title VARCHAR(190) NOT NULL,
  message TEXT NOT NULL, type VARCHAR(30) NOT NULL DEFAULT 'general', enabled TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL,
  PRIMARY KEY (id), KEY idx_notifications_target (user_id,enabled,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_notification_reads (
  notification_id BIGINT UNSIGNED NOT NULL, user_id BIGINT NOT NULL,
  state TINYINT(1) NOT NULL DEFAULT 1, read_at DATETIME NOT NULL,
  PRIMARY KEY (notification_id,user_id), KEY idx_notification_reads_user (user_id,state)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_spin_prizes (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT, label VARCHAR(100) NOT NULL,
  amount DECIMAL(18,2) NOT NULL DEFAULT 0, weight INT UNSIGNED NOT NULL DEFAULT 1,
  first_spin TINYINT(1) NOT NULL DEFAULT 0, enabled TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0, updated_at DATETIME NOT NULL,
  PRIMARY KEY (id), KEY idx_spin_prizes_pool (enabled,first_spin,sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_channel_bonus_rules (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT, method_code VARCHAR(40) NOT NULL DEFAULT '*',
  title VARCHAR(120) NOT NULL, min_amount DECIMAL(18,2) NOT NULL DEFAULT 0,
  max_amount DECIMAL(18,2) NOT NULL DEFAULT 0, bonus_percent DECIMAL(8,4) NOT NULL DEFAULT 0,
  bonus_fixed DECIMAL(18,2) NOT NULL DEFAULT 0, max_bonus DECIMAL(18,2) NOT NULL DEFAULT 0,
  enabled TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 0, updated_at DATETIME NOT NULL,
  PRIMARY KEY (id), KEY idx_channel_bonus_match (enabled,method_code,min_amount,max_amount,sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS vip (
  userid BIGINT NOT NULL, expe BIGINT NOT NULL DEFAULT 0, lvl INT NOT NULL DEFAULT 0,
  createdate DATETIME NULL, PRIMARY KEY (userid), KEY idx_vip_level (lvl,expe)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tb_agent (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, userid BIGINT NOT NULL, mobile VARCHAR(32) NOT NULL,
  createdate DATETIME NOT NULL, status TINYINT NOT NULL DEFAULT 1, type VARCHAR(20) NOT NULL DEFAULT 'month',
  salary DECIMAL(18,2) NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY uq_agent_user (userid),
  KEY idx_agent_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS shonu_turntable (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT NOT NULL,
  invited_wheel_amount DECIMAL(18,2) NOT NULL DEFAULT 0, total_spins INT NOT NULL DEFAULT 0,
  PRIMARY KEY (id), UNIQUE KEY uq_turntable_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS shonu_turntable_spins (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT NOT NULL,
  prize_amount DECIMAL(18,2) NOT NULL DEFAULT 0, spin_time DATETIME NOT NULL,
  user_name VARCHAR(100) NOT NULL DEFAULT 'User', prize_id INT NULL,
  is_credited TINYINT(1) NOT NULL DEFAULT 0, PRIMARY KEY (id),
  KEY idx_turntable_spins_user (user_id,spin_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_spin_daily_grants (
  user_id BIGINT NOT NULL, grant_date DATE NOT NULL, amount INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL, PRIMARY KEY (user_id,grant_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_spin_transfers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT NOT NULL,
  amount DECIMAL(18,2) NOT NULL, balance_before DECIMAL(18,2) NOT NULL,
  balance_after DECIMAL(18,2) NOT NULL, created_at DATETIME NOT NULL,
  PRIMARY KEY (id), KEY idx_spin_transfers_user (user_id,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO app_settings(setting_key,setting_value,group_name,updated_at) VALUES
('control_center_schema_version','20260722-v11.7','Internal',NOW()),
('site_messages_enabled','1','General',NOW()),('notifications_enabled','1','General',NOW()),
('spin_enabled','1','General',NOW()),('daily_spin_limit','1','General',NOW()),
('maintenance_message','We are improving the platform. Please check back shortly.','General',NOW()),
('registration_starting_balance','0','Wallet & Finance',NOW()),
('registration_bonus','0','Wallet & Finance',NOW()),
('demo_starting_balance','5000','Wallet & Finance',NOW()),
('demo_withdraw_auto_complete','1','Wallet & Finance',NOW());

INSERT IGNORE INTO app_payment_methods(code,name,method_type,enabled,sort_order,min_amount,max_amount,instructions,config_json,updated_at) VALUES
('manual_upi','Manual UPI','upi',1,10,100,50000,'Submit a payment request with the correct UTR/reference and PNG receipt.','{}',NOW()),
('qr_deposit','QR Deposit','qr',1,20,100,50000,'Scan the configured QR and submit the payment reference.','{}',NOW()),
('bank_transfer','Bank Transfer','bank',1,30,500,100000,'Use the configured bank instructions and submit the transaction reference.','{}',NOW()),
('usdt_demo','USDT','usdt',0,40,10,10000,'Optional administrator-managed USDT request flow.','{}',NOW());

INSERT INTO app_spin_prizes(label,amount,weight,first_spin,enabled,sort_order,updated_at)
SELECT 'Small reward',1.00,35,0,1,10,NOW() FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM app_spin_prizes LIMIT 1);
INSERT INTO app_spin_prizes(label,amount,weight,first_spin,enabled,sort_order,updated_at)
SELECT 'Reward',2.00,25,0,1,20,NOW() FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM app_spin_prizes WHERE label='Reward');
INSERT INTO app_spin_prizes(label,amount,weight,first_spin,enabled,sort_order,updated_at)
SELECT 'Try again',0.00,15,0,1,30,NOW() FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM app_spin_prizes WHERE label='Try again');
INSERT INTO app_spin_prizes(label,amount,weight,first_spin,enabled,sort_order,updated_at)
SELECT 'Welcome reward',5.00,100,1,1,10,NOW() FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM app_spin_prizes WHERE label='Welcome reward');