-- schema.sql (MariaDB 10.0.38)
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  role ENUM('admin','client') NOT NULL,
  name VARCHAR(120) NOT NULL,
  username VARCHAR(100) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  pdf_path VARCHAR(255) DEFAULT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS courts (
  id TINYINT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(1) NOT NULL UNIQUE,
  name VARCHAR(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
  id TINYINT PRIMARY KEY,
  price_per_hour INT NOT NULL DEFAULT 0,
  reservations_disabled TINYINT(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS closed_dates (
  id INT AUTO_INCREMENT PRIMARY KEY,
  closed_date DATE NOT NULL,
  note VARCHAR(120) DEFAULT NULL,
  UNIQUE KEY unique_closed_date (closed_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS reservations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  court_id TINYINT NOT NULL,
  start_datetime DATETIME NOT NULL,
  end_datetime DATETIME NOT NULL,
  hours TINYINT NOT NULL,
  discount_percent TINYINT NOT NULL DEFAULT 0,
  total_price INT NOT NULL DEFAULT 0,
  paid TINYINT(1) NOT NULL DEFAULT 0,
  canceled TINYINT(1) NOT NULL DEFAULT 0,
  canceled_at DATETIME DEFAULT NULL,
  method ENUM('stripe','transfer','none') NOT NULL DEFAULT 'none',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_resv_time (start_datetime, end_datetime),
  KEY idx_resv_court (court_id),
  CONSTRAINT fk_resv_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_resv_court FOREIGN KEY (court_id) REFERENCES courts(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- Rango de bloqueos por cancha / ambas, con recurrencia
CREATE TABLE IF NOT EXISTS closed_ranges (
  id INT AUTO_INCREMENT PRIMARY KEY,
  court_scope ENUM('A','B','both') NOT NULL DEFAULT 'both',
  base_date DATE NOT NULL,
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  recurrence ENUM('none','weekly4','weekly_infinite') NOT NULL DEFAULT 'none',
  note VARCHAR(120) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_scope_date (court_scope, base_date),
  KEY idx_recurrence (recurrence)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
