-- ============================================================
-- Unified Minds — Database Schema
-- Run once to set up all tables
-- ============================================================

CREATE DATABASE IF NOT EXISTS admin_ufm
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE admin_ufm;

-- ─── Schedule / Events ───────────────────────────────────────
CREATE TABLE IF NOT EXISTS events (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title       VARCHAR(255)  NOT NULL,
    description TEXT,
    event_type  ENUM('stream','show','collab','other') NOT NULL DEFAULT 'stream',
    start_at    DATETIME      NOT NULL,
    end_at      DATETIME,
    timezone    VARCHAR(64)   NOT NULL DEFAULT 'UTC',
    platform    VARCHAR(128),          -- e.g. "Twitch", "YouTube", "IRL"
    stream_url  VARCHAR(512),
    is_recurring TINYINT(1)   NOT NULL DEFAULT 0,
    recur_rule  VARCHAR(255),          -- optional iCal RRULE string
    created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ─── Announcements ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS announcements (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title       VARCHAR(255)  NOT NULL,
    body        TEXT          NOT NULL,
    is_pinned   TINYINT(1)    NOT NULL DEFAULT 0,
    published_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at  DATETIME,
    created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ─── Newsletter / Mailing list ───────────────────────────────
CREATE TABLE IF NOT EXISTS subscribers (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email       VARCHAR(255)  NOT NULL UNIQUE,
    name        VARCHAR(128),
    subscribed_at TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    confirmed   TINYINT(1)    NOT NULL DEFAULT 0,
    token       VARCHAR(128)           -- for email confirmation
) ENGINE=InnoDB;

-- ─── Page Meta (for SEO / dynamic pages) ────────────────────
CREATE TABLE IF NOT EXISTS page_meta (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    page_slug   VARCHAR(128)  NOT NULL UNIQUE,
    meta_title  VARCHAR(255),
    meta_desc   VARCHAR(512),
    og_image    VARCHAR(512),
    updated_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ─── Seed: default page meta ────────────────────────────────
INSERT IGNORE INTO page_meta (page_slug, meta_title, meta_desc) VALUES
('home',     'Unified Minds — Where VR Meets Music',    'Experience the fusion of virtual reality and music at Unified Minds.'),
('schedule', 'Schedule — Unified Minds',                'Upcoming streams, shows, and events from Unified Minds.'),
('community','Community — Unified Minds',               'Join the Unified Minds community on Discord, Patreon, and more.'),
('about',    'About — Unified Minds',                   'Learn about the vision behind Unified Minds.');

-- ─── Seed: sample events ─────────────────────────────────────
INSERT IGNORE INTO events (title, description, event_type, start_at, end_at, timezone, platform, stream_url) VALUES
('VR Rave Night',        'Full immersive VR DJ set — bring your headset!',   'stream', DATE_ADD(NOW(), INTERVAL 3 DAY),  DATE_ADD(DATE_ADD(NOW(), INTERVAL 3 DAY),  INTERVAL 3 HOUR), 'UTC', 'Twitch',  'https://twitch.tv/unifiedminds'),
('Synthwave Sunday',     'Chilling synthwave mix while exploring VR worlds',  'stream', DATE_ADD(NOW(), INTERVAL 7 DAY),  DATE_ADD(DATE_ADD(NOW(), INTERVAL 7 DAY),  INTERVAL 2 HOUR), 'UTC', 'YouTube', 'https://youtube.com/@unifiedminds'),
('Collab: Guest DJ Set', 'Special guest brings their virtual stage',          'collab', DATE_ADD(NOW(), INTERVAL 14 DAY), DATE_ADD(DATE_ADD(NOW(), INTERVAL 14 DAY), INTERVAL 2 HOUR), 'UTC', 'Twitch',  'https://twitch.tv/unifiedminds');<br />
<b>Warning</b>:  Constant UFM_ROOT already defined in <b>/var/www/vhosts/korvarix.com/ufm.korvarix.com/includes/helpers.php</b> on line <b>4</b><br />
<br />
<b>Fatal error</b>:  Uncaught Error: Call to undefined function getDB() in /var/www/vhosts/korvarix.com/ufm.korvarix.com/includes/helpers.php:61
Stack trace:
#0 /var/www/vhosts/korvarix.com/ufm.korvarix.com/public/index.php(13): getUpcomingEvents()
#1 {main}
  thrown in <b>/var/www/vhosts/korvarix.com/ufm.korvarix.com/includes/helpers.php</b> on line <b>61</b><br />