-- StreamLive schema — MySQL / MariaDB.
-- For SQLite users: use install.php instead (auto-detects driver).
--
-- Run:
--   mysql -u user -p dbname < schema.sql
--   OR
--   phpMyAdmin → your database → Import → upload this file
--   OR
--   Visit install.php once in a browser (easiest on shared hosting)

CREATE TABLE IF NOT EXISTS categories (
    id         INT          NOT NULL AUTO_INCREMENT PRIMARY KEY,
    slug       VARCHAR(64)  NOT NULL UNIQUE,
    name       VARCHAR(120) NOT NULL,
    sort_order INT          NOT NULL DEFAULT 0,
    enabled    TINYINT(1)   NOT NULL DEFAULT 1,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS countries (
    id         INT          NOT NULL AUTO_INCREMENT PRIMARY KEY,
    code       VARCHAR(8)   NOT NULL UNIQUE,
    name       VARCHAR(120) NOT NULL,
    flag_emoji VARCHAR(16)  NULL,
    enabled    TINYINT(1)   NOT NULL DEFAULT 1,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS channels (
    id            INT          NOT NULL AUTO_INCREMENT PRIMARY KEY,
    slug          VARCHAR(160) NOT NULL UNIQUE,
    name          VARCHAR(200) NOT NULL,
    description   TEXT         NULL,
    logo_url      VARCHAR(1024) NULL,
    stream_url    TEXT         NOT NULL,
    category_slug VARCHAR(64)  NULL,
    country_code  VARCHAR(8)   NULL,
    is_featured   TINYINT(1)   NOT NULL DEFAULT 0,
    enabled       TINYINT(1)   NOT NULL DEFAULT 1,
    sort_order    INT          NOT NULL DEFAULT 0,
    created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_channels_category (category_slug),
    INDEX idx_channels_country  (country_code),
    INDEX idx_channels_enabled  (enabled, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS banners (
    id          INT          NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title       VARCHAR(200) NOT NULL,
    subtitle    VARCHAR(400) NULL,
    image_url   VARCHAR(1024) NULL,
    link_url    VARCHAR(1024) NULL,
    cta_label   VARCHAR(80)  NULL,
    sort_order  INT          NOT NULL DEFAULT 0,
    enabled     TINYINT(1)   NOT NULL DEFAULT 1,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
    setting_key VARCHAR(64) NOT NULL PRIMARY KEY,
    value_json  MEDIUMTEXT  NOT NULL,
    updated_at  DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS playlists (
    id          INT          NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(200) NOT NULL,
    url         TEXT         NOT NULL,
    type        VARCHAR(16)  NOT NULL DEFAULT 'm3u',
    enabled     TINYINT(1)   NOT NULL DEFAULT 1,
    last_synced DATETIME     NULL,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS visitor_daily (
    visit_date   DATE      NOT NULL,
    visitor_hash CHAR(64)  NOT NULL,
    hit_count    INT       NOT NULL DEFAULT 1,
    last_seen    DATETIME  NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (visit_date, visitor_hash),
    INDEX idx_visitor_daily_date (visit_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS visitor_totals (
    visit_date      DATE NOT NULL PRIMARY KEY,
    unique_visitors INT  NOT NULL DEFAULT 0,
    total_hits      INT  NOT NULL DEFAULT 0,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS visitor_lifetime (
    id         INT NOT NULL PRIMARY KEY,
    total_hits INT NOT NULL DEFAULT 0,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO visitor_lifetime (id, total_hits) VALUES (1, 0);