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

CREATE TABLE IF NOT EXISTS users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 telegram_id BIGINT UNSIGNED NOT NULL UNIQUE,
 first_name VARCHAR(150) NOT NULL DEFAULT '', username VARCHAR(150) DEFAULT NULL,
 balance BIGINT NOT NULL DEFAULT 0, referrer_id BIGINT UNSIGNED DEFAULT NULL,
 is_blocked TINYINT(1) NOT NULL DEFAULT 0,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX(referrer_id), CONSTRAINT fk_user_ref FOREIGN KEY(referrer_id) REFERENCES users(id)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS admins (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 username VARCHAR(80) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL,
 created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS settings (`key` VARCHAR(80) PRIMARY KEY, `value` TEXT NOT NULL) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS products (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(200) NOT NULL,
 description TEXT NOT NULL, price BIGINT NOT NULL, stock INT DEFAULT NULL,
 requirement_mode ENUM('none','fields') NOT NULL DEFAULT 'none',
 requirements_json JSON DEFAULT NULL, active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX(active,created_at)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS product_images (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, product_id BIGINT UNSIGNED NOT NULL,
 file_path VARCHAR(255) NOT NULL, sort_order INT NOT NULL DEFAULT 0,
 FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS orders (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NOT NULL,
 quantity INT UNSIGNED NOT NULL, unit_price BIGINT NOT NULL, total BIGINT NOT NULL,
 responses_json JSON DEFAULT NULL,
 status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, reviewed_at DATETIME DEFAULT NULL,
 FOREIGN KEY(user_id) REFERENCES users(id), FOREIGN KEY(product_id) REFERENCES products(id),
 INDEX(user_id,status,created_at), INDEX(status,created_at)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS order_deliverables (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_id BIGINT UNSIGNED NOT NULL,
 kind ENUM('text','link','zip') NOT NULL, label VARCHAR(150) NOT NULL,
 content TEXT DEFAULT NULL, storage_path VARCHAR(255) DEFAULT NULL,
 FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS wallet_transactions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL,
 amount BIGINT NOT NULL, balance_after BIGINT NOT NULL,
 kind ENUM('purchase','refund','deposit','withdraw','withdraw_refund','referral','admin_adjust') NOT NULL,
 reference_type VARCHAR(30) NOT NULL, reference_id BIGINT UNSIGNED NOT NULL,
 description VARCHAR(255) NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uniq_ledger (kind,reference_type,reference_id,user_id),
 INDEX(user_id,id), FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS payment_methods (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, kind ENUM('deposit','withdraw') NOT NULL,
 name VARCHAR(120) NOT NULL, account VARCHAR(200) NOT NULL DEFAULT '',
 instructions TEXT NOT NULL, active TINYINT(1) NOT NULL DEFAULT 1,
 sort_order INT NOT NULL DEFAULT 0, INDEX(kind,active)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS deposits (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL,
 method_id BIGINT UNSIGNED NOT NULL, amount BIGINT NOT NULL, trx_id VARCHAR(120) NOT NULL,
 status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
 created_at DATETIME DEFAULT CURRENT_TIMESTAMP, reviewed_at DATETIME DEFAULT NULL,
 FOREIGN KEY(user_id) REFERENCES users(id), FOREIGN KEY(method_id) REFERENCES payment_methods(id),
 INDEX(status,created_at), INDEX(user_id,id), UNIQUE KEY uniq_payment_trx(method_id,trx_id)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS withdrawals (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL,
 method_id BIGINT UNSIGNED NOT NULL, amount BIGINT NOT NULL, account VARCHAR(200) NOT NULL,
 status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
 created_at DATETIME DEFAULT CURRENT_TIMESTAMP, reviewed_at DATETIME DEFAULT NULL,
 FOREIGN KEY(user_id) REFERENCES users(id), FOREIGN KEY(method_id) REFERENCES payment_methods(id),
 INDEX(status,created_at), INDEX(user_id,id)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS referral_rewards (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_id BIGINT UNSIGNED NOT NULL UNIQUE,
 referrer_id BIGINT UNSIGNED NOT NULL, referred_user_id BIGINT UNSIGNED NOT NULL,
 amount BIGINT NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(order_id) REFERENCES orders(id), FOREIGN KEY(referrer_id) REFERENCES users(id),
 FOREIGN KEY(referred_user_id) REFERENCES users(id)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS announcements (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, message TEXT NOT NULL,
 status ENUM('queued','sending','done') NOT NULL DEFAULT 'queued',
 next_user_id BIGINT UNSIGNED NOT NULL DEFAULT 0, sent_count INT NOT NULL DEFAULT 0,
 created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS webhook_updates (
 update_id BIGINT NOT NULL PRIMARY KEY, received_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS rate_limits (
 bucket VARCHAR(100) PRIMARY KEY, hits INT NOT NULL, window_start INT UNSIGNED NOT NULL
) ENGINE=InnoDB;

INSERT IGNORE INTO settings (`key`,`value`) VALUES
('app_name','Sukubu Ai Shop'),('app_logo',''),('currency','BDT'),('currency_symbol','৳'),
('support_link','https://t.me/'),('primary_color','#6657f5'),('secondary_color','#17b9a3'),
('maintenance','0'),('maintenance_text','সাময়িক রক্ষণাবেক্ষণ চলছে। কিছুক্ষণ পর আবার চেষ্টা করুন।'),
('min_withdraw','10000'),('max_withdraw','1000000'),
('min_deposit','10000'),('max_deposit','10000000'),
('referral_type','percent'),('referral_value','500'),
('announcement_cooldown_minutes','60'),('banner_text','ডিজিটাল পণ্যে সহজ, দ্রুত এবং নিরাপদ কেনাকাটা'),
('notice_text','সব অর্ডার যাচাই করে অনুমোদন করা হয়।');
CREATE TABLE IF NOT EXISTS admin_actions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, admin_id BIGINT UNSIGNED NOT NULL,
 action VARCHAR(100) NOT NULL, subject_type VARCHAR(40) NOT NULL,
 subject_id BIGINT UNSIGNED NOT NULL, note VARCHAR(255) NOT NULL DEFAULT '',
 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(admin_id) REFERENCES admins(id), INDEX(created_at)
) ENGINE=InnoDB;