/* ===========================
   Fresh schema for Space Sniffer
   Tested with MariaDB 10.11+
   =========================== */

-- Change this if you want a different DB name
SET @DB := 'itahukamedia_spacer';

-- Create DB (no-op if it already exists)
CREATE DATABASE IF NOT EXISTS `itahukamedia_spacer`
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `itahukamedia_spacer`;

-- Clean drop in dependency order

DROP VIEW IF EXISTS `v_space_events_detailed`;
DROP VIEW IF EXISTS `v_space_current_detailed`;



-- ========== Core tables ==========

/* Spaces: one row per Space */
CREATE TABLE `spaces` (
  `space_id`     VARCHAR(64)  NOT NULL,
  `title`        VARCHAR(512) DEFAULT NULL,
  `state`        VARCHAR(32)  DEFAULT NULL,
  `url`          VARCHAR(1024) DEFAULT NULL,
  `last_seen_ts` BIGINT       NOT NULL DEFAULT 0,
  PRIMARY KEY (`space_id`),
  KEY `idx_spaces_last_seen` (`last_seen_ts`)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

/* Users: unique by user_id; screen_name is unique if present */
CREATE TABLE `users` (
  `user_id`     VARCHAR(64)  NOT NULL,
  `screen_name` VARCHAR(60)  DEFAULT NULL,
  `name`        VARCHAR(120) DEFAULT NULL,
  `avatar`      VARCHAR(600) DEFAULT NULL,
  `updated_at`  BIGINT       DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `uniq_users_screen_name` (`screen_name`),
  KEY `idx_users_updated` (`updated_at`)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

/* space_current: current live roster for a space */
CREATE TABLE `space_current` (
  `space_id`   VARCHAR(64)  NOT NULL,
  `user_id`    VARCHAR(64)  NOT NULL,
  `role`       VARCHAR(32)  DEFAULT NULL,
  `updated_at` BIGINT       DEFAULT NULL,
  PRIMARY KEY (`space_id`,`user_id`),
  KEY `idx_sc_user` (`user_id`),
  KEY `idx_sc_role` (`role`),
  CONSTRAINT `fk_sc_space` FOREIGN KEY (`space_id`)
    REFERENCES `spaces`(`space_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_sc_user` FOREIGN KEY (`user_id`)
    REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

/* space_events: immutable event log */
CREATE TABLE `space_events` (
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `space_id`    VARCHAR(64)  NOT NULL,
  `event_ts`    BIGINT       NOT NULL,             -- ms since epoch
  `type`        VARCHAR(32)  NOT NULL,             -- join/leave/role_change/...
  `user_id`     VARCHAR(64)  DEFAULT NULL,
  `from_role`   VARCHAR(32)  DEFAULT NULL,
  `to_role`     VARCHAR(32)  DEFAULT NULL,
  `screen_name` VARCHAR(60)  DEFAULT NULL,         -- snapshot at event time
  `name`        VARCHAR(120) DEFAULT NULL,         -- snapshot at event time
  `avatar`      VARCHAR(600) DEFAULT NULL,         -- snapshot at event time
  `details_json` LONGTEXT
      CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL
      CHECK (json_valid(`details_json`)),
  PRIMARY KEY (`id`),
  KEY `idx_se_space_ts` (`space_id`,`event_ts`),
  KEY `idx_se_user` (`user_id`),
  KEY `idx_se_type` (`type`),
  CONSTRAINT `fk_se_space` FOREIGN KEY (`space_id`)
    REFERENCES `spaces`(`space_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

/* spaces_blocklist: IDs to ignore/auto-reject in webhook */
CREATE TABLE `spaces_blocklist` (
  `space_id`   VARCHAR(64) NOT NULL,
  `note`       VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`space_id`),
  KEY `idx_block_created` (`created_at`)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- ========== Optional helper VIEWS ==========

/* Current roster with user profile fields */
CREATE OR REPLACE VIEW `v_space_current_detailed` AS
SELECT
  sc.space_id,
  sc.user_id,
  sc.role,
  sc.updated_at,
  u.screen_name,
  u.name,
  u.avatar
FROM space_current sc
JOIN users u ON u.user_id = sc.user_id;

/* Events with user fallbacks (event snapshot wins; fall back to users) */
CREATE OR REPLACE VIEW `v_space_events_detailed` AS
SELECT
  se.id,
  se.space_id,
  se.event_ts,
  se.type,
  se.user_id,
  se.from_role,
  se.to_role,
  COALESCE(se.screen_name, u.screen_name) AS screen_name,
  COALESCE(se.name,        u.name)        AS name,
  COALESCE(se.avatar,      u.avatar)      AS avatar
FROM space_events se
LEFT JOIN users u ON u.user_id = se.user_id;

-- ========== (Optional) quick grants example ==========
-- Replace 'db_user'@'db_host' and 'db_password' as needed, or skip if already set up.
/*
CREATE USER IF NOT EXISTS 'itahukamedia_spacer'@'localhost' IDENTIFIED BY 'REPLACE_ME';
GRANT SELECT, INSERT, UPDATE, DELETE
  ON `itahukamedia_spacer`.*
  TO 'itahukamedia_spacer'@'localhost';
FLUSH PRIVILEGES;
*/

-- ========== Sanity checks ==========
-- SHOW TABLES;
-- SHOW COLUMNS FROM spaces;
-- SHOW COLUMNS FROM users;
-- SHOW COLUMNS FROM space_current;
-- SHOW COLUMNS FROM space_events;
-- SHOW FULL TABLES WHERE TABLE_TYPE='VIEW';
