-- ============================================
-- Supabase/PostgreSQL Setup untuk Chatbot LMS
-- Universitas Yatsi Madani
-- ============================================
--
-- Cara menggunakan:
-- 1. Buka Supabase Dashboard
-- 2. Pilih project > SQL Editor
-- 3. Jalankan seluruh isi file ini
-- 4. Copy connection string Supabase ke backend env:
--    DB_TYPE=supabase
--    DATABASE_URL=postgresql://postgres.[project-ref]:[password]@aws-[region].pooler.supabase.com:5432/postgres
--
-- Untuk backend Express yang long-running, pakai Direct Connection atau
-- Shared Pooler Session Mode. Hindari Transaction Pooler kecuali backend
-- Anda serverless dan library database sudah disesuaikan.
-- ============================================

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW."updatedAt" = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- ============================================
-- Table: users
-- ============================================
CREATE TABLE IF NOT EXISTS users (
  id BIGSERIAL PRIMARY KEY,
  nim VARCHAR(20) UNIQUE,
  email VARCHAR(255) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  "fullName" VARCHAR(255) NOT NULL,
  role VARCHAR(20) DEFAULT 'student'
    CHECK (role IN ('student', 'lecturer', 'admin', 'staff')),

  "academicInfo" JSONB,
  "lecturerInfo" JSONB,
  profile JSONB,
  "lmsIntegration" JSONB,
  "chatPreferences" JSONB,

  "isActive" BOOLEAN DEFAULT TRUE,
  "isVerified" BOOLEAN DEFAULT FALSE,
  "lastLogin" TIMESTAMPTZ,
  "loginCount" INTEGER DEFAULT 0,

  "createdAt" TIMESTAMPTZ DEFAULT NOW(),
  "updatedAt" TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_users_role ON users(role);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

DROP TRIGGER IF EXISTS trg_users_updated_at ON users;
CREATE TRIGGER trg_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

-- ============================================
-- Table: chat_sessions
-- ============================================
CREATE TABLE IF NOT EXISTS chat_sessions (
  id BIGSERIAL PRIMARY KEY,
  "userId" BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  "sessionId" VARCHAR(255) NOT NULL UNIQUE,

  context JSONB,
  status VARCHAR(20) DEFAULT 'active'
    CHECK (status IN ('active', 'paused', 'completed', 'escalated', 'abandoned')),
  "lmsData" JSONB,
  analytics JSONB,

  "startedAt" TIMESTAMPTZ DEFAULT NOW(),
  "endedAt" TIMESTAMPTZ,
  "lastActivityAt" TIMESTAMPTZ DEFAULT NOW(),
  "createdAt" TIMESTAMPTZ DEFAULT NOW(),
  "updatedAt" TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_chat_sessions_user_id ON chat_sessions("userId");
CREATE INDEX IF NOT EXISTS idx_chat_sessions_status ON chat_sessions(status);
CREATE INDEX IF NOT EXISTS idx_chat_sessions_started_at ON chat_sessions("startedAt");

DROP TRIGGER IF EXISTS trg_chat_sessions_updated_at ON chat_sessions;
CREATE TRIGGER trg_chat_sessions_updated_at
BEFORE UPDATE ON chat_sessions
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

-- ============================================
-- Table: messages
-- ============================================
CREATE TABLE IF NOT EXISTS messages (
  id BIGSERIAL PRIMARY KEY,
  "sessionId" BIGINT NOT NULL REFERENCES chat_sessions(id) ON DELETE CASCADE,
  role VARCHAR(20) NOT NULL
    CHECK (role IN ('user', 'assistant', 'system')),
  content TEXT NOT NULL,
  "messageType" VARCHAR(20) DEFAULT 'text'
    CHECK ("messageType" IN ('text', 'image', 'file', 'quick_reply', 'card')),

  metadata JSONB,
  attachments JSONB,
  timestamp TIMESTAMPTZ DEFAULT NOW(),
  "createdAt" TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_messages_session_id ON messages("sessionId");
CREATE INDEX IF NOT EXISTS idx_messages_timestamp ON messages(timestamp);

-- ============================================
-- Sample Data (Optional)
-- ============================================
INSERT INTO users (email, password, "fullName", role, "isActive", "isVerified")
VALUES
('admin@yatsi.ac.id', '$2a$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewY5GyYqJqJqJqJq', 'Admin User', 'admin', TRUE, TRUE)
ON CONFLICT (email) DO NOTHING;

INSERT INTO users (nim, email, password, "fullName", role, "isActive", "isVerified", "academicInfo")
VALUES
(
  '87654321',
  'student@yatsi.ac.id',
  '$2a$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewY5GyYqJqJqJqJq',
  'Student User',
  'student',
  TRUE,
  TRUE,
  jsonb_build_object(
    'faculty', 'Fakultas Teknik',
    'studyProgram', 'Teknik Informatika',
    'semester', 3,
    'academicYear', '2023/2024',
    'class', 'TI-21',
    'studentStatus', 'active'
  )
)
ON CONFLICT (email) DO NOTHING;

-- ============================================
-- Views (Optional - untuk reporting)
-- ============================================
CREATE OR REPLACE VIEW v_active_sessions AS
SELECT
  cs.id,
  cs."sessionId",
  u.email,
  u."fullName",
  cs.status,
  cs."startedAt",
  cs."lastActivityAt",
  (SELECT COUNT(*) FROM messages m WHERE m."sessionId" = cs.id) AS "messageCount"
FROM chat_sessions cs
JOIN users u ON cs."userId" = u.id
WHERE cs.status = 'active';

CREATE OR REPLACE VIEW v_user_stats AS
SELECT
  u.id,
  u.email,
  u."fullName",
  u.role,
  COUNT(DISTINCT cs.id) AS "totalSessions",
  COUNT(DISTINCT m.id) AS "totalMessages",
  MAX(cs."lastActivityAt") AS "lastActivity"
FROM users u
LEFT JOIN chat_sessions cs ON u.id = cs."userId"
LEFT JOIN messages m ON cs.id = m."sessionId"
GROUP BY u.id, u.email, u."fullName", u.role;