-- =====================================================
-- 탑에듀프렙 LMS - Supabase SQL Schema
-- index.html / mypage.html / admin.html 기반
-- =====================================================

-- UUID 확장 활성화
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";


-- =====================================================
-- 1. 사용자 프로필 (Supabase Auth와 연동)
-- =====================================================
CREATE TABLE profiles (
  id            UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  name          TEXT NOT NULL,
  email         TEXT UNIQUE NOT NULL,
  avatar_url    TEXT,
  role          TEXT NOT NULL DEFAULT 'student' CHECK (role IN ('student', 'teacher', 'both', 'admin')),
  branch        TEXT CHECK (branch IN ('jamsil', 'centum', 'online')),
  kakao_id      TEXT,                          -- 카톡 상담 ID
  bio           TEXT,
  is_active     BOOLEAN DEFAULT TRUE,
  created_at    TIMESTAMPTZ DEFAULT NOW(),
  updated_at    TIMESTAMPTZ DEFAULT NOW()
);

-- 프로필 통계 (mypage.html 상단 stat 카드: 수강중, 내 강의, 내 자료, 포인트)
CREATE TABLE profile_stats (
  user_id           UUID PRIMARY KEY REFERENCES profiles(id) ON DELETE CASCADE,
  enrolled_count    INT DEFAULT 0,          -- 수강중 강의 수
  teaching_count    INT DEFAULT 0,          -- 내 강의 수
  material_count    INT DEFAULT 0,          -- 내 자료 수
  total_earnings    NUMERIC(12,2) DEFAULT 0 -- 누적 수익
);


-- =====================================================
-- 2. 강의 (courses)
-- =====================================================
CREATE TABLE courses (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  teacher_id    UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  title         TEXT NOT NULL,
  description   TEXT,
  category      TEXT NOT NULL CHECK (category IN ('sat','ap','ib','math','english','science','other')),
  subject_tags  TEXT[],                      -- ['SAT','수학'] 등 다중 태그
  thumb_emoji   TEXT DEFAULT '📚',
  thumb_color   TEXT DEFAULT '#FFE0E0',      -- 썸네일 배경색
  is_free       BOOLEAN DEFAULT FALSE,
  price         NUMERIC(10,2) DEFAULT 0,
  status        TEXT DEFAULT 'pending' CHECK (status IN ('pending','active','rejected','archived')),
  branch        TEXT CHECK (branch IN ('jamsil','centum','online','all')) DEFAULT 'all',
  view_count    INT DEFAULT 0,
  enroll_count  INT DEFAULT 0,
  created_at    TIMESTAMPTZ DEFAULT NOW(),
  updated_at    TIMESTAMPTZ DEFAULT NOW()
);

-- 강의 섹션 (챕터)
CREATE TABLE course_sections (
  id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  course_id   UUID NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
  title       TEXT NOT NULL,
  order_num   INT NOT NULL DEFAULT 0,
  created_at  TIMESTAMPTZ DEFAULT NOW()
);

-- 강의 자료/영상 (섹션별)
CREATE TABLE course_lessons (
  id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  section_id  UUID NOT NULL REFERENCES course_sections(id) ON DELETE CASCADE,
  course_id   UUID NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
  title       TEXT NOT NULL,
  type        TEXT CHECK (type IN ('video','pdf','image','ppt','link')) DEFAULT 'video',
  file_url    TEXT,
  duration    INT,                           -- 영상 길이 (초)
  order_num   INT DEFAULT 0,
  is_free     BOOLEAN DEFAULT FALSE,         -- 미리보기 무료 여부
  created_at  TIMESTAMPTZ DEFAULT NOW()
);


-- =====================================================
-- 3. 수강 신청 & 진도 (enrollments)
-- =====================================================
CREATE TABLE enrollments (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  student_id    UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  course_id     UUID NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
  progress      INT DEFAULT 0 CHECK (progress BETWEEN 0 AND 100), -- 진도율 %
  status        TEXT DEFAULT 'active' CHECK (status IN ('active','completed','cancelled')),
  paid_amount   NUMERIC(10,2) DEFAULT 0,
  enrolled_at   TIMESTAMPTZ DEFAULT NOW(),
  completed_at  TIMESTAMPTZ,
  UNIQUE(student_id, course_id)
);

