-- ============================================================
-- KickConnect Database Schema + Seed Data
-- Compatible with: MySQL 5.7+ / MariaDB 10.3+
-- Import via: phpMyAdmin → Import → paste this SQL
-- ============================================================

-- -------------------------------------------------------
-- TABLE: users
-- Stores authenticated KICK users (OAuth login)
-- -------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `id`              VARCHAR(36)   NOT NULL PRIMARY KEY,
  `kick_user_id`    VARCHAR(64)   NOT NULL UNIQUE,
  `username`        VARCHAR(100)  NOT NULL,
  `display_name`    VARCHAR(100)  NOT NULL,
  `channel_slug`    VARCHAR(100)  NOT NULL,
  `avatar_url`      TEXT,
  `account_tier`    VARCHAR(50)   DEFAULT 'Free',
  `followers_count` INT           DEFAULT 0,
  `is_live`         TINYINT(1)    DEFAULT 0,
  `stream_title`    VARCHAR(255),
  `created_at`      DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------------------
-- TABLE: api_usage_logs
-- Tracks daily API call counts per application
-- -------------------------------------------------------
CREATE TABLE IF NOT EXISTS `api_usage_logs` (
  `id`             VARCHAR(36)  NOT NULL PRIMARY KEY,
  `app_id`         VARCHAR(36)  NOT NULL,
  `user_id`        VARCHAR(36)  NOT NULL,
  `endpoint`       VARCHAR(255),
  `method`         VARCHAR(10)  DEFAULT 'GET',
  `status_code`    SMALLINT,
  `response_ms`    INT,
  `called_at`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`app_id`)  REFERENCES `applications`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------------------
-- TABLE: auth_events
-- Auth event feed (token issued, refreshed, revoked, etc.)
-- -------------------------------------------------------
CREATE TABLE IF NOT EXISTS `auth_events` (
  `id`         VARCHAR(36)  NOT NULL PRIMARY KEY,
  `user_id`    VARCHAR(36)  NOT NULL,
  `app_id`     VARCHAR(36),
  `event_type` ENUM('token_issued','token_refreshed','token_expired','token_revoked','login','logout') NOT NULL,
  `ip_address` VARCHAR(45),
  `user_agent` TEXT,
  `metadata`   JSON,
  `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`app_id`)  REFERENCES `applications`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ============================================================
-- SEED DATA
-- ============================================================

INSERT INTO `users` (`id`, `kick_user_id`, `username`, `display_name`, `channel_slug`, `avatar_url`, `account_tier`, `followers_count`, `is_live`, `stream_title`, `created_at`) VALUES
('usr-001', 'kick_usr_7f4a2d91', 'streamdev_kick', 'StreamDev', 'streamdev', 'https://i.pravatar.cc/150?img=12', 'Verified Partner', 48320, 1, 'Building KICK bots live 🚀', '2026-05-14 09:22:00'),
('usr-002', 'kick_usr_3b8c1e44', 'coderstreamer', 'CoderStreamer', 'coderstreamer', 'https://i.pravatar.cc/150?img=5',  'Affiliate', 12500, 0, NULL, '2026-04-20 15:10:00');

INSERT INTO `auth_events` (`id`, `user_id`, `app_id`, `event_type`, `ip_address`, `created_at`) VALUES
('evt-001', 'usr-001', 'app-001', 'login',           '192.168.1.10', '2026-06-01 09:00:00'),
('evt-002', 'usr-001', 'app-001', 'token_issued',    '192.168.1.10', '2026-06-01 09:00:05'),
('evt-003', 'usr-001', 'app-002', 'token_refreshed', '192.168.1.10', '2026-06-01 11:30:00'),
('evt-004', 'usr-001', 'app-003', 'token_expired',   '192.168.1.10', '2026-05-29 08:15:44'),
('evt-005', 'usr-002', NULL,      'login',           '10.0.0.55',    '2026-04-20 15:10:00');
