-- ==========================================================
-- NADIPLAY  ·  MySQL schema  ·  cPanel / phpMyAdmin
-- ==========================================================
-- This is the pristine schema.  If you're doing a fresh install
-- you can either:
--   (a) upload it via phpMyAdmin → Import, OR
--   (b) let the Python app create tables on first boot
--       (SQLAlchemy does `CREATE TABLE IF NOT EXISTS` at import time).
-- After schema creation, load `nadiplayer_data_dump.sql` to import your
-- existing users / devices / settings.
-- ==========================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS `users` (
  `id`              VARCHAR(64)  NOT NULL PRIMARY KEY,
  `username`        VARCHAR(64)  NOT NULL UNIQUE,
  `password_hash`   VARCHAR(255) NOT NULL,
  `role`            VARCHAR(16)  NOT NULL DEFAULT 'user',
  `created_at`      VARCHAR(40)  NOT NULL,
  `expiry_at`       VARCHAR(40)          DEFAULT NULL,
  `xtream_mode`     VARCHAR(16)  NOT NULL DEFAULT 'shared',
  `xtream_server`   VARCHAR(500) NOT NULL DEFAULT '',
  `xtream_username` VARCHAR(255) NOT NULL DEFAULT '',
  `xtream_password` VARCHAR(255) NOT NULL DEFAULT '',
  `live_server`     VARCHAR(500) NOT NULL DEFAULT '',
  `live_username`   VARCHAR(255) NOT NULL DEFAULT '',
  `live_password`   VARCHAR(255) NOT NULL DEFAULT '',
  `vod_server`      VARCHAR(500) NOT NULL DEFAULT '',
  `vod_username`    VARCHAR(255) NOT NULL DEFAULT '',
  `vod_password`    VARCHAR(255) NOT NULL DEFAULT '',
  `note`            TEXT         NOT NULL,
  INDEX `ix_users_role` (`role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `devices` (
  `device_id`    VARCHAR(32)  NOT NULL PRIMARY KEY,
  `mac_address`  VARCHAR(32)  NOT NULL,
  `pin`          VARCHAR(8)   NOT NULL UNIQUE,
  `paired`       TINYINT(1)   NOT NULL DEFAULT 0,
  `server_url`   VARCHAR(500)          DEFAULT NULL,
  `username`     VARCHAR(255)          DEFAULT NULL,
  `password`     VARCHAR(255)          DEFAULT NULL,
  `profile_name` VARCHAR(255)          DEFAULT NULL,
  `created_at`   VARCHAR(40)  NOT NULL,
  `paired_at`    VARCHAR(40)           DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `settings` (
  `id`   VARCHAR(32) NOT NULL PRIMARY KEY,
  `data` JSON        NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `status_checks` (
  `id`          VARCHAR(64)  NOT NULL PRIMARY KEY,
  `client_name` VARCHAR(255) NOT NULL,
  `timestamp`   VARCHAR(40)  NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------
-- World Cup Hub (Sports API cache). Optional — `routes/sports.php`
-- creates this table lazily on first request, so this block is only
-- needed if you prefer to provision schema up-front.
-- ----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sports_cache` (
  `cache_key`  VARCHAR(160) NOT NULL PRIMARY KEY,
  `payload`    LONGTEXT     NOT NULL,
  `fetched_at` INT          NOT NULL,
  `expires_at` INT          NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
