-- =====================================================================
-- Digitális menütábla — alapséma (MySQL 8 / MariaDB 10.4+)
--
-- Megjegyzések:
--  * Az azonosítók CHAR(36) UUID-k, amiket az alkalmazás generál
--    (crypto.randomUUID()) — így nem függünk a szerver UUID() függvényétől.
--  * A frissítési időbélyeget az oszlop ON UPDATE záradéka tartja karban,
--    nem trigger.
--  * A logó adat-URI-ként, közvetlenül az adatbázisban van. Megosztott
--    tárhelyen ez megbízhatóbb, mint fájlt írni, és a TV-k offline
--    gyorsítótárába is automatikusan bekerül.
-- =====================================================================

CREATE TABLE IF NOT EXISTS users (
  id            CHAR(36)     NOT NULL PRIMARY KEY,
  email         VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  display_name  VARCHAR(120) NULL,
  created_at    DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  UNIQUE KEY users_email_uq (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS themes (
  id               CHAR(36)     NOT NULL PRIMARY KEY,
  name             VARCHAR(120) NOT NULL,
  template_type    ENUM('classic','modern','bold','split') NOT NULL DEFAULT 'classic',
  primary_color    CHAR(7)      NOT NULL DEFAULT '#C8102E',
  background_color CHAR(7)      NOT NULL DEFAULT '#0E0E10',
  text_color       CHAR(7)      NOT NULL DEFAULT '#F5F3EF',
  font             ENUM('inter','playfair','lora','oswald','bebas','nunito') NOT NULL DEFAULT 'inter',
  logo_url         MEDIUMTEXT   NULL,
  footer_text      VARCHAR(200) NULL,
  created_at       DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at       DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS menus (
  id          CHAR(36)     NOT NULL PRIMARY KEY,
  name        VARCHAR(120) NOT NULL,
  currency    CHAR(3)      NOT NULL DEFAULT 'HUF',
  is_archived TINYINT(1)   NOT NULL DEFAULT 0,
  created_at  DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at  DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS menu_categories (
  id         CHAR(36)     NOT NULL PRIMARY KEY,
  menu_id    CHAR(36)     NOT NULL,
  name       VARCHAR(120) NOT NULL,
  sort_order INT          NOT NULL DEFAULT 0,
  created_at DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  KEY menu_categories_menu_sort_idx (menu_id, sort_order),
  CONSTRAINT menu_categories_menu_fk FOREIGN KEY (menu_id)
    REFERENCES menus (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS menu_items (
  id           CHAR(36)      NOT NULL PRIMARY KEY,
  category_id  CHAR(36)      NOT NULL,
  name         VARCHAR(160)  NOT NULL,
  description  VARCHAR(400)  NULL,
  price        DECIMAL(10,2) NULL,
  sort_order   INT           NOT NULL DEFAULT 0,
  is_available TINYINT(1)    NOT NULL DEFAULT 1,
  created_at   DATETIME(3)   NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at   DATETIME(3)   NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  KEY menu_items_category_sort_idx (category_id, sort_order),
  CONSTRAINT menu_items_category_fk FOREIGN KEY (category_id)
    REFERENCES menu_categories (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Egy fizikai TV.
--   token:     tartós azonosító, a TV localStorage-ban tárolja
--   device_id: a TV böngészője által generált egyedi azonosító
CREATE TABLE IF NOT EXISTS screens (
  id                CHAR(36)     NOT NULL PRIMARY KEY,
  name              VARCHAR(120) NOT NULL,
  token             CHAR(43)     NULL,
  device_id         VARCHAR(64)  NULL,
  paired_at         DATETIME(3)  NULL,
  last_seen_at      DATETIME(3)  NULL,
  assigned_menu_id  CHAR(36)     NULL,
  assigned_theme_id CHAR(36)     NULL,
  created_at        DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at        DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  UNIQUE KEY screens_token_uq (token),
  UNIQUE KEY screens_device_uq (device_id),
  CONSTRAINT screens_menu_fk FOREIGN KEY (assigned_menu_id)
    REFERENCES menus (id) ON DELETE SET NULL,
  CONSTRAINT screens_theme_fk FOREIGN KEY (assigned_theme_id)
    REFERENCES themes (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Párosítás alatt álló, még ismeretlen TV.
CREATE TABLE IF NOT EXISTS pairing_requests (
  id         CHAR(36)     NOT NULL PRIMARY KEY,
  code       CHAR(6)      NOT NULL,
  device_id  VARCHAR(64)  NOT NULL,
  user_agent VARCHAR(400) NULL,
  expires_at DATETIME(3)  NOT NULL,
  claimed_at DATETIME(3)  NULL,
  screen_id  CHAR(36)     NULL,
  created_at DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  UNIQUE KEY pairing_requests_code_uq (code),
  KEY pairing_requests_device_idx (device_id),
  KEY pairing_requests_expires_idx (expires_at),
  CONSTRAINT pairing_requests_screen_fk FOREIGN KEY (screen_id)
    REFERENCES screens (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;