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

CREATE TABLE IF NOT EXISTS admins (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO admins(username,password_hash) VALUES
('admin', '$2y$10$f7XUez6COt6v1BZc5eAK5.IFiU3PuhS2ncRPvw5A2xWbvhjWMN5ZW')
ON DUPLICATE KEY UPDATE username=username;
-- login: admin  password: admin123

CREATE TABLE IF NOT EXISTS settings (
  `key` VARCHAR(100) PRIMARY KEY,
  `value` TEXT
);

INSERT INTO settings(`key`,`value`) VALUES
('bot_name','Шымбай Авто Келешек'),
('welcome_text','Ассалаўма әлейкум! Шымбай Авто Келешек авто мектебиниң рәсмий ботына хош келдиңиз.'),
('phone1','+998 90 658 79 79'),
('phone2','+998 99 955 79 29'),
('address','Шымбай районы, Бағдар МРЭО ГАИ'),
('instagram',''),
('telegram_group',''),
('logo','assets/uploads/logo.jpg')
ON DUPLICATE KEY UPDATE value=VALUES(value);

CREATE TABLE IF NOT EXISTS categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(100) NOT NULL,
  study_period VARCHAR(100) NOT NULL,
  price VARCHAR(100) NOT NULL,
  description TEXT,
  sort_order INT DEFAULT 0,
  is_active TINYINT DEFAULT 1
);

INSERT INTO categories(title,study_period,price,description,sort_order) VALUES
('A категориясы','2 ай','2 000 000 сум','Мотоцикл категориясы',1),
('B категориясы','2 ай 15 күн','4 000 000 сум','Жеңил автомобиль категориясы',2),
('BC категориясы','5 ай 15 күн','Келисим бойынша','B ҳәм C категориялары',3)
ON DUPLICATE KEY UPDATE title=VALUES(title);

CREATE TABLE IF NOT EXISTS documents (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  body TEXT,
  sort_order INT DEFAULT 0,
  is_active TINYINT DEFAULT 1
);
INSERT INTO documents(title,body,sort_order) VALUES
('Керекли ҳүжжетлер','Паспорт нусқасы\n3x4 фото\nМед справка\nТөлем квитанциясы',1)
ON DUPLICATE KEY UPDATE title=VALUES(title);

CREATE TABLE IF NOT EXISTS info_pages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(100) UNIQUE NOT NULL,
  title VARCHAR(255) NOT NULL,
  body TEXT
);
INSERT INTO info_pages(slug,title,body) VALUES
('payments','Оқыў төлемлери','Төлемлер категория бойынша белгиленеди. Толық мағлыўмат ушын админ менен байланысыңыз.'),
('period','Оқыў мүддети','A: 2 ай\nB: 2 ай 15 күн\nBC: 5 ай 15 күн'),
('route','Маршрут','Базардан 5-7-10-12 маршрутлар жүреди.')
ON DUPLICATE KEY UPDATE title=VALUES(title), body=VALUES(body);

CREATE TABLE IF NOT EXISTS tests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  is_active TINYINT DEFAULT 1
);
INSERT INTO tests(title) VALUES ('Авто мектеп тестлери') ON DUPLICATE KEY UPDATE title=VALUES(title);

CREATE TABLE IF NOT EXISTS test_questions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  test_id INT NOT NULL,
  question TEXT NOT NULL,
  option_a VARCHAR(255), option_b VARCHAR(255), option_c VARCHAR(255), option_d VARCHAR(255),
  correct_option ENUM('A','B','C','D') NOT NULL DEFAULT 'A',
  FOREIGN KEY (test_id) REFERENCES tests(id) ON DELETE CASCADE
);
INSERT INTO test_questions(test_id,question,option_a,option_b,option_c,option_d,correct_option) VALUES
(1,'Қызыл светофор жанғанда не қыласыз?','Тоқтайман','Жүреман','Тез өтемен','Сигнал беремен','A'),
(1,'Жол белгиси нени билдиреди?','Ескертиў','Рухсат','Тыйым','Хызмет','C')
ON DUPLICATE KEY UPDATE question=VALUES(question);

CREATE TABLE IF NOT EXISTS bot_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  telegram_id BIGINT UNIQUE NOT NULL,
  first_name VARCHAR(255), username VARCHAR(255),
  is_active TINYINT DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS test_results (
  id INT AUTO_INCREMENT PRIMARY KEY,
  telegram_id BIGINT NOT NULL,
  test_id INT NOT NULL,
  score INT DEFAULT 0,
  total INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_name VARCHAR(255) NOT NULL,
  phone VARCHAR(50),
  amount DECIMAL(12,2) DEFAULT 0,
  note TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);