SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS users(
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 telegram_id BIGINT NOT NULL UNIQUE,
 first_name VARCHAR(120) DEFAULT '', last_name VARCHAR(120) DEFAULT '',
 username VARCHAR(120) DEFAULT '', is_blocked TINYINT(1) NOT NULL DEFAULT 0,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS auto_replies(
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 keyword VARCHAR(191) NOT NULL UNIQUE, reply_text TEXT NOT NULL,
 active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bot_buttons(
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 title VARCHAR(191) NOT NULL,
 action_type ENUM('reply','submenu','url') NOT NULL DEFAULT 'reply',
 parent_id INT UNSIGNED NULL, response_text TEXT NULL,
 url VARCHAR(1000) NULL, url_label VARCHAR(191) NULL,
 row_no INT NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 1,
 active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 KEY(parent_id), KEY(row_no,sort_order),
 CONSTRAINT fk_parent FOREIGN KEY(parent_id) REFERENCES bot_buttons(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO auto_replies(keyword,reply_text) VALUES
('hello','👋 Hello! Welcome to our bot.'),
('price','💰 Pricing জানতে Menu ব্যবহার করুন।');

INSERT INTO bot_buttons(title,action_type,response_text,row_no,sort_order)
SELECT '🛠 সার্ভিস সমূহ','submenu','🚀 আমাদের Services:',1,1
WHERE NOT EXISTS(SELECT 1 FROM bot_buttons WHERE title='🛠 সার্ভিস সমূহ' AND parent_id IS NULL);
INSERT INTO bot_buttons(title,action_type,response_text,row_no,sort_order)
SELECT '📦 অর্ডার করুন','reply','📦 Order করতে Admin-এর সাথে যোগাযোগ করুন।',1,2
WHERE NOT EXISTS(SELECT 1 FROM bot_buttons WHERE title='📦 অর্ডার করুন' AND parent_id IS NULL);
INSERT INTO bot_buttons(title,action_type,response_text,row_no,sort_order)
SELECT '💰 মূল্য তালিকা','reply','💰 Pricing জানতে Admin-এর সাথে যোগাযোগ করুন।',2,1
WHERE NOT EXISTS(SELECT 1 FROM bot_buttons WHERE title='💰 মূল্য তালিকা' AND parent_id IS NULL);
INSERT INTO bot_buttons(title,action_type,url,url_label,response_text,row_no,sort_order)
SELECT '📢 আমাদের চ্যানেল','url','https://t.me/TEAM_NCL9','📢 Open Channel','Official Channel:',2,2
WHERE NOT EXISTS(SELECT 1 FROM bot_buttons WHERE title='📢 আমাদের চ্যানেল' AND parent_id IS NULL);
INSERT INTO bot_buttons(title,action_type,response_text,row_no,sort_order)
SELECT '📞 যোগাযোগ করুন','reply','👨‍💻 Admin-এর সাথে যোগাযোগ করুন।',3,1
WHERE NOT EXISTS(SELECT 1 FROM bot_buttons WHERE title='📞 যোগাযোগ করুন' AND parent_id IS NULL);