-- DARKEXS fresh installation ONLY. Do not import over existing data.
SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS users (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(160) NOT NULL, email VARCHAR(190) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, whatsapp VARCHAR(40) NULL, role ENUM('client','admin') NOT NULL DEFAULT 'client', status ENUM('active','banned') NOT NULL DEFAULT 'active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS packages (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, price_usd DECIMAL(14,2) NOT NULL, domain_limit INT UNSIGNED NOT NULL DEFAULT 1, ip_limit INT UNSIGNED NOT NULL DEFAULT 1, ggr_commission_percent DECIMAL(5,2) NOT NULL DEFAULT 0, enabled TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS package_providers (package_id BIGINT UNSIGNED NOT NULL, provider_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY(package_id,provider_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS providers (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(140) NOT NULL, enabled TINYINT(1) NOT NULL DEFAULT 0) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS client_packages (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, package_id BIGINT UNSIGNED NOT NULL, status ENUM('pending','active','expired','suspended') NOT NULL DEFAULT 'pending', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX(user_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS client_domains (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, domain VARCHAR(253) NOT NULL, status ENUM('pending','verified','rejected') NOT NULL DEFAULT 'pending', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY user_domain(user_id,domain)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS client_server_ips (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, ip VARCHAR(45) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY user_ip(user_id,ip)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS audit_logs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, actor_id BIGINT UNSIGNED NULL, action VARCHAR(100) NOT NULL, subject_id BIGINT UNSIGNED NULL, meta_json JSON NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX(actor_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Apply after database/schema.sql. Requires MySQL 8+.
ALTER TABLE users ADD COLUMN staff_login_token_hash CHAR(64) NULL, ADD COLUMN totp_secret VARCHAR(128) NULL, ADD COLUMN totp_enabled TINYINT(1) NOT NULL DEFAULT 0;
CREATE TABLE staff_permissions (user_id BIGINT UNSIGNED NOT NULL, permission VARCHAR(80) NOT NULL, PRIMARY KEY(user_id,permission));
CREATE TABLE staff_invites (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, token_hash CHAR(64) NOT NULL UNIQUE, expires_at DATETIME NOT NULL, used_at DATETIME NULL);
CREATE TABLE admin_login_events (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, ip VARCHAR(45) NULL, agent VARCHAR(255) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX(user_id));
CREATE TABLE admin_2fa_recovery (user_id BIGINT UNSIGNED NOT NULL, code_hash VARCHAR(255) NOT NULL);
CREATE TABLE client_bans (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, actor_id BIGINT UNSIGNED NOT NULL, reason VARCHAR(500) NOT NULL, expires_at DATETIME NULL, lifted_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

-- Run ONCE after schema.sql and step4.sql; MySQL 8+.
CREATE TABLE payment_destinations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, chain ENUM('TRC20','ERC20','BEP20','POLYGON') NOT NULL UNIQUE,
 address VARCHAR(160) NOT NULL, enabled TINYINT(1) NOT NULL DEFAULT 0,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE finance_deposits (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL,
 chain ENUM('TRC20','ERC20','BEP20','POLYGON') NOT NULL,
 destination_id BIGINT UNSIGNED NOT NULL, destination_address VARCHAR(160) NOT NULL,
 txid VARCHAR(160) NOT NULL, amount_usd DECIMAL(18,2) NOT NULL,
 status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
 reviewed_by BIGINT UNSIGNED NULL, reviewed_at DATETIME NULL, review_note VARCHAR(500) NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uniq_chain_txid(chain,txid),
 INDEX(user_id,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE finance_wallets (
 user_id BIGINT UNSIGNED PRIMARY KEY, available_usd DECIMAL(18,2) NOT NULL DEFAULT 0,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT positive_wallet CHECK(available_usd >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE finance_ledger (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL,
 kind ENUM('deposit','package_purchase','manual_adjustment') NOT NULL,
 delta_usd DECIMAL(18,2) NOT NULL, balance_after_usd DECIMAL(18,2) NOT NULL,
 reference_type VARCHAR(40) NOT NULL, reference_id BIGINT UNSIGNED NOT NULL,
 actor_id BIGINT UNSIGNED NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uniq_finance_reference(kind,reference_type,reference_id), INDEX(user_id,id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE finance_package_orders (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL,
 client_package_id BIGINT UNSIGNED NOT NULL UNIQUE, amount_usd DECIMAL(18,2) NOT NULL,
 status ENUM('paid') NOT NULL DEFAULT 'paid', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE finance_ggr_rules (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, package_id BIGINT UNSIGNED NOT NULL UNIQUE,
 commission_percent DECIMAL(5,2) NOT NULL, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT valid_ggr_percent CHECK(commission_percent BETWEEN 0 AND 100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- No provider bet/win/GGR events or automated payment confirmation in Step 5.

-- Run once after schema.sql, step4.sql, step5.sql (MySQL 8+).
CREATE TABLE provider_connections (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 provider_id BIGINT UNSIGNED NOT NULL UNIQUE,
 adapter VARCHAR(50) NOT NULL DEFAULT 'unconfigured',
 environment ENUM('sandbox','production') NOT NULL DEFAULT 'sandbox',
 base_url VARCHAR(500) NULL,
 enabled TINYINT(1) NOT NULL DEFAULT 0,
 last_sync_at DATETIME NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE provider_games (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 provider_id BIGINT UNSIGNED NOT NULL,
 external_game_id VARCHAR(150) NOT NULL,
 name VARCHAR(200) NOT NULL,
 category VARCHAR(60) NOT NULL DEFAULT 'casino',
 image_url VARCHAR(500) NULL,
 enabled TINYINT(1) NOT NULL DEFAULT 0,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uniq_provider_game(provider_id,external_game_id),
 INDEX idx_provider_category(provider_id,category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE client_api_keys (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 label VARCHAR(100) NOT NULL,
 key_prefix VARCHAR(24) NOT NULL,
 secret_hash CHAR(64) NOT NULL UNIQUE,
 environment ENUM('sandbox','production') NOT NULL DEFAULT 'sandbox',
 revoked_at DATETIME NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_client_keys(user_id,revoked_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE gateway_requests (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 client_key_id BIGINT UNSIGNED NULL,
 user_id BIGINT UNSIGNED NULL,
 provider_id BIGINT UNSIGNED NULL,
 outcome VARCHAR(50) NOT NULL,
 request_id VARCHAR(80) NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_gateway_user(user_id,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE gateway_nonces (
 key_id BIGINT UNSIGNED NOT NULL,
 nonce CHAR(32) NOT NULL,
 expires_at DATETIME NOT NULL,
 PRIMARY KEY(key_id,nonce), INDEX(expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE client_domain_verifications (
 domain_id BIGINT UNSIGNED PRIMARY KEY,
 token_hash CHAR(64) NOT NULL,
 requested_at DATETIME NOT NULL,
 verified_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Apply after schema.sql and step4.sql through step6.sql
CREATE TABLE IF NOT EXISTS cms_entries (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 section VARCHAR(40) NOT NULL,
 slug VARCHAR(90) NOT NULL,
 title VARCHAR(200) NOT NULL,
 body TEXT NULL,
 media_path VARCHAR(255) NULL,
 target_url VARCHAR(500) NULL,
 metadata_json JSON NULL,
 is_published TINYINT(1) NOT NULL DEFAULT 0,
 sort_order INT NOT NULL DEFAULT 0,
 updated_by BIGINT UNSIGNED NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY cms_section_slug (section,slug),
 KEY cms_public (section,is_published,sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS cms_settings (
 setting_key VARCHAR(90) PRIMARY KEY,
 setting_value TEXT NOT NULL,
 updated_by BIGINT UNSIGNED NULL,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO cms_settings(setting_key,setting_value) VALUES
 ('hero_title','Global Gaming API Solutions'),
 ('hero_subtitle','Integrate casino, slots, live casino and more with a flexible gaming API platform built for operators.'),
 ('footer_description','Gaming API integration solutions for businesses.'),
 ('company_country',''),
 ('company_email',''),('company_phone',''),('company_address',''),
 ('whatsapp_url',''),('telegram_url',''),('facebook_url','');

-- Step 9. Import after step7.sql, before enabling the updated PHP endpoints.
CREATE TABLE IF NOT EXISTS auth_rate_limits (
 rate_key CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL PRIMARY KEY,
 attempts INT UNSIGNED NOT NULL DEFAULT 0,
 first_attempt_at DATETIME NOT NULL,
 INDEX idx_auth_rate_started (first_attempt_at)
) ENGINE=InnoDB;
-- Schedule a daily cleanup in cPanel Cron or your DB scheduler:
-- DELETE FROM auth_rate_limits WHERE first_attempt_at < UTC_TIMESTAMP() - INTERVAL 2 DAY;

-- Step 10: reporting endpoints operate on existing Step 5 tables.
-- No additional migration required. Run schema.sql and steps 4,5,6,7,9 first.
-- Do not use finance_ggr_rules as proof of live GGR settlement.

-- Step 11: import AFTER step10.sql; MySQL 8+
CREATE TABLE IF NOT EXISTS finance_activation_reviews (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 order_id BIGINT UNSIGNED NOT NULL UNIQUE,
 admin_id BIGINT UNSIGNED NOT NULL,
 review_note VARCHAR(500) NOT NULL,
 reviewed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE login_attempts (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, ip VARCHAR(45) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX(ip,created_at)) ENGINE=InnoDB;
CREATE TABLE tickets (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,subject VARCHAR(200) NOT NULL,priority ENUM('normal','high') DEFAULT 'normal',status ENUM('open','closed') DEFAULT 'open',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(user_id) REFERENCES users(id)) ENGINE=InnoDB;
CREATE TABLE ticket_messages (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,ticket_id BIGINT UNSIGNED NOT NULL,user_id BIGINT UNSIGNED NOT NULL,body TEXT NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(ticket_id) REFERENCES tickets(id),FOREIGN KEY(user_id) REFERENCES users(id)) ENGINE=InnoDB;
CREATE TABLE notifications (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,title VARCHAR(200) NOT NULL,body TEXT NOT NULL,read_at DATETIME NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(user_id) REFERENCES users(id)) ENGINE=InnoDB;
CREATE TABLE webhook_endpoints (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,url VARCHAR(500) NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(user_id) REFERENCES users(id)) ENGINE=InnoDB;


-- INITIAL ADMIN SEED: fresh database only; no shared default password.
START TRANSACTION;
INSERT INTO users(name,email,password_hash,role,status,staff_login_token_hash,totp_enabled) VALUES ('DARKEXS Administrator','admin@darkexs.local','$2a$12$ottWhHctIot7HKRlhILOjujqfVVehz4U6ByVHxn3wyUzilOj4hc9W','admin','active','5ed051ea66ce5685e758a03aee64a49039d86114775b053ae184fba8d8804b6a',0);
SET @darkexs_initial_admin_id = LAST_INSERT_ID();
INSERT INTO staff_permissions(user_id,permission) VALUES
(@darkexs_initial_admin_id,'clients.view'),
(@darkexs_initial_admin_id,'clients.ban'),
(@darkexs_initial_admin_id,'staff.manage'),
(@darkexs_initial_admin_id,'packages.manage'),
(@darkexs_initial_admin_id,'providers.manage'),
(@darkexs_initial_admin_id,'audit.view'),
(@darkexs_initial_admin_id,'finance.review'),
(@darkexs_initial_admin_id,'finance.settings'),
(@darkexs_initial_admin_id,'cms.manage');
COMMIT;