CREATE TABLE IF NOT EXISTS settings (k VARCHAR(64) NOT NULL PRIMARY KEY, v MEDIUMTEXT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS texts (k VARCHAR(64) NOT NULL PRIMARY KEY, v MEDIUMTEXT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS buttons (k VARCHAR(64) NOT NULL PRIMARY KEY, label VARCHAR(120) NOT NULL, style VARCHAR(12) NOT NULL DEFAULT '', enabled TINYINT NOT NULL DEFAULT 1, sort INT NOT NULL DEFAULT 0) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS admins (id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(60) NOT NULL UNIQUE, pass_hash VARCHAR(255) NOT NULL, role VARCHAR(10) NOT NULL DEFAULT 'owner', created_at INT NOT NULL DEFAULT 0) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS tiers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(80) NOT NULL, discount_percent DECIMAL(5,2) NOT NULL DEFAULT 0, min_balance BIGINT NOT NULL DEFAULT 0, min_purchases INT NOT NULL DEFAULT 0, auto_upgrade TINYINT NOT NULL DEFAULT 0, is_default TINYINT NOT NULL DEFAULT 0, public TINYINT NOT NULL DEFAULT 1, sort INT NOT NULL DEFAULT 0, note TEXT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, tg_id BIGINT NOT NULL UNIQUE, username VARCHAR(64) NULL, first_name VARCHAR(120) NULL, phone VARCHAR(30) NULL, balance BIGINT NOT NULL DEFAULT 0, tier_id INT NOT NULL DEFAULT 1, blocked TINYINT NOT NULL DEFAULT 0, ref_by BIGINT NULL, ref_paid TINYINT NOT NULL DEFAULT 0, dcode VARCHAR(40) NULL, state VARCHAR(40) NULL, sdata TEXT NULL, purchases INT NOT NULL DEFAULT 0, note TEXT NULL, created_at INT NOT NULL DEFAULT 0, seen_at INT NOT NULL DEFAULT 0) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS categories (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, sort INT NOT NULL DEFAULT 0, active TINYINT NOT NULL DEFAULT 1) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS products (id INT AUTO_INCREMENT PRIMARY KEY, category_id INT NULL, title VARCHAR(190) NOT NULL, price BIGINT NOT NULL DEFAULT 0, description TEXT NULL, warranty_hours INT NOT NULL DEFAULT 48, min_qty INT NOT NULL DEFAULT 1, max_qty INT NOT NULL DEFAULT 20, sort INT NOT NULL DEFAULT 0, active TINYINT NOT NULL DEFAULT 1, created_at INT NOT NULL DEFAULT 0) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS tier_prices (id INT AUTO_INCREMENT PRIMARY KEY, product_id INT NOT NULL, tier_id INT NOT NULL, price BIGINT NOT NULL, UNIQUE KEY uq_tp (product_id, tier_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS stock_items (id BIGINT AUTO_INCREMENT PRIMARY KEY, product_id INT NOT NULL, email VARCHAR(190) NOT NULL, password VARCHAR(190) NOT NULL DEFAULT '', birth VARCHAR(40) NOT NULL DEFAULT '', q1 VARCHAR(190) NOT NULL DEFAULT '', q2 VARCHAR(190) NOT NULL DEFAULT '', q3 VARCHAR(190) NOT NULL DEFAULT '', extra TEXT NULL, status VARCHAR(12) NOT NULL DEFAULT 'available', order_id INT NULL, gen TINYINT NOT NULL DEFAULT 0, reserved_until INT NULL, created_at INT NOT NULL DEFAULT 0, sold_at INT NULL, UNIQUE KEY uq_item (product_id, email), KEY ix_pick (product_id, status), KEY ix_order (order_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS orders (id INT AUTO_INCREMENT PRIMARY KEY, code VARCHAR(24) NOT NULL UNIQUE, tracking VARCHAR(16) NOT NULL DEFAULT '', user_id INT NOT NULL, product_id INT NOT NULL, title VARCHAR(190) NOT NULL DEFAULT '', qty INT NOT NULL DEFAULT 1, unit_price BIGINT NOT NULL DEFAULT 0, discount BIGINT NOT NULL DEFAULT 0, total BIGINT NOT NULL DEFAULT 0, dcode VARCHAR(40) NULL, status VARCHAR(12) NOT NULL DEFAULT 'pending', warranty_hours INT NOT NULL DEFAULT 0, warranty_until INT NULL, expires_at INT NOT NULL DEFAULT 0, created_at INT NOT NULL DEFAULT 0, delivered_at INT NULL, KEY ix_user (user_id), KEY ix_status (status)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS payments (id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, order_id INT NULL, amount BIGINT NOT NULL, method VARCHAR(12) NOT NULL, status VARCHAR(12) NOT NULL DEFAULT 'pending', authority VARCHAR(64) NULL, ref_id VARCHAR(64) NULL, card_id INT NULL, file_id VARCHAR(255) NULL, note VARCHAR(255) NULL, created_at INT NOT NULL DEFAULT 0, settled_at INT NULL, KEY ix_auth (authority), KEY ix_status (status)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS ledger (id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, amount BIGINT NOT NULL, balance_after BIGINT NOT NULL, type VARCHAR(16) NOT NULL, ref VARCHAR(40) NULL, note VARCHAR(255) NULL, created_at INT NOT NULL DEFAULT 0, KEY ix_user (user_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS discounts (id INT AUTO_INCREMENT PRIMARY KEY, code VARCHAR(40) NOT NULL UNIQUE, percent DECIMAL(5,2) NOT NULL DEFAULT 0, fixed BIGINT NOT NULL DEFAULT 0, max_uses INT NOT NULL DEFAULT 0, used INT NOT NULL DEFAULT 0, min_total BIGINT NOT NULL DEFAULT 0, product_id INT NULL, expires_on DATE NULL, active TINYINT NOT NULL DEFAULT 1) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS cards (id INT AUTO_INCREMENT PRIMARY KEY, number VARCHAR(40) NOT NULL, holder VARCHAR(120) NOT NULL DEFAULT '', bank VARCHAR(80) NOT NULL DEFAULT '', active TINYINT NOT NULL DEFAULT 1) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS tickets (id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, order_id INT NULL, subject VARCHAR(190) NOT NULL DEFAULT '', kind VARCHAR(10) NOT NULL DEFAULT 'ticket', status VARCHAR(10) NOT NULL DEFAULT 'open', created_at INT NOT NULL DEFAULT 0, updated_at INT NOT NULL DEFAULT 0, KEY ix_user (user_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS ticket_messages (id INT AUTO_INCREMENT PRIMARY KEY, ticket_id INT NOT NULL, from_admin TINYINT NOT NULL DEFAULT 0, body TEXT NULL, file_id VARCHAR(255) NULL, created_at INT NOT NULL DEFAULT 0, KEY ix_t (ticket_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS replacements (id INT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, stock_id BIGINT NOT NULL, new_stock_id BIGINT NULL, reason VARCHAR(255) NULL, status VARCHAR(10) NOT NULL DEFAULT 'pending', created_at INT NOT NULL DEFAULT 0, decided_at INT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS notify_stock (user_id INT NOT NULL, product_id INT NOT NULL, PRIMARY KEY (user_id, product_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS broadcasts (id INT AUTO_INCREMENT PRIMARY KEY, body TEXT NOT NULL, total INT NOT NULL DEFAULT 0, sent INT NOT NULL DEFAULT 0, failed INT NOT NULL DEFAULT 0, last_user_id INT NOT NULL DEFAULT 0, status VARCHAR(10) NOT NULL DEFAULT 'running', created_at INT NOT NULL DEFAULT 0) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS logs (id BIGINT AUTO_INCREMENT PRIMARY KEY, level VARCHAR(8) NOT NULL DEFAULT 'warn', msg TEXT NULL, created_at INT NOT NULL DEFAULT 0) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS processed_updates (update_id BIGINT NOT NULL PRIMARY KEY, created_at INT NOT NULL DEFAULT 0) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4