CREATE TABLE users_web (
  UW_ID INT AUTO_INCREMENT PRIMARY KEY,
  UW_BUSINESS INT NOT NULL,
  UW_BANNER INT NOT NULL,

  UW_EMAIL VARCHAR(255) NOT NULL,
  UW_PHONE VARCHAR(45),
  UW_PASSWORD_HASH VARCHAR(255) NOT NULL,

  UW_ROLE ENUM('CLIENT','ADMIN','SUPERADMIN') DEFAULT 'CLIENT',
  UW_STATUS ENUM('ACTIVE','INACTIVE','LOCKED','PENDING') DEFAULT 'PENDING',

  UW_MFA_ENABLED TINYINT(1) DEFAULT 1,

  UW_CREATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP,
  UW_UPDATED_AT DATETIME,

  UNIQUE KEY (UW_BUSINESS, UW_EMAIL),
  INDEX IDX_USERS_BUSINESS (UW_BUSINESS, UW_BANNER)
);

CREATE TABLE events_web (
  EW_ID INT AUTO_INCREMENT PRIMARY KEY,
  EW_BUSINESS INT NOT NULL,
  EW_BANNER INT NOT NULL,

  EW_NAME VARCHAR(255),
  EW_DESCRIPTION TEXT,
  EW_EVENT_TYPE ENUM('TRAINING','GAME','OTHER'),

  EW_START_DATETIME DATETIME,
  EW_END_DATETIME DATETIME,

  EW_STATUS ENUM('DRAFT','PUBLISHED','CANCELLED','COMPLETED'),

  EW_CREATED_BY INT,
  EW_CREATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP,

  INDEX IDX_EVENTS_BUSINESS (EW_BUSINESS, EW_BANNER),
  INDEX IDX_EVENTS_DATE (EW_START_DATETIME)
);

CREATE TABLE event_slots_web (
  ESW_ID INT AUTO_INCREMENT PRIMARY KEY,
  ESW_BUSINESS INT NOT NULL,
  ESW_BANNER INT NOT NULL,

  ESW_EVENT_ID INT NOT NULL,

  ESW_CODE VARCHAR(45),
  ESW_NAME VARCHAR(255),

  ESW_PRICE DECIMAL(10,2),
  ESW_MAX_QTY INT,
  ESW_RESERVED_QTY INT DEFAULT 0,

  ESW_IS_PRIVATE TINYINT(1) DEFAULT 0,
  ESW_STATUS ENUM('AVAILABLE','FULL','LOCKED','CANCELLED'),

  INDEX IDX_SLOT_BUSINESS (ESW_BUSINESS, ESW_BANNER),
  INDEX IDX_SLOT_EVENT (ESW_EVENT_ID)
);

CREATE TABLE slot_invitations_web (
  SIW_ID INT AUTO_INCREMENT PRIMARY KEY,
  SIW_BUSINESS INT NOT NULL,
  SIW_BANNER INT NOT NULL,

  SIW_SLOT_ID INT,
  SIW_USER_ID INT,
  SIW_EMAIL VARCHAR(255),

  SIW_TOKEN VARCHAR(128),
  SIW_STATUS ENUM('PENDING','SENT','ACCEPTED','EXPIRED'),

  SIW_EXPIRES_AT DATETIME,

  INDEX IDX_INV_BUSINESS (SIW_BUSINESS, SIW_BANNER)
);

CREATE TABLE reservations_web (
  RW_ID INT AUTO_INCREMENT PRIMARY KEY,
  RW_BUSINESS INT NOT NULL,
  RW_BANNER INT NOT NULL,

  RW_USER_ID INT,

  RW_STATUS ENUM('PENDING_PAYMENT','PAID','CANCELLED'),

  RW_TOTAL_AMOUNT DECIMAL(10,2),

  RW_CREATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP,

  INDEX IDX_RES_BUSINESS (RW_BUSINESS, RW_BANNER)
);

ALTER TABLE reservations_web
ADD RW_EXPIRES_AT DATETIME DEFAULT NULL;

ALTER TABLE reservations_web
MODIFY RW_STATUS ENUM(
  'PENDING_PAYMENT',
  'PAID',
  'CANCELLED',
  'REFUNDED',
  'EXPIRED'
) NOT NULL DEFAULT 'PENDING_PAYMENT';


CREATE TABLE reservation_lines_web (
  RLW_ID INT AUTO_INCREMENT PRIMARY KEY,
  RLW_BUSINESS INT NOT NULL,
  RLW_BANNER INT NOT NULL,

  RLW_RESERVATION_ID INT,
  RLW_SLOT_ID INT,

  RLW_QTY INT,
  RLW_UNIT_PRICE DECIMAL(10,2),
  RLW_TOTAL_PRICE DECIMAL(10,2),

  INDEX IDX_RL_BUSINESS (RLW_BUSINESS, RLW_BANNER)
);

