-- NightMap · esquema MariaDB (importar via phpMyAdmin no cPanel)
SET NAMES utf8mb4;

CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('visitor','business','staff') NOT NULL DEFAULT 'visitor',
  venue_id INT UNSIGNED NULL,               -- só para role=staff
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE venues (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  owner_id INT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  type ENUM('discoteca','bar') NOT NULL DEFAULT 'bar',
  description TEXT NULL,
  services TEXT NULL,
  address VARCHAR(255) NULL,
  city VARCHAR(100) NULL,
  lat DECIMAL(9,6) NULL,
  lng DECIMAL(9,6) NULL,
  photo VARCHAR(255) NULL,
  price_level TINYINT UNSIGNED NOT NULL DEFAULT 2,          -- 1..4 (€ a €€€€)
  entry_mode ENUM('free','ticket','min_consumption') NOT NULL DEFAULT 'free',
  ticket_price DECIMAL(8,2) NULL,
  min_consumption DECIMAL(8,2) NULL,
  boost_level TINYINT UNSIGNED NOT NULL DEFAULT 0,          -- 0..3
  boost_until DATETIME NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_geo (lat, lng),
  KEY idx_owner (owner_id),
  CONSTRAINT fk_venue_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE genres (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(60) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE venue_genres (
  venue_id INT UNSIGNED NOT NULL,
  genre_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (venue_id, genre_id),
  CONSTRAINT fk_vg_venue FOREIGN KEY (venue_id) REFERENCES venues(id) ON DELETE CASCADE,
  CONSTRAINT fk_vg_genre FOREIGN KEY (genre_id) REFERENCES genres(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE events (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  venue_id INT UNSIGNED NOT NULL,
  title VARCHAR(150) NOT NULL,
  description TEXT NULL,
  starts_at DATETIME NOT NULL,
  genre_id INT UNSIGNED NULL,
  ticket_price DECIMAL(8,2) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_event_venue (venue_id, starts_at),
  CONSTRAINT fk_event_venue FOREIGN KEY (venue_id) REFERENCES venues(id) ON DELETE CASCADE,
  CONSTRAINT fk_event_genre FOREIGN KEY (genre_id) REFERENCES genres(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE promotions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  venue_id INT UNSIGNED NOT NULL,
  title VARCHAR(150) NOT NULL,
  discount_percent TINYINT UNSIGNED NOT NULL,
  valid_from DATE NULL,
  valid_until DATE NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_promo_venue (venue_id),
  CONSTRAINT fk_promo_venue FOREIGN KEY (venue_id) REFERENCES venues(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE coupons (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  promotion_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  code CHAR(8) NOT NULL UNIQUE,
  status ENUM('issued','used') NOT NULL DEFAULT 'issued',
  issued_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  used_at DATETIME NULL,
  validated_by INT UNSIGNED NULL,
  UNIQUE KEY uq_user_promo (promotion_id, user_id),
  CONSTRAINT fk_coupon_promo FOREIGN KEY (promotion_id) REFERENCES promotions(id) ON DELETE CASCADE,
  CONSTRAINT fk_coupon_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE payments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  venue_id INT UNSIGNED NOT NULL,
  stripe_session_id VARCHAR(255) NOT NULL UNIQUE,
  plan VARCHAR(20) NOT NULL,
  amount_cents INT UNSIGNED NOT NULL,
  status ENUM('pending','paid','failed') NOT NULL DEFAULT 'pending',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  paid_at DATETIME NULL,
  CONSTRAINT fk_pay_venue FOREIGN KEY (venue_id) REFERENCES venues(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO genres (name) VALUES
 ('Country'),('Sertanejo'),('Pop'),('Rock'),('Hip-Hop'),('Reggaeton'),('Eletrónica'),
 ('House'),('Techno'),('Funk'),('Kizomba'),('Latina'),('Fado'),('Karaoke'),
 ('Anos 80/90'),('Comercial'),('Jazz'),('Música ao vivo');