-- =====================================================================
--  SIGNAGE TV — Schema (MySQL 8.0+ / MariaDB 10.4+)
--  Charset utf8mb4 / collation utf8mb4_unicode_ci / engine InnoDB
--
--  Convenção: TODA tabela de domínio carrega tenant_id.
--  O isolamento entre clientes é garantido na camada de modelo E aqui,
--  via FK ON DELETE CASCADE a partir de tenants.
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- Clientes (tenants)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS tenants (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name            VARCHAR(150)    NOT NULL,
  slug            VARCHAR(80)     NOT NULL,
  document        VARCHAR(32)     NULL COMMENT 'CNPJ/CPF',
  contact_name    VARCHAR(150)    NULL,
  contact_email   VARCHAR(190)    NULL,
  contact_phone   VARCHAR(32)     NULL,
  timezone        VARCHAR(64)     NOT NULL DEFAULT 'America/Sao_Paulo',
  max_devices     INT UNSIGNED    NOT NULL DEFAULT 10,
  storage_quota_mb INT UNSIGNED   NOT NULL DEFAULT 5120,
  status          ENUM('active','suspended','canceled') NOT NULL DEFAULT 'active',
  notes           TEXT            NULL,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at      DATETIME        NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_tenants_slug (slug),
  KEY ix_tenants_status (status, deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Usuários
--   tenant_id NULL  => superadmin (enxerga todos os clientes)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id       BIGINT UNSIGNED NULL,
  name            VARCHAR(150)    NOT NULL,
  email           VARCHAR(190)    NOT NULL,
  password_hash   VARCHAR(255)    NOT NULL,
  role            ENUM('superadmin','admin','operator','viewer') NOT NULL DEFAULT 'operator',
  status          ENUM('active','disabled') NOT NULL DEFAULT 'active',
  totp_secret     VARBINARY(255)  NULL COMMENT 'cifrado (AES-256-GCM)',
  totp_enabled    TINYINT(1)      NOT NULL DEFAULT 0,
  must_change_pwd TINYINT(1)      NOT NULL DEFAULT 0,
  failed_logins   SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  locked_until    DATETIME        NULL,
  last_login_at   DATETIME        NULL,
  last_login_ip   VARBINARY(16)   NULL,
  password_changed_at DATETIME    NULL,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at      DATETIME        NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_email (email),
  KEY ix_users_tenant (tenant_id, status),
  CONSTRAINT fk_users_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sessões persistidas: permitem revogação imediata ("encerrar sessões")
CREATE TABLE IF NOT EXISTS user_sessions (
  id              CHAR(64)        NOT NULL COMMENT 'sha256 do session id',
  user_id         BIGINT UNSIGNED NOT NULL,
  ip              VARBINARY(16)   NULL,
  user_agent_hash CHAR(64)        NULL,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_seen_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at      DATETIME        NOT NULL,
  revoked_at      DATETIME        NULL,
  PRIMARY KEY (id),
  KEY ix_sessions_user (user_id, revoked_at),
  KEY ix_sessions_expires (expires_at),
  CONSTRAINT fk_sessions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Rate limit de login (IP + e-mail)
CREATE TABLE IF NOT EXISTS login_attempts (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  identifier      VARCHAR(190)    NOT NULL COMMENT 'email ou ip:<addr>',
  ip              VARBINARY(16)   NULL,
  success         TINYINT(1)      NOT NULL DEFAULT 0,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY ix_attempts_lookup (identifier, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS password_resets (
  token_hash      CHAR(64)        NOT NULL,
  user_id         BIGINT UNSIGNED NOT NULL,
  expires_at      DATETIME        NOT NULL,
  used_at         DATETIME        NULL,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (token_hash),
  KEY ix_reset_user (user_id),
  CONSTRAINT fk_reset_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Dispositivos (TVs)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS devices (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id       BIGINT UNSIGNED NOT NULL,
  name            VARCHAR(150)    NOT NULL,
  location        VARCHAR(190)    NULL COMMENT 'Ex.: Recepção - Loja Centro',
  pairing_code    CHAR(6)         NULL COMMENT 'código exibido na TV; NULL após parear',
  pairing_expires_at DATETIME     NULL,
  token_hash      CHAR(64)        NULL COMMENT 'sha256 do token do device',
  token_issued_at DATETIME        NULL,
  brand           ENUM('lg_webos','samsung_tizen','android_tv','roku','fire_tv','browser','other') NOT NULL DEFAULT 'browser',
  model           VARCHAR(120)    NULL,
  orientation     ENUM('landscape','portrait','portrait_left','portrait_right') NOT NULL DEFAULT 'landscape',
  resolution      VARCHAR(20)     NULL COMMENT 'Ex.: 1920x1080',
  timezone        VARCHAR(64)     NOT NULL DEFAULT 'America/Sao_Paulo',
  volume          TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0-100',
  default_playlist_id BIGINT UNSIGNED NULL,
  status          ENUM('pending','active','blocked') NOT NULL DEFAULT 'pending',
  os_version      VARCHAR(80)     NULL,
  app_version     VARCHAR(40)     NULL,
  last_ip         VARBINARY(16)   NULL,
  last_seen_at    DATETIME        NULL,
  last_error      VARCHAR(500)    NULL,
  content_version CHAR(40)        NULL COMMENT 'hash da programação entregue',
  settings_json   JSON            NULL,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at      DATETIME        NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_devices_pairing (pairing_code),
  UNIQUE KEY uq_devices_token (token_hash),
  KEY ix_devices_tenant (tenant_id, status, deleted_at),
  KEY ix_devices_lastseen (last_seen_at),
  CONSTRAINT fk_devices_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS device_groups (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id       BIGINT UNSIGNED NOT NULL,
  name            VARCHAR(150)    NOT NULL,
  description     VARCHAR(255)    NULL,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at      DATETIME        NULL,
  PRIMARY KEY (id),
  KEY ix_groups_tenant (tenant_id, deleted_at),
  CONSTRAINT fk_groups_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS device_group_members (
  group_id        BIGINT UNSIGNED NOT NULL,
  device_id       BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (group_id, device_id),
  KEY ix_dgm_device (device_id),
  CONSTRAINT fk_dgm_group  FOREIGN KEY (group_id)  REFERENCES device_groups(id) ON DELETE CASCADE,
  CONSTRAINT fk_dgm_device FOREIGN KEY (device_id) REFERENCES devices(id)       ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Fila de comandos remotos (reload, screenshot, reboot, clear-cache...)
CREATE TABLE IF NOT EXISTS device_commands (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id       BIGINT UNSIGNED NOT NULL,
  device_id       BIGINT UNSIGNED NOT NULL,
  command         ENUM('reload','clear_cache','screenshot','reboot','identify','update_app') NOT NULL,
  payload_json    JSON            NULL,
  status          ENUM('queued','sent','done','failed','expired') NOT NULL DEFAULT 'queued',
  result_text     VARCHAR(500)    NULL,
  created_by      BIGINT UNSIGNED NULL,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  sent_at         DATETIME        NULL,
  completed_at    DATETIME        NULL,
  expires_at      DATETIME        NULL,
  PRIMARY KEY (id),
  KEY ix_cmd_device (device_id, status),
  CONSTRAINT fk_cmd_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_cmd_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Mídias / Conteúdos
--   Tudo que pode entrar numa playlist vive aqui (inclusive câmera e YouTube).
--   Detalhes de RTSP ficam na tabela-extensão stream_settings (1:1).
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS media (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id       BIGINT UNSIGNED NOT NULL,
  type            ENUM('image','video','audio','youtube','stream','web','text') NOT NULL,
  title           VARCHAR(190)    NOT NULL,
  description     VARCHAR(500)    NULL,
  -- arquivos locais
  file_path       VARCHAR(255)    NULL COMMENT 'relativo a storage/uploads',
  original_name   VARCHAR(255)    NULL,
  mime_type       VARCHAR(120)    NULL,
  file_size       BIGINT UNSIGNED NULL,
  checksum_sha256 CHAR(64)        NULL,
  width           INT UNSIGNED    NULL,
  height          INT UNSIGNED    NULL,
  -- fontes externas (youtube / web / hls)
  source_url      VARCHAR(1000)   NULL,
  external_id     VARCHAR(190)    NULL COMMENT 'videoId ou playlistId do YouTube',
  thumb_path      VARCHAR(255)    NULL,
  duration_sec    INT UNSIGNED    NOT NULL DEFAULT 10 COMMENT 'padrão de exibição',
  muted           TINYINT(1)      NOT NULL DEFAULT 1,
  valid_from      DATETIME        NULL COMMENT 'janela de veiculação da peça',
  valid_until     DATETIME        NULL,
  tags            VARCHAR(255)    NULL,
  meta_json       JSON            NULL,
  status          ENUM('processing','ready','error','archived') NOT NULL DEFAULT 'ready',
  created_by      BIGINT UNSIGNED NULL,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at      DATETIME        NULL,
  PRIMARY KEY (id),
  KEY ix_media_tenant (tenant_id, type, status, deleted_at),
  KEY ix_media_validity (valid_from, valid_until),
  CONSTRAINT fk_media_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Extensão 1:1 para câmeras RTSP. Credenciais SEMPRE cifradas (AES-256-GCM).
CREATE TABLE IF NOT EXISTS stream_settings (
  media_id        BIGINT UNSIGNED NOT NULL,
  rtsp_url_enc    VARBINARY(1024) NOT NULL COMMENT 'cifrado',
  username_enc    VARBINARY(512)  NULL     COMMENT 'cifrado',
  password_enc    VARBINARY(512)  NULL     COMMENT 'cifrado',
  transport       ENUM('tcp','udp') NOT NULL DEFAULT 'tcp',
  hls_url         VARCHAR(1000)   NULL COMMENT 'URL HLS entregue ao player',
  relay_mode      ENUM('external','mediamtx','ffmpeg') NOT NULL DEFAULT 'external',
  relay_name      VARCHAR(120)    NULL COMMENT 'path no MediaMTX',
  audio_enabled   TINYINT(1)      NOT NULL DEFAULT 0,
  last_check_at   DATETIME        NULL,
  last_check_ok   TINYINT(1)      NULL,
  PRIMARY KEY (media_id),
  CONSTRAINT fk_stream_media FOREIGN KEY (media_id) REFERENCES media(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Playlists
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS playlists (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id       BIGINT UNSIGNED NOT NULL,
  name            VARCHAR(150)    NOT NULL,
  description     VARCHAR(500)    NULL,
  play_mode       ENUM('sequential','shuffle') NOT NULL DEFAULT 'sequential',
  transition      ENUM('none','fade','slide') NOT NULL DEFAULT 'fade',
  status          ENUM('draft','active','archived') NOT NULL DEFAULT 'active',
  created_by      BIGINT UNSIGNED NULL,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at      DATETIME        NULL,
  PRIMARY KEY (id),
  KEY ix_playlists_tenant (tenant_id, status, deleted_at),
  CONSTRAINT fk_playlists_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS playlist_items (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  playlist_id     BIGINT UNSIGNED NOT NULL,
  media_id        BIGINT UNSIGNED NOT NULL,
  position        INT UNSIGNED    NOT NULL DEFAULT 0,
  duration_sec    INT UNSIGNED    NULL COMMENT 'sobrescreve media.duration_sec',
  muted           TINYINT(1)      NULL,
  enabled         TINYINT(1)      NOT NULL DEFAULT 1,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY ix_items_playlist (playlist_id, position),
  KEY ix_items_media (media_id),
  CONSTRAINT fk_items_playlist FOREIGN KEY (playlist_id) REFERENCES playlists(id) ON DELETE CASCADE,
  CONSTRAINT fk_items_media    FOREIGN KEY (media_id)    REFERENCES media(id)     ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Agendamentos
--   days_mask: bitmask — bit0=Dom, bit1=Seg ... bit6=Sáb  (127 = todos)
--   start_time > end_time  => janela atravessa a meia-noite
--   priority: maior vence em caso de sobreposição
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS schedules (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id       BIGINT UNSIGNED NOT NULL,
  name            VARCHAR(150)    NOT NULL,
  playlist_id     BIGINT UNSIGNED NOT NULL,
  priority        SMALLINT UNSIGNED NOT NULL DEFAULT 10,
  start_date      DATE            NULL,
  end_date        DATE            NULL,
  start_time      TIME            NOT NULL DEFAULT '00:00:00',
  end_time        TIME            NOT NULL DEFAULT '23:59:59',
  days_mask       TINYINT UNSIGNED NOT NULL DEFAULT 127,
  status          ENUM('active','paused') NOT NULL DEFAULT 'active',
  created_by      BIGINT UNSIGNED NULL,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at      DATETIME        NULL,
  PRIMARY KEY (id),
  KEY ix_sched_tenant (tenant_id, status, deleted_at),
  KEY ix_sched_window (start_date, end_date),
  CONSTRAINT fk_sched_tenant   FOREIGN KEY (tenant_id)   REFERENCES tenants(id)   ON DELETE CASCADE,
  CONSTRAINT fk_sched_playlist FOREIGN KEY (playlist_id) REFERENCES playlists(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS schedule_targets (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  schedule_id     BIGINT UNSIGNED NOT NULL,
  target_type     ENUM('device','group','all') NOT NULL,
  target_id       BIGINT UNSIGNED NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_target (schedule_id, target_type, target_id),
  CONSTRAINT fk_target_sched FOREIGN KEY (schedule_id) REFERENCES schedules(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Comprovação de veiculação (proof of play) — cresce rápido, ver docs
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS playback_logs (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id       BIGINT UNSIGNED NOT NULL,
  device_id       BIGINT UNSIGNED NOT NULL,
  media_id        BIGINT UNSIGNED NULL,
  playlist_id     BIGINT UNSIGNED NULL,
  schedule_id     BIGINT UNSIGNED NULL,
  played_at       DATETIME        NOT NULL,
  duration_sec    INT UNSIGNED    NOT NULL DEFAULT 0,
  outcome         ENUM('played','skipped','error') NOT NULL DEFAULT 'played',
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY ix_pb_tenant_date (tenant_id, played_at),
  KEY ix_pb_device_date (device_id, played_at),
  KEY ix_pb_media_date (media_id, played_at),
  CONSTRAINT fk_pb_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_pb_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS device_events (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id       BIGINT UNSIGNED NOT NULL,
  device_id       BIGINT UNSIGNED NOT NULL,
  level           ENUM('info','warning','error') NOT NULL DEFAULT 'info',
  code            VARCHAR(60)     NOT NULL,
  message         VARCHAR(1000)   NULL,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY ix_ev_device (device_id, created_at),
  KEY ix_ev_level (tenant_id, level, created_at),
  CONSTRAINT fk_ev_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_ev_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Auditoria — quem fez o quê, com valores antes/depois
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS audit_log (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id       BIGINT UNSIGNED NULL,
  user_id         BIGINT UNSIGNED NULL,
  user_email      VARCHAR(190)    NULL COMMENT 'desnormalizado: sobrevive à exclusão',
  action          VARCHAR(60)     NOT NULL COMMENT 'create|update|delete|login|logout|...',
  entity          VARCHAR(60)     NULL,
  entity_id       BIGINT UNSIGNED NULL,
  before_json     JSON            NULL,
  after_json      JSON            NULL,
  ip              VARBINARY(16)   NULL,
  user_agent      VARCHAR(255)    NULL,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY ix_audit_tenant (tenant_id, created_at),
  KEY ix_audit_entity (entity, entity_id),
  KEY ix_audit_user (user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS settings (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id       BIGINT UNSIGNED NULL COMMENT 'NULL = global',
  skey            VARCHAR(100)    NOT NULL,
  svalue          TEXT            NULL,
  updated_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_settings (tenant_id, skey),
  CONSTRAINT fk_settings_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE devices
  ADD CONSTRAINT fk_devices_playlist FOREIGN KEY (default_playlist_id)
  REFERENCES playlists(id) ON DELETE SET NULL;

SET FOREIGN_KEY_CHECKS = 1;