CREATE TABLE payments_web (
  PW_ID INT AUTO_INCREMENT PRIMARY KEY,
  PW_BUSINESS INT NOT NULL,
  PW_BANNER INT NOT NULL,

  PW_RESERVATION_ID INT,

  PW_PROVIDER ENUM('PAYPAL'),
  PW_PROVIDER_ORDER_ID VARCHAR(255),

  PW_AMOUNT DECIMAL(10,2),
  PW_STATUS ENUM('CREATED','APPROVED','CAPTURED','FAILED'),

  PW_CREATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP,

  INDEX IDX_PAY_BUSINESS (PW_BUSINESS, PW_BANNER)
);

CREATE TABLE auth_tokens_web (
  ATW_ID INT AUTO_INCREMENT PRIMARY KEY,
  ATW_BUSINESS INT NOT NULL,
  ATW_BANNER INT NOT NULL,
  ATW_USER_ID INT NOT NULL,
  ATW_TOKEN_HASH CHAR(64) NOT NULL,
  ATW_DEVICE_NAME VARCHAR(255) DEFAULT NULL,
  ATW_IP VARCHAR(45) DEFAULT NULL,
  ATW_EXPIRES_AT DATETIME NOT NULL,
  ATW_REVOKED TINYINT(1) NOT NULL DEFAULT 0,
  ATW_CREATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ATW_REVOKED_AT DATETIME DEFAULT NULL,
  INDEX IDX_ATW_USER (ATW_BUSINESS, ATW_BANNER, ATW_USER_ID),
  UNIQUE KEY UK_ATW_TOKEN_HASH (ATW_TOKEN_HASH)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE mfa_challenges_web (
  MCW_ID INT AUTO_INCREMENT PRIMARY KEY,
  MCW_BUSINESS INT NOT NULL,
  MCW_BANNER INT NOT NULL,
  MCW_USER_ID INT NOT NULL,
  MCW_CODE_HASH CHAR(64) NOT NULL,
  MCW_CHANNEL ENUM('SMS','EMAIL') NOT NULL DEFAULT 'SMS',
  MCW_DESTINATION VARCHAR(255) NOT NULL,
  MCW_EXPIRES_AT DATETIME NOT NULL,
  MCW_USED TINYINT(1) NOT NULL DEFAULT 0,
  MCW_CREATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  MCW_USED_AT DATETIME DEFAULT NULL,
  INDEX IDX_MCW_USER (MCW_BUSINESS, MCW_BANNER, MCW_USER_ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE password_resets_web (
  PRW_ID INT AUTO_INCREMENT PRIMARY KEY,
  PRW_BUSINESS INT NOT NULL,
  PRW_BANNER INT NOT NULL,
  PRW_USER_ID INT NOT NULL,
  PRW_TOKEN_HASH CHAR(64) NOT NULL,
  PRW_EXPIRES_AT DATETIME NOT NULL,
  PRW_USED TINYINT(1) NOT NULL DEFAULT 0,
  PRW_CREATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRW_USED_AT DATETIME DEFAULT NULL,
  UNIQUE KEY UK_PRW_TOKEN_HASH (PRW_TOKEN_HASH)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE notification_logs_web (
  NLW_ID INT AUTO_INCREMENT PRIMARY KEY,
  NLW_BUSINESS INT NOT NULL,
  NLW_BANNER INT NOT NULL,
  NLW_USER_ID INT DEFAULT NULL,
  NLW_RESERVATION_ID INT DEFAULT NULL,
  NLW_SLOT_ID INT DEFAULT NULL,

  NLW_CHANNEL ENUM('EMAIL','SMS') NOT NULL,
  NLW_TO_ADDRESS VARCHAR(255) NOT NULL,
  NLW_SUBJECT VARCHAR(255) DEFAULT NULL,
  NLW_MESSAGE TEXT NOT NULL,

  NLW_STATUS ENUM('PENDING','SENT','FAILED') NOT NULL DEFAULT 'PENDING',
  NLW_ERROR TEXT DEFAULT NULL,

  NLW_CREATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  NLW_SENT_AT DATETIME DEFAULT NULL,

  INDEX IDX_NLW_BUSINESS (NLW_BUSINESS, NLW_BANNER),
  INDEX IDX_NLW_USER (NLW_USER_ID),
  INDEX IDX_NLW_RESERVATION (NLW_RESERVATION_ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;