-- ============================================================
-- 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: oauth_sessions
-- Stores OAuth 2.1 tokens per user
-- -------------------------------------------------------
CREATE TABLE IF NOT EXISTS `oauth_sessions` (
  `id`                VARCHAR(36)   NOT NULL PRIMARY KEY,
  `user_id`           VARCHAR(36)   NOT NULL,
  `access_token`      TEXT          NOT NULL,
  `refresh_token`     TEXT,
  `token_type`        VARCHAR(20)   DEFAULT 'Bearer',
  `expires_at`        DATETIME      NOT NULL,
  `scopes`            TEXT,
  `connected_at`      DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_refreshed_at` DATETIME,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------------------
-- TABLE: applications
-- Stores registered KICK OAuth applications
-- -------------------------------------------------------
CREATE TABLE IF NOT EXISTS `applications` (
  `id`                  VARCHAR(36)   NOT NULL PRIMARY KEY,
  `user_id`             VARCHAR(36)   NOT NULL,
  `app_name`            VARCHAR(150)  NOT NULL,
  `description`         TEXT,
  `client_id`           VARCHAR(100)  NOT NULL UNIQUE,
  `client_secret_hash`  VARCHAR(255)  NOT NULL,
  `redirect_url`        TEXT          NOT NULL,
  `scopes`              TEXT,
  `pkce_enabled`        TINYINT(1)    DEFAULT 1,
  `token_status`        ENUM('valid','expiring','expired','revoked') DEFAULT 'valid',
  `api_calls_today`     INT           DEFAULT 0,
  `last_used_at`        DATETIME,
  `created_at`          DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`          DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) 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 `oauth_sessions` (`id`, `user_id`, `access_token`, `refresh_token`, `token_type`, `expires_at`, `scopes`, `connected_at`) VALUES
('sess-001', 'usr-001', 'eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9.access_token_placeholder', 'eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9.refresh_token_placeholder', 'Bearer', '2026-06-01 15:02:44', 'user:read,channel:read,chat:write,events:subscribe', '2026-05-14 09:22:00'),
('sess-002', 'usr-002', 'eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9.access_token_placeholder2', 'eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9.refresh_token_placeholder2', 'Bearer', '2026-06-01 16:00:00', 'user:read,channel:read', '2026-04-20 15:10:00');

INSERT INTO `applications` (`id`, `user_id`, `app_name`, `description`, `client_id`, `client_secret_hash`, `redirect_url`, `scopes`, `pkce_enabled`, `token_status`, `api_calls_today`, `last_used_at`, `created_at`) VALUES
('app-001', 'usr-001', 'StreamBot v2',    'Automated chat bot for stream interactions and commands',    'kick_cid_4f8a2e91b3c7d5e2', 'hashed_secret_001', 'https://streambot.myapp.io/auth/callback',  'user:read,channel:read,chat:write,events:subscribe',                 1, 'valid',    1847, '2026-06-01 13:58:12', '2026-04-12 10:00:00'),
('app-002', 'usr-001', 'ChatOverlay Pro', 'Stream overlay showing live chat with animations',           'kick_cid_7c1d3e5f8a2b4c6d', 'hashed_secret_002', 'https://overlay.chatpro.dev/kick/callback', 'user:read,channel:read,chat:write,clips:create',                     1, 'expiring',  412, '2026-06-01 13:41:05', '2026-03-28 14:30:00'),
('app-003', 'usr-001', 'AlertBot',        'Follower and subscription alert system with custom sounds', 'kick_cid_2e4f6a8b0c2d4e6f', 'hashed_secret_003', 'https://alerts.devstudio.io/oauth/kick',    'user:read,channel:read,events:subscribe,channel:subscriptions:read', 0, 'expired',     0, '2026-05-29 08:15:44', '2026-02-14 09:00: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');