-- 강의별 레슨 수강 기록
CREATE TABLE lesson_progress (
  id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  student_id  UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  lesson_id   UUID NOT NULL REFERENCES course_lessons(id) ON DELETE CASCADE,
  course_id   UUID NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
  is_done     BOOLEAN DEFAULT FALSE,
  watched_sec INT DEFAULT 0,                -- 시청 시간 (초)
  done_at     TIMESTAMPTZ,
  UNIQUE(student_id, lesson_id)
);


-- =====================================================
-- 4. 자료 공유 (materials) - mypage 업로드 탭
-- =====================================================
CREATE TABLE materials (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  uploader_id   UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  course_id     UUID REFERENCES courses(id) ON DELETE SET NULL, -- 강의에 묶인 자료 (선택)
  title         TEXT NOT NULL,
  description   TEXT,
  type          TEXT CHECK (type IN ('pdf','video','image','ppt','link')) DEFAULT 'pdf',
  file_url      TEXT,
  category      TEXT CHECK (category IN ('sat','ap','ib','math','english','science','other')),
  is_shared     BOOLEAN DEFAULT TRUE,        -- 전체 공유 여부
  is_free       BOOLEAN DEFAULT TRUE,
  price         NUMERIC(10,2) DEFAULT 0,
  download_count INT DEFAULT 0,
  created_at    TIMESTAMPTZ DEFAULT NOW(),
  updated_at    TIMESTAMPTZ DEFAULT NOW()
);


-- =====================================================
-- 5. 공유 다이어리 / 캘린더 이벤트 (mypage 다이어리 탭)
-- =====================================================
CREATE TABLE diary_events (
  id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id     UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  title       TEXT NOT NULL,
  description TEXT,
  event_date  DATE NOT NULL,
  event_time  TIME,
  color       TEXT DEFAULT '#FF6B6B',       -- 이벤트 색상 (UI 색상 그대로)
  category    TEXT CHECK (category IN ('study','exam','class','personal','group')) DEFAULT 'study',
  is_shared   BOOLEAN DEFAULT FALSE,        -- 공유 다이어리 여부
  course_id   UUID REFERENCES courses(id) ON DELETE SET NULL,
  created_at  TIMESTAMPTZ DEFAULT NOW()
);

-- 다이어리 노트 (일기 형식)
CREATE TABLE diary_notes (
  id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id     UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  title       TEXT,
  content     TEXT NOT NULL,
  is_shared   BOOLEAN DEFAULT FALSE,
  note_date   DATE DEFAULT CURRENT_DATE,
  created_at  TIMESTAMPTZ DEFAULT NOW(),
  updated_at  TIMESTAMPTZ DEFAULT NOW()
);


-- =====================================================
-- 6. 리뷰 / 후기 (testimonials)
-- =====================================================
CREATE TABLE reviews (
  id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  student_id  UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  course_id   UUID NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
  rating      INT NOT NULL CHECK (rating BETWEEN 1 AND 5),
  content     TEXT,
  is_reported BOOLEAN DEFAULT FALSE,
  created_at  TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(student_id, course_id)
);


