-- MySQL schema for Court Booking System
CREATE DATABASE IF NOT EXISTS court_booking CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE court_booking;

-- users (clients)
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  whatsapp VARCHAR(32) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_whatsapp (whatsapp)
);

-- admin users
CREATE TABLE IF NOT EXISTS admin_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(60) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- courts (quadras)
CREATE TABLE IF NOT EXISTS courts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  active TINYINT(1) DEFAULT 1
);

-- time slots (horários)
CREATE TABLE IF NOT EXISTS timeslots (
  id INT AUTO_INCREMENT PRIMARY KEY,
  court_id INT NOT NULL,
  date DATE NOT NULL,
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  status ENUM('available','reserved','paid','blocked') DEFAULT 'available',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (court_id) REFERENCES courts(id)
);

-- bookings (reservas)
CREATE TABLE IF NOT EXISTS bookings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  timeslot_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  status ENUM('pending','reserved','paid','canceled','expired') DEFAULT 'pending',
  pix_txid VARCHAR(64),
  pix_qr TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (timeslot_id) REFERENCES timeslots(id)
);

-- Sample data
INSERT INTO courts (name, active) VALUES ('Quadra 1', 1), ('Quadra 2', 1);

-- Create a default admin user: username 'admin', password 'admin123'
INSERT INTO admin_users (username, password_hash)
VALUES ('admin', '$2y$10$T6l6j5p9X3kqzIY2qE3CneC2kPp8r6F2Zg2zYtqVq7f9j4n7qv6n2')
ON DUPLICATE KEY UPDATE username=VALUES(username);


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

INSERT INTO settings (`key`, `value`) VALUES
('pix_enabled', '1'),
('asaas_env', 'sandbox'),
('asaas_api_key', '');