create extension if not exists pgcrypto;

create type user_role as enum ('student', 'instructor', 'admin');
create type course_status as enum ('draft', 'published', 'archived');
create type lesson_access_level as enum ('public', 'enrolled');
create type media_status as enum ('processing', 'ready', 'failed');
create type asset_kind as enum ('document', 'spreadsheet', 'archive', 'image', 'link');

create table users (
  id uuid primary key default gen_random_uuid(),
  email text not null unique,
  password_hash text not null,
  display_name text not null,
  role user_role not null default 'student',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table courses (
  id uuid primary key default gen_random_uuid(),
  slug text not null unique,
  title text not null,
  description text not null default '',
  category text not null,
  cover_image_url text,
  status course_status not null default 'draft',
  instructor_id uuid not null references users(id),
  published_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint courses_published_at_check check (
    (status = 'published' and published_at is not null) or status <> 'published'
  )
);

create table lessons (
  id uuid primary key default gen_random_uuid(),
  course_id uuid not null references courses(id) on delete cascade,
  title text not null,
  description text not null default '',
  position integer not null check (position > 0),
  duration_seconds integer check (duration_seconds is null or duration_seconds >= 0),
  access_level lesson_access_level not null default 'enrolled',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (course_id, position)
);

-- Video delivery is intentionally provider-neutral. A lesson can be moved from
-- one provider to another without changing the lesson itself.
create table lesson_media (
  id uuid primary key default gen_random_uuid(),
  lesson_id uuid not null references lessons(id) on delete cascade,
  provider text not null,
  external_id text not null,
  playback_url text,
  embed_url text,
  status media_status not null default 'processing',
  duration_seconds integer check (duration_seconds is null or duration_seconds >= 0),
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (provider, external_id)
);

create table course_enrollments (
  id uuid primary key default gen_random_uuid(),
  course_id uuid not null references courses(id) on delete cascade,
  user_id uuid not null references users(id) on delete cascade,
  enrolled_at timestamptz not null default now(),
  revoked_at timestamptz,
  unique (course_id, user_id)
);

create table lesson_progress (
  lesson_id uuid not null references lessons(id) on delete cascade,
  user_id uuid not null references users(id) on delete cascade,
  watched_seconds integer not null default 0 check (watched_seconds >= 0),
  completed_at timestamptz,
  updated_at timestamptz not null default now(),
  primary key (lesson_id, user_id)
);

create table assets (
  id uuid primary key default gen_random_uuid(),
  course_id uuid references courses(id) on delete cascade,
  lesson_id uuid references lessons(id) on delete cascade,
  name text not null,
  kind asset_kind not null,
  url text not null,
  size_bytes bigint check (size_bytes is null or size_bytes >= 0),
  description text not null default '',
  access_level lesson_access_level not null default 'enrolled',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint assets_parent_check check (
    (course_id is not null and lesson_id is null) or (course_id is null and lesson_id is not null)
  )
);

create table comments (
  id uuid primary key default gen_random_uuid(),
  course_id uuid not null references courses(id) on delete cascade,
  lesson_id uuid references lessons(id) on delete set null,
  author_id uuid not null references users(id),
  body text not null check (char_length(body) between 1 and 4000),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table comment_replies (
  id uuid primary key default gen_random_uuid(),
  comment_id uuid not null unique references comments(id) on delete cascade,
  author_id uuid not null references users(id),
  body text not null check (char_length(body) between 1 and 4000),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index courses_published_index on courses (published_at desc) where status = 'published';
create index lessons_course_position_index on lessons (course_id, position);
create index lesson_media_lesson_index on lesson_media (lesson_id);
create index enrollments_active_index on course_enrollments (user_id, course_id) where revoked_at is null;
create index comments_course_created_index on comments (course_id, created_at desc);

create function set_updated_at() returns trigger language plpgsql as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

create trigger users_set_updated_at before update on users for each row execute function set_updated_at();
create trigger courses_set_updated_at before update on courses for each row execute function set_updated_at();
create trigger lessons_set_updated_at before update on lessons for each row execute function set_updated_at();
create trigger lesson_media_set_updated_at before update on lesson_media for each row execute function set_updated_at();
create trigger lesson_progress_set_updated_at before update on lesson_progress for each row execute function set_updated_at();
create trigger assets_set_updated_at before update on assets for each row execute function set_updated_at();
create trigger comments_set_updated_at before update on comments for each row execute function set_updated_at();
create trigger comment_replies_set_updated_at before update on comment_replies for each row execute function set_updated_at();