-- =====================================================
-- 7. 수익 & 정산 (revenue) - admin.html 수익 탭
-- =====================================================
CREATE TABLE revenue_records (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  teacher_id    UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  student_id    UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  course_id     UUID NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
  enrollment_id UUID REFERENCES enrollments(id) ON DELETE SET NULL,
  gross_amount  NUMERIC(10,2) NOT NULL,      -- 총 결제금액
  fee_rate      NUMERIC(4,2) DEFAULT 0.10,  -- 수수료율 (10%)
  fee_amount    NUMERIC(10,2) NOT NULL,      -- 수수료 금액
  net_amount    NUMERIC(10,2) NOT NULL,      -- 강사 정산액
  status        TEXT DEFAULT 'pending' CHECK (status IN ('pending','settled','cancelled')),
  settled_at    TIMESTAMPTZ,
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

-- 정산 요청
CREATE TABLE settlement_requests (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  teacher_id      UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  request_amount  NUMERIC(10,2) NOT NULL,
  status          TEXT DEFAULT 'pending' CHECK (status IN ('pending','approved','rejected','paid')),
  bank_info       TEXT,                      -- 계좌 정보 (암호화 권장)
  admin_note      TEXT,
  requested_at    TIMESTAMPTZ DEFAULT NOW(),
  processed_at    TIMESTAMPTZ
);


-- =====================================================
-- 8. 공지사항 (notices) - admin.html 공지 탭
-- =====================================================
CREATE TABLE notices (
  id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  author_id   UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  title       TEXT NOT NULL,
  content     TEXT NOT NULL,
  is_pinned   BOOLEAN DEFAULT FALSE,
  target      TEXT DEFAULT 'all' CHECK (target IN ('all','student','teacher','admin')),
  created_at  TIMESTAMPTZ DEFAULT NOW(),
  updated_at  TIMESTAMPTZ DEFAULT NOW()
);


-- =====================================================
-- 9. 알림 (notifications) - admin 대시보드 알림 목록
-- =====================================================
CREATE TABLE notifications (
  id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id     UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  type        TEXT NOT NULL CHECK (type IN ('new_course','settlement','new_member','review_report','enroll','system')),
  title       TEXT NOT NULL,
  body        TEXT,
  is_read     BOOLEAN DEFAULT FALSE,
  link        TEXT,
  created_at  TIMESTAMPTZ DEFAULT NOW()
);


-- =====================================================
-- 10. 사이트 설정 (admin.html 설정 탭)
-- =====================================================
CREATE TABLE site_settings (
  key         TEXT PRIMARY KEY,
  value       TEXT,
  description TEXT,
  updated_at  TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO site_settings (key, value, description) VALUES
  ('site_name',       '탑에듀프렙 TopEduPrep LMS',  '사이트 이름'),
  ('fee_rate',        '0.10',                       '플랫폼 수수료율'),
  ('phone',           '1555-0982',                  '대표 전화'),
  ('kakao_id',        'minhokimm',                  '카카오톡 상담 ID'),
  ('phone_direct',    '010-6664-8209',              '직통 연락처'),
  ('branch_jamsil',   '서울 송파구',                 '잠실점 주소'),
  ('branch_centum',   '부산 해운대구',               '센텀점 주소'),
  ('notice_banner',   '🎉 무료 시범수업 신청 중!',    '상단 배너 문구');


-- =====================================================
-- FUNCTIONS & TRIGGERS
-- =====================================================

-- 새 유저 가입 시 profile + profile_stats 자동 생성
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO profiles (id, name, email)
  VALUES (NEW.id, COALESCE(NEW.raw_user_meta_data->>'name', '신규회원'), NEW.email);
  
  INSERT INTO profile_stats (user_id) VALUES (NEW.id);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION handle_new_user();


-- 수강 신청 시 enroll_count 증가 & profile_stats 업데이트
CREATE OR REPLACE FUNCTION handle_enrollment()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE courses SET enroll_count = enroll_count + 1 WHERE id = NEW.course_id;
  UPDATE profile_stats SET enrolled_count = enrolled_count + 1 WHERE user_id = NEW.student_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_enrollment_created
  AFTER INSERT ON enrollments
  FOR EACH ROW EXECUTE FUNCTION handle_enrollment();


-- 강의 업로드 시 teaching_count 증가
CREATE OR REPLACE FUNCTION handle_course_created()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE profile_stats SET teaching_count = teaching_count + 1 WHERE user_id = NEW.teacher_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_course_created
  AFTER INSERT ON courses
  FOR EACH ROW EXECUTE FUNCTION handle_course_created();


-- 자료 업로드 시 material_count 증가
CREATE OR REPLACE FUNCTION handle_material_created()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE profile_stats SET material_count = material_count + 1 WHERE user_id = NEW.uploader_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_material_created
  AFTER INSERT ON materials
  FOR EACH ROW EXECUTE FUNCTION handle_material_created();


-- 결제 시 revenue_records 자동 계산 및 강사 수익 반영
CREATE OR REPLACE FUNCTION handle_revenue()
RETURNS TRIGGER AS $$
DECLARE
  v_fee_rate NUMERIC := 0.10;
  v_fee      NUMERIC;
  v_net      NUMERIC;
  v_teacher  UUID;
BEGIN
  SELECT teacher_id INTO v_teacher FROM courses WHERE id = NEW.course_id;
  v_fee := NEW.paid_amount * v_fee_rate;
  v_net := NEW.paid_amount - v_fee;
  
  INSERT INTO revenue_records
    (teacher_id, student_id, course_id, enrollment_id, gross_amount, fee_rate, fee_amount, net_amount)
  VALUES
    (v_teacher, NEW.student_id, NEW.course_id, NEW.id, NEW.paid_amount, v_fee_rate, v_fee, v_net);
  
  UPDATE profile_stats SET total_earnings = total_earnings + v_net WHERE user_id = v_teacher;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_enrollment_paid
  AFTER INSERT ON enrollments
  FOR EACH ROW
  WHEN (NEW.paid_amount > 0)
  EXECUTE FUNCTION handle_revenue();


-- =====================================================
-- ROW LEVEL SECURITY (RLS)
-- =====================================================

ALTER TABLE profiles           ENABLE ROW LEVEL SECURITY;
ALTER TABLE profile_stats      ENABLE ROW LEVEL SECURITY;
ALTER TABLE courses            ENABLE ROW LEVEL SECURITY;
ALTER TABLE course_sections    ENABLE ROW LEVEL SECURITY;
ALTER TABLE course_lessons     ENABLE ROW LEVEL SECURITY;
ALTER TABLE enrollments        ENABLE ROW LEVEL SECURITY;
ALTER TABLE lesson_progress    ENABLE ROW LEVEL SECURITY;
ALTER TABLE materials          ENABLE ROW LEVEL SECURITY;
ALTER TABLE diary_events       ENABLE ROW LEVEL SECURITY;
ALTER TABLE diary_notes        ENABLE ROW LEVEL SECURITY;
ALTER TABLE reviews            ENABLE ROW LEVEL SECURITY;
ALTER TABLE revenue_records    ENABLE ROW LEVEL SECURITY;
ALTER TABLE settlement_requests ENABLE ROW LEVEL SECURITY;
ALTER TABLE notices            ENABLE ROW LEVEL SECURITY;
ALTER TABLE notifications      ENABLE ROW LEVEL SECURITY;

-- profiles: 본인만 수정, 전체 읽기 가능
CREATE POLICY "profiles_select_all"   ON profiles FOR SELECT USING (true);
CREATE POLICY "profiles_update_own"   ON profiles FOR UPDATE USING (auth.uid() = id);

-- profile_stats: 본인만 접근
CREATE POLICY "stats_own"             ON profile_stats FOR ALL USING (auth.uid() = user_id);

-- courses: 활성 강의는 전체 열람, 본인 강의 관리
CREATE POLICY "courses_select_active" ON courses FOR SELECT USING (status = 'active' OR teacher_id = auth.uid());
CREATE POLICY "courses_insert_own"    ON courses FOR INSERT WITH CHECK (teacher_id = auth.uid());
CREATE POLICY "courses_update_own"    ON courses FOR UPDATE USING (teacher_id = auth.uid());

-- course_sections & lessons: 수강자 또는 강사
CREATE POLICY "sections_select"       ON course_sections FOR SELECT USING (true);
CREATE POLICY "lessons_select"        ON course_lessons   FOR SELECT USING (true);
CREATE POLICY "lessons_insert_teacher" ON course_lessons  FOR INSERT
  WITH CHECK (EXISTS (SELECT 1 FROM courses WHERE id = course_id AND teacher_id = auth.uid()));

-- enrollments: 본인 수강 내역
CREATE POLICY "enrollments_own"       ON enrollments FOR ALL USING (student_id = auth.uid());

-- lesson_progress: 본인
CREATE POLICY "progress_own"          ON lesson_progress FOR ALL USING (student_id = auth.uid());

-- materials: 공유 자료 전체 열람, 본인 자료 관리
CREATE POLICY "materials_shared"      ON materials FOR SELECT USING (is_shared = true OR uploader_id = auth.uid());
CREATE POLICY "materials_insert_own"  ON materials FOR INSERT WITH CHECK (uploader_id = auth.uid());
CREATE POLICY "materials_update_own"  ON materials FOR UPDATE USING (uploader_id = auth.uid());

-- diary_events: 본인 또는 공유된 것
CREATE POLICY "diary_events_own"      ON diary_events FOR ALL USING (user_id = auth.uid());
CREATE POLICY "diary_events_shared"   ON diary_events FOR SELECT USING (is_shared = true);

-- diary_notes: 본인 또는 공유된 것
CREATE POLICY "diary_notes_own"       ON diary_notes FOR ALL USING (user_id = auth.uid());
CREATE POLICY "diary_notes_shared"    ON diary_notes FOR SELECT USING (is_shared = true);

-- reviews: 전체 열람, 본인만 작성/수정
CREATE POLICY "reviews_select_all"    ON reviews FOR SELECT USING (true);
CREATE POLICY "reviews_insert_own"    ON reviews FOR INSERT WITH CHECK (student_id = auth.uid());
CREATE POLICY "reviews_update_own"    ON reviews FOR UPDATE USING (student_id = auth.uid());

-- revenue: 강사 본인만
CREATE POLICY "revenue_own"           ON revenue_records FOR SELECT USING (teacher_id = auth.uid());

-- settlement: 본인만
CREATE POLICY "settlement_own"        ON settlement_requests FOR ALL USING (teacher_id = auth.uid());

-- notices: 전체 열람
CREATE POLICY "notices_select_all"    ON notices FOR SELECT USING (true);

-- notifications: 본인만
CREATE POLICY "notifications_own"     ON notifications FOR ALL USING (user_id = auth.uid());


-- =====================================================
-- INDEXES (성능 최적화)
-- =====================================================
CREATE INDEX idx_courses_teacher       ON courses(teacher_id);
CREATE INDEX idx_courses_category      ON courses(category);
CREATE INDEX idx_courses_status        ON courses(status);
CREATE INDEX idx_enrollments_student   ON enrollments(student_id);
CREATE INDEX idx_enrollments_course    ON enrollments(course_id);
CREATE INDEX idx_materials_uploader    ON materials(uploader_id);
CREATE INDEX idx_diary_events_user     ON diary_events(user_id, event_date);
CREATE INDEX idx_diary_events_shared   ON diary_events(is_shared, event_date);
CREATE INDEX idx_revenue_teacher       ON revenue_records(teacher_id);
CREATE INDEX idx_notifications_user    ON notifications(user_id, is_read);


-- =====================================================
-- 샘플 데이터 (index.html 강의 목록 기반)
-- =====================================================

-- 샘플 강의 (실제 사용 시 teacher_id를 실제 UUID로 교체)
-- INSERT INTO courses (teacher_id, title, category, subject_tags, is_free, price, status, thumb_emoji) VALUES
--   ('<teacher_uuid>', 'SAT Math 800점 완성반',           'sat',     ARRAY['SAT','수학'], false, 150000, 'active', '🧮'),
--   ('<teacher_uuid>', 'SAT Reading & Writing 기초',      'sat',     ARRAY['SAT','영어'], true,       0, 'active', '📖'),
--   ('<teacher_uuid>', 'AP Chemistry 5점 달성 전략',       'ap',      ARRAY['AP','과학'],  false, 120000, 'active', '⚗️'),
--   ('<teacher_uuid>', 'IB Math AA/AI HL 완전정복',        'ib',      ARRAY['IB','수학'],  false, 200000, 'active', '📐'),
--   ('<teacher_uuid>', 'AP Language & Composition 입문',   'ap',      ARRAY['AP','영어'],  true,       0, 'active', '🌍');
