-- =====================================================================
-- منصة تدريبية وتعليمية (Training & Educational Platform)
-- Database Schema — derived from Business Requirements Document v1.1
-- Dialect: PostgreSQL (portable to MySQL with minor type changes:
--          BIGSERIAL -> BIGINT AUTO_INCREMENT, JSONB -> JSON, TIMESTAMP as-is)
-- =====================================================================
-- Design notes:
--  1. "Exclusive arc" pattern is used instead of true polymorphic FKs:
--     tables like enrollments / payments / reviews / certificates
--     have several nullable FK columns (course_id, program_id, ...) with a
--     CHECK constraint enforcing that exactly one of them is set.
--  2. "sessions" is a single shared table for scheduled live/onsite meetings,
--     used by both live courses AND training programs (owner_type/owner_id).
--  3. There is no separate currencies table — currency (code/symbol) and
--     phone dialing code live directly on `countries`. Anywhere an item
--     needs a price currency, it references countries(id) via a
--     `currency_country_id` column.
--  4. Courses and training programs are priced per-country in dedicated
--     `course_prices` / `program_prices` tables (item_id, country_id,
--     price each). The remaining catalog items (trainer_consultants,
--     books) keep a single default price + `currency_country_id`, with
--     country-specific overrides in the generic `item_price_overrides`
--     table.
--  5. `courses` and `training_programs` share goals/subscription_terms/
--     certificate_terms as plain duplicated columns (kept as two tables —
--     their content structures differ: lesson-based vs. session-based).
--     Total training hours is never stored; it's derived on demand via the
--     course_total_hours / program_total_hours views (Section 17).
--  6. BILINGUAL CONTENT (Arabic/English): every entity with admin- or
--     trainer-authored free text (names, descriptions, terms, quiz
--     wording, etc.) keeps ONLY structural/non-text columns on its main
--     table; the actual text lives in a companion `<entity>_translations`
--     table keyed by (entity_id, locale). There is no `languages` lookup
--     table — `locale` is a plain VARCHAR(5) with a CHECK constraint
--     (`locale IN ('ar','en')`) on every translation table and on
--     `users.preferred_language`. Enum/status codes (course_type,
--     content_status, attendance_mode, ...) are NOT translated in the DB —
--     the app's i18n layer maps those codes to display labels.
--     User-generated free text (review comments, contact request
--     descriptions) is stored as-is, not translated.
--  7. Transactional notifications do NOT store free title/body text per
--     row (that would freeze a notification in one language forever).
--     Instead: notification_templates + notification_template_translations
--     hold reusable, translated message templates with placeholders, and
--     each `notifications` row just stores which template + JSON params to
--     render it with, in the recipient's preferred_language.
--  8. PEOPLE: visitors/trainees, trainers/consultants, and admins are ALL
--     rows in a single `users` table, distinguished by a `user_type` column.
--     Role-specific attributes live in thin 1:1 extension tables instead
--     of bloating `users` with columns that are NULL for most roles:
--       - trainer_profiles  (user_id PK/FK) — only trainers have a row here
--       - admin_profiles    (user_id PK/FK) — only admins have a row here
--     Anything that needs to link to "a trainer" specifically (courses,
--     programs, trainer_consultants, trainer_permissions, trainer_categories)
--     references trainer_profiles(user_id) rather than users(id) directly —
--     that both documents intent and guarantees the referenced user
--     actually has a trainer profile.
--     Likewise trainer_profiles.created_by_admin_id references
--     admin_profiles(user_id). Note: the DB cannot equally guarantee that
--     a "plain customer" FK (enrollments.user_id, payments.user_id, ...)
--     points at user_type='user' rather than a trainer/admin account — that's
--     left to the application layer, since there's no extension table for
--     the base user_type to anchor a similar FK-based guarantee.
--  9. Every table has both `id` (BIGSERIAL) and `uuid` (UUID, unique,
--     auto-generated via gen_random_uuid(), needs the pgcrypto extension
--     enabled below). ALL foreign keys across the schema — including the
--     two tables whose primary key is `user_id` rather than `id`
--     (admin_profiles, trainer_profiles) — reference the target table's
--     `uuid` column, not its `id`. `id` remains each table's PRIMARY KEY
--     (small, sequential — good for the table's own physical/clustered
--     storage and insert performance) but is otherwise unused outside
--     that table; nothing else joins on it. This also applies to the
--     three "soft" foreign keys that can't have a real REFERENCES
--     constraint because they're polymorphic across more than one target
--     table — sessions.owner_id, item_price_overrides.item_id, and
--     notifications.related_id all store a uuid value (of whichever table
--     their sibling type-discriminator column points to), not a bigint id.
--     Practical effect: never expose or accept a bare `id` outside the
--     database layer — every API/URL/app-facing identifier is a `uuid`.
--     Exception: every `<entity>_translations` table has only `id`, no
--     `uuid`. Nothing ever references a translation row from anywhere else
--     (they're always leaves — courses.uuid is the FK target, never
--     course_translations' own key), and nothing exposes "a translation
--     row" as its own public resource — the API returns a course's
--     translated name/description embedded in the course payload, not as
--     a separately addressable object. So a public identifier for these
--     rows would never actually be used; `id` alone is enough.
-- =====================================================================


-- =====================================================================
-- SECTION 1 — Reference / lookup tables
-- =====================================================================

CREATE EXTENSION IF NOT EXISTS pgcrypto;  -- provides gen_random_uuid()

CREATE TABLE countries (
    id              BIGSERIAL PRIMARY KEY,
    uuid            UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    iso_code        VARCHAR(5)   NOT NULL UNIQUE,
    phone_code      VARCHAR(10)  NOT NULL,                -- e.g. +966, +20
    currency_code   VARCHAR(10)  NOT NULL,                -- e.g. SAR, USD, EGP
    currency_symbol VARCHAR(10)  NOT NULL,
    is_active       BOOLEAN      NOT NULL DEFAULT TRUE
);

CREATE TABLE country_translations (
    id              BIGSERIAL PRIMARY KEY,
    country_id      UUID NOT NULL REFERENCES countries(uuid) ON DELETE CASCADE,
    locale          VARCHAR(5) NOT NULL CHECK (locale IN ('ar','en')),
    name            VARCHAR(150) NOT NULL,                -- e.g. "Saudi Arabia" / "المملكة العربية السعودية"
    currency_name   VARCHAR(100) NOT NULL,                -- e.g. "Saudi Riyal" / "ريال سعودي"
    UNIQUE (country_id, locale)
);

CREATE TABLE cities (
    id              BIGSERIAL PRIMARY KEY,
    uuid            UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    country_id      UUID NOT NULL REFERENCES countries(uuid) ON DELETE CASCADE,
    is_active       BOOLEAN      NOT NULL DEFAULT TRUE
);

CREATE TABLE city_translations (
    id              BIGSERIAL PRIMARY KEY,
    city_id         UUID NOT NULL REFERENCES cities(uuid) ON DELETE CASCADE,
    locale          VARCHAR(5) NOT NULL CHECK (locale IN ('ar','en')),
    name            VARCHAR(150) NOT NULL,
    UNIQUE (city_id, locale)
);

-- Sub-classifications used by courses/programs/books (e.g. Accounting, Tax,
-- HR under education; Fresh Graduates, CFOs under training). A single
-- category row can be attached to a course, a program, and a book at the
-- same time — there's no per-content-type restriction (previously an
-- `area` column pinned each category to one content type; dropped since
-- categories are meant to be shared across all three).
CREATE TABLE categories (
    id              BIGSERIAL PRIMARY KEY,
    uuid            UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    sort_order      INT          NOT NULL DEFAULT 0,
    is_visible      BOOLEAN      NOT NULL DEFAULT TRUE
);

CREATE TABLE category_translations (
    id              BIGSERIAL PRIMARY KEY,
    category_id     UUID NOT NULL REFERENCES categories(uuid) ON DELETE CASCADE,
    locale          VARCHAR(5) NOT NULL CHECK (locale IN ('ar','en')),
    name            VARCHAR(150) NOT NULL,
    description     TEXT,
    UNIQUE (category_id, locale)
);

CREATE TABLE static_pages (                              -- من نحن / الشروط / الخصوصية / الأسئلة الشائعة / تواصل معنا
    id              BIGSERIAL PRIMARY KEY,
    uuid            UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    slug            VARCHAR(100) NOT NULL UNIQUE,
    contact_phone   VARCHAR(50),
    contact_email   VARCHAR(150),
    social_links    JSONB,
    is_visible      BOOLEAN      NOT NULL DEFAULT TRUE,
    sort_order      INT          NOT NULL DEFAULT 0,
    updated_at      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE static_page_translations (
    id              BIGSERIAL PRIMARY KEY,
    page_id         UUID NOT NULL REFERENCES static_pages(uuid) ON DELETE CASCADE,
    locale          VARCHAR(5) NOT NULL CHECK (locale IN ('ar','en')),
    title           VARCHAR(255) NOT NULL,
    content         TEXT,
    contact_address VARCHAR(255),
    UNIQUE (page_id, locale)
);


-- =====================================================================
-- SECTION 2 — People: one unified `users` table + user_type extensions
-- =====================================================================

CREATE TABLE users (                                      -- زائر مسجّل / متدرب / مدرب / أدمن — كلهم صف هنا
    id                  BIGSERIAL PRIMARY KEY,
    uuid                UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    user_type           VARCHAR(20)  NOT NULL DEFAULT 'user'
                            CHECK (user_type IN ('user','trainer','admin')),
    full_name           VARCHAR(150) NOT NULL,
    email               VARCHAR(150) NOT NULL UNIQUE,
    phone               VARCHAR(30)  UNIQUE,               -- required for user_type='user' at the app layer; optional otherwise
    password_hash       VARCHAR(255) NOT NULL,
    photo_url           VARCHAR(255),
    country_id          UUID REFERENCES countries(uuid),
    preferred_language  VARCHAR(5)   NOT NULL DEFAULT 'ar' CHECK (preferred_language IN ('ar','en')),
    allow_notifications BOOLEAN      NOT NULL DEFAULT TRUE,
    status              VARCHAR(20)  NOT NULL DEFAULT 'active'
                            CHECK (status IN ('active','inactive')),
    created_at          TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- user_type='admin' extension
CREATE TABLE admin_profiles (
    user_id         UUID PRIMARY KEY REFERENCES users(uuid) ON DELETE CASCADE,
    uuid            UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    permissions     JSONB                                  -- optional granular admin permissions
);

-- user_type='trainer' extension (account is created only by an admin — never self-signup)
CREATE TABLE trainer_profiles (
    user_id             UUID PRIMARY KEY REFERENCES users(uuid) ON DELETE CASCADE,
    uuid                UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    created_by_admin_id UUID REFERENCES admin_profiles(uuid)
);

-- A trainer's specialization(s) are just tags into the same `categories`
-- taxonomy courses/training_programs already use (e.g. a trainer tagged
-- with the "Accounting" category and/or the "CFOs" category). Many-to-many,
-- since a trainer can specialize in more than one.
CREATE TABLE trainer_categories (
    id              BIGSERIAL PRIMARY KEY,
    uuid            UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    trainer_id      UUID NOT NULL REFERENCES trainer_profiles(uuid) ON DELETE CASCADE,
    category_id     UUID NOT NULL REFERENCES categories(uuid) ON DELETE CASCADE,
    UNIQUE (trainer_id, category_id)
);

-- Granular permissions granted to a trainer beyond the default read-only /
-- content-prep scope (e.g. edit_pricing, publish_content, manual_attendance)
CREATE TABLE trainer_permissions (
    id              BIGSERIAL PRIMARY KEY,
    uuid            UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    user_id         UUID NOT NULL REFERENCES trainer_profiles(uuid) ON DELETE CASCADE,
    permission_code VARCHAR(80) NOT NULL,
    UNIQUE (user_id, permission_code)
);


-- =====================================================================
-- SECTION 3 — Consultation catalog (الاستشارات)
-- =====================================================================

CREATE TABLE consultation_types (                           -- مالية / ضريبية / إدارية...
    id              BIGSERIAL PRIMARY KEY,
    uuid            UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    is_visible      BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE consultation_type_translations (
    id                      BIGSERIAL PRIMARY KEY,
    consultation_type_id    UUID NOT NULL REFERENCES consultation_types(uuid) ON DELETE CASCADE,
    locale                  VARCHAR(5) NOT NULL CHECK (locale IN ('ar','en')),
    name                    VARCHAR(150) NOT NULL,
    description             TEXT,
    UNIQUE (consultation_type_id, locale)
);


-- =====================================================================
-- SECTION 4 — Education: courses (recorded / live)
-- =====================================================================

CREATE TABLE courses (                                      -- الكورسات التعليمية
    id                      BIGSERIAL PRIMARY KEY,
    uuid                    UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    slug                    VARCHAR(220) NOT NULL UNIQUE,
    category_id             UUID REFERENCES categories(uuid),
    trainer_id              UUID REFERENCES trainer_profiles(uuid),  -- المحاضر المسند إليه
    demo_video_url          VARCHAR(255),                            -- Demo تعريفي (language-agnostic asset)
    course_type             VARCHAR(20) NOT NULL
                                CHECK (course_type IN ('recorded','live')),
    has_free_sample         BOOLEAN NOT NULL DEFAULT FALSE,
    has_quiz                BOOLEAN NOT NULL DEFAULT FALSE,
    has_certificate         BOOLEAN NOT NULL DEFAULT FALSE,
    start_date              DATE,                                    -- for live courses
    content_status          VARCHAR(20) NOT NULL DEFAULT 'draft'
                                CHECK (content_status IN ('draft','pending_review','needs_revision','approved')),
    is_visible              BOOLEAN NOT NULL DEFAULT FALSE,           -- published to end users
    created_at              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE course_translations (
    id                      BIGSERIAL PRIMARY KEY,
    course_id               UUID NOT NULL REFERENCES courses(uuid) ON DELETE CASCADE,
    locale                  VARCHAR(5) NOT NULL CHECK (locale IN ('ar','en')),
    name                    VARCHAR(200) NOT NULL,
    description             TEXT,
    demo_text               TEXT,
    target_audience         TEXT,
    requirements            TEXT,
    goals                   TEXT,
    subscription_terms      TEXT,
    certificate_terms       TEXT,
    UNIQUE (course_id, locale)
);

CREATE TABLE course_lessons (                               -- محاضرات الكورس المسجل
    id              BIGSERIAL PRIMARY KEY,
    uuid            UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    course_id       UUID NOT NULL REFERENCES courses(uuid) ON DELETE CASCADE,
    type            VARCHAR(10) NOT NULL DEFAULT 'lesson'
                        CHECK (type IN ('lesson','quiz')),
    video_url       VARCHAR(255),                            -- type='lesson' only
    duration_seconds INT,                                    -- type='lesson' only (video length)
    quiz_duration_minutes      INT,                           -- type='quiz' only (time limit)
    max_attempts                INT NOT NULL DEFAULT 1,       -- type='quiz' only
    pass_score                  NUMERIC(5,2),                 -- type='quiz' only
    is_certificate_requirement  BOOLEAN NOT NULL DEFAULT FALSE, -- type='quiz' only
    show_result_immediately     BOOLEAN NOT NULL DEFAULT TRUE,  -- type='quiz' only
    sort_order      INT NOT NULL DEFAULT 0
);
-- A 'quiz' row's position in the ordered lesson list (sort_order) IS its
-- availability rule: it naturally becomes relevant once the trainee has
-- watched the preceding 'lesson' rows, no separate availability_* config
-- needed (the old quizzes table's availability_type/availability_session_id/
-- date-range mechanism is gone along with that table — see note below).

CREATE TABLE course_lesson_translations (
    id              BIGSERIAL PRIMARY KEY,
    lesson_id       UUID NOT NULL REFERENCES course_lessons(uuid) ON DELETE CASCADE,
    locale          VARCHAR(5) NOT NULL CHECK (locale IN ('ar','en')),
    title           VARCHAR(200) NOT NULL,
    description     TEXT,
    UNIQUE (lesson_id, locale)
);

CREATE TABLE course_materials (                              -- المرفقات / المادة العلمية
    id              BIGSERIAL PRIMARY KEY,
    uuid            UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    course_id       UUID NOT NULL REFERENCES courses(uuid) ON DELETE CASCADE,
    lesson_id       UUID REFERENCES course_lessons(uuid) ON DELETE CASCADE,
    file_url        VARCHAR(255) NOT NULL,
    material_type   VARCHAR(50)
);

CREATE TABLE course_material_translations (
    id              BIGSERIAL PRIMARY KEY,
    material_id     UUID NOT NULL REFERENCES course_materials(uuid) ON DELETE CASCADE,
    locale          VARCHAR(5) NOT NULL CHECK (locale IN ('ar','en')),
    title           VARCHAR(200),
    UNIQUE (material_id, locale)
);

-- One row per country a course is priced in; currency is looked up via
-- country_id -> countries.currency_code/symbol. A course with no row here
-- simply has no price set for that country yet.
CREATE TABLE course_prices (
    id              BIGSERIAL PRIMARY KEY,
    uuid            UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    course_id       UUID NOT NULL REFERENCES courses(uuid) ON DELETE CASCADE,
    country_id      UUID NOT NULL REFERENCES countries(uuid),
    price           NUMERIC(10,2) NOT NULL,
    UNIQUE (course_id, country_id)
);


-- =====================================================================
-- SECTION 5 — Training programs (live / onsite / hybrid)
-- =====================================================================

CREATE TABLE training_programs (                             -- البرامج التدريبية
    id                  BIGSERIAL PRIMARY KEY,
    uuid                UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    slug                VARCHAR(220) NOT NULL UNIQUE,
    category_id         UUID REFERENCES categories(uuid),
    trainer_id          UUID REFERENCES trainer_profiles(uuid),
    demo_video_url      VARCHAR(255),
    attendance_mode     VARCHAR(20) NOT NULL
                            CHECK (attendance_mode IN ('online','onsite','hybrid')),
    start_date          DATE,
    has_quiz            BOOLEAN NOT NULL DEFAULT FALSE,
    has_certificate     BOOLEAN NOT NULL DEFAULT FALSE,
    content_status      VARCHAR(20) NOT NULL DEFAULT 'draft'
                            CHECK (content_status IN ('draft','pending_review','needs_revision','approved')),
    is_visible          BOOLEAN NOT NULL DEFAULT FALSE,
    created_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE training_program_translations (
    id                  BIGSERIAL PRIMARY KEY,
    program_id          UUID NOT NULL REFERENCES training_programs(uuid) ON DELETE CASCADE,
    locale              VARCHAR(5) NOT NULL CHECK (locale IN ('ar','en')),
    name                VARCHAR(200) NOT NULL,
    description         TEXT,
    demo_text           TEXT,
    goals               TEXT,
    content             TEXT,
    target_audience     TEXT,
    experience_level    VARCHAR(100),
    subscription_terms  TEXT,
    certificate_terms   TEXT,
    refund_policy       TEXT,
    UNIQUE (program_id, locale)
);

CREATE TABLE program_materials (
    id              BIGSERIAL PRIMARY KEY,
    uuid            UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    program_id      UUID NOT NULL REFERENCES training_programs(uuid) ON DELETE CASCADE,
    session_id      UUID,                                     -- FK added after `sessions` table below
    file_url        VARCHAR(255) NOT NULL,
    material_type   VARCHAR(50)
);

CREATE TABLE program_material_translations (
    id              BIGSERIAL PRIMARY KEY,
    material_id     UUID NOT NULL REFERENCES program_materials(uuid) ON DELETE CASCADE,
    locale          VARCHAR(5) NOT NULL CHECK (locale IN ('ar','en')),
    title           VARCHAR(200),
    UNIQUE (material_id, locale)
);

-- One row per country a program is priced in; currency is looked up via
-- country_id -> countries.currency_code/symbol, same pattern as course_prices.
CREATE TABLE program_prices (
    id              BIGSERIAL PRIMARY KEY,
    uuid            UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    program_id      UUID NOT NULL REFERENCES training_programs(uuid) ON DELETE CASCADE,
    country_id      UUID NOT NULL REFERENCES countries(uuid),
    price           NUMERIC(10,2) NOT NULL,
    UNIQUE (program_id, country_id)
);


-- =====================================================================
-- SECTION 6 — Shared scheduling: live/onsite sessions & attendance
-- Used by BOTH live courses and training programs (owner_type/owner_id)
-- =====================================================================

CREATE TABLE sessions (                                      -- جلسة / محاضرة لايف أو حضورية
    id                  BIGSERIAL PRIMARY KEY,
    uuid                UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    owner_type          VARCHAR(20) NOT NULL
                            CHECK (owner_type IN ('course','program')),
    owner_id            UUID NOT NULL,                     -- courses.uuid or training_programs.uuid depending on owner_type
    mode                VARCHAR(20) NOT NULL
                            CHECK (mode IN ('online','onsite')),
    session_date        DATE NOT NULL,
    start_time          TIME NOT NULL,
    duration_minutes    INT NOT NULL,
    meeting_link        VARCHAR(255),                        -- for online sessions
    trainer_joined_at   TIMESTAMP,
    sort_order          INT NOT NULL DEFAULT 0
);

CREATE TABLE session_translations (                          -- onsite location text, per language
    id              BIGSERIAL PRIMARY KEY,
    session_id      UUID NOT NULL REFERENCES sessions(uuid) ON DELETE CASCADE,
    locale          VARCHAR(5) NOT NULL CHECK (locale IN ('ar','en')),
    location        VARCHAR(255),
    UNIQUE (session_id, locale)
);

ALTER TABLE program_materials
    ADD CONSTRAINT fk_program_materials_session
    FOREIGN KEY (session_id) REFERENCES sessions(uuid) ON DELETE CASCADE;

CREATE TABLE session_attendance (                            -- حضور المتدربين للجلسات
    id              BIGSERIAL PRIMARY KEY,
    uuid            UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    session_id      UUID NOT NULL REFERENCES sessions(uuid) ON DELETE CASCADE,
    user_id         UUID NOT NULL REFERENCES users(uuid) ON DELETE CASCADE,
    attended        BOOLEAN NOT NULL DEFAULT FALSE,
    joined_at       TIMESTAMP,
    marked_by       VARCHAR(20) NOT NULL DEFAULT 'system'
                        CHECK (marked_by IN ('system','trainer','admin')),
    UNIQUE (session_id, user_id)
);


-- =====================================================================
-- SECTION 7 — Enrollments & progress (محتواي / كورساتي / برامجي)
-- =====================================================================

CREATE TABLE enrollments (
    id                  BIGSERIAL PRIMARY KEY,
    uuid                UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    user_id             UUID NOT NULL REFERENCES users(uuid) ON DELETE CASCADE,
    course_id           UUID REFERENCES courses(uuid),
    program_id          UUID REFERENCES training_programs(uuid),
    progress_percent    NUMERIC(5,2) NOT NULL DEFAULT 0,
    status              VARCHAR(20) NOT NULL DEFAULT 'active'
                            CHECK (status IN ('active','completed')),
    enrolled_at         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CHECK (
        (course_id IS NOT NULL AND program_id IS NULL) OR
        (course_id IS NULL AND program_id IS NOT NULL)
    )
);

CREATE TABLE lesson_progress (                              -- تقدم مشاهدة الكورس المسجل
    id              BIGSERIAL PRIMARY KEY,
    uuid            UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    enrollment_id   UUID NOT NULL REFERENCES enrollments(uuid) ON DELETE CASCADE,
    lesson_id       UUID NOT NULL REFERENCES course_lessons(uuid) ON DELETE CASCADE,
    watched         BOOLEAN NOT NULL DEFAULT FALSE,
    watched_at      TIMESTAMP,
    UNIQUE (enrollment_id, lesson_id)
);


-- =====================================================================
-- SECTION 8 — Consultation bookings
-- =====================================================================

CREATE TABLE trainer_consultants (                            -- ربط مستشار بنوع استشارة يقدمه
    id                      BIGSERIAL PRIMARY KEY,
    uuid                    UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    trainer_id              UUID NOT NULL REFERENCES trainer_profiles(uuid) ON DELETE CASCADE,
    consultation_type_id    UUID NOT NULL REFERENCES consultation_types(uuid),
    price                   NUMERIC(10,2) NOT NULL DEFAULT 0,
    currency_country_id     UUID REFERENCES countries(uuid),
    duration_minutes        INT NOT NULL DEFAULT 30,
    is_available            BOOLEAN NOT NULL DEFAULT TRUE,
    UNIQUE (trainer_id, consultation_type_id)
);

CREATE TABLE consultation_bookings (                          -- حجوزات الاستشارات
    id                      BIGSERIAL PRIMARY KEY,
    uuid                    UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    user_id                 UUID NOT NULL REFERENCES users(uuid) ON DELETE CASCADE,
    trainer_consultant_id   UUID NOT NULL REFERENCES trainer_consultants(uuid),
    scheduled_at            TIMESTAMP NOT NULL,
    duration_minutes        INT NOT NULL,
    price                   NUMERIC(10,2) NOT NULL,
    currency_country_id     UUID REFERENCES countries(uuid),
    meeting_link            VARCHAR(255),                     -- Video Consultation
    status                  VARCHAR(20) NOT NULL DEFAULT 'upcoming'
                                CHECK (status IN ('upcoming','completed','no_show','cancelled')),
    user_attended           BOOLEAN NOT NULL DEFAULT FALSE,
    trainer_attended        BOOLEAN NOT NULL DEFAULT FALSE,
    created_at              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);


-- =====================================================================
-- SECTION 9 — Books & research (الكتب والأبحاث)
-- =====================================================================

-- Revised for the books & research store (see
-- docs/superpowers/specs/2026-09-13-books-research-cart-design.md).
-- `stock_quantity` and `currency_country_id NOT NULL` were added for
-- physical books; `digital_file` lives in `media` (private disk, spec
-- §6.7) instead of a URL column — never a bare file URL.
CREATE TABLE books (                                          -- كتاب ورقي / رقمي / بحث
    id                  BIGSERIAL PRIMARY KEY,
    uuid                UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    category_id         UUID REFERENCES categories(uuid),
    content_type        VARCHAR(20) NOT NULL                   -- immutable after create
                            CHECK (content_type IN ('physical_book','digital_book','research')),
    stock_quantity      INT,                                    -- physical_book only; adjusted via signed deltas (§7.6)
    price               NUMERIC(10,2) NOT NULL DEFAULT 0,
    currency_country_id UUID NOT NULL REFERENCES countries(uuid),
    is_available        BOOLEAN NOT NULL DEFAULT TRUE,          -- stock / availability
    is_visible          BOOLEAN NOT NULL DEFAULT TRUE,
    created_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- cover_image / digital_file are `media` rows keyed by (model_type='Book',
-- model_id, collection); digital_file requires collection_disks.digital_file
-- (private) at attach time — spec §6.7.

CREATE TABLE book_translations (
    id              BIGSERIAL PRIMARY KEY,
    book_id         UUID NOT NULL REFERENCES books(uuid) ON DELETE CASCADE,
    locale          VARCHAR(5) NOT NULL CHECK (locale IN ('ar','en')),
    title           VARCHAR(200) NOT NULL,
    author          VARCHAR(150),
    description     TEXT,
    UNIQUE (book_id, locale)
);

-- Per-country book price overrides — `item_type='book'` in the shared
-- item_price_overrides table (no dedicated book_prices table); see §6.5.

-- Restructured into orders + order_items (spec §6.2–§6.3): a physical
-- order ships one or more books; a digital order is a single delivered
-- item created at once. `book_id` / `book_price` moved to order_items.
CREATE TABLE orders (                                          -- طلباتي / كتبي (شراء كتاب ورقي أو رقمي أو بحث)
    id                  BIGSERIAL PRIMARY KEY,
    uuid                UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    order_number        VARCHAR(50) NOT NULL UNIQUE,
    user_id             UUID NOT NULL REFERENCES users(uuid) ON DELETE CASCADE,
    type                VARCHAR(20) NOT NULL DEFAULT 'physical'
                            CHECK (type IN ('physical','digital')),
    address_id          UUID REFERENCES addresses(uuid) ON DELETE SET NULL,
    recipient_name      VARCHAR(150),                          -- physical orders only
    recipient_phone     VARCHAR(30),                           -- physical orders only
    shipping_address    TEXT,                                   -- physical orders only; display line
    shipping_snapshot   JSONB,                                  -- physical orders only; per-locale address (§8.4)
    subtotal            NUMERIC(10,2) NOT NULL DEFAULT 0,
    shipping_price      NUMERIC(10,2) NOT NULL DEFAULT 0,
    total_price         NUMERIC(10,2) NOT NULL,
    currency_country_id UUID REFERENCES countries(uuid),
    status              VARCHAR(20) NOT NULL DEFAULT 'processing'
                            CHECK (status IN ('processing','delivered')),      -- processing→delivered, one-way, admin only
    delivered_at        TIMESTAMP,
    created_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (                                     -- one row per book on an order
    id              BIGSERIAL PRIMARY KEY,
    order_id        UUID NOT NULL REFERENCES orders(uuid) ON DELETE CASCADE,
    book_id         UUID NOT NULL REFERENCES books(uuid),       -- RESTRICT: an ordered book cannot be deleted (§9.1)
    quantity        INT NOT NULL DEFAULT 1,
    unit_price      NUMERIC(10,2) NOT NULL,                    -- snapshot at purchase time
    line_total      NUMERIC(10,2) NOT NULL,                    -- unit_price * quantity
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (order_id, book_id)
);
-- "Don't double-sell a digital book to the same user" is an
-- application-layer check (BookOwnershipService::owns) over order_items —
-- a physical book legitimately CAN be re-ordered (e.g. as a gift).

-- Physical-book cart, for guests and signed-in users (spec §6.4, §8.2).
-- Exactly one of user_id / guest_cart_id is set — enforced in the app,
-- because a CHECK constraint can't reference columns with FK actions.
-- Rows store no prices: the cart is always priced live (§7).
CREATE TABLE guest_carts (
    id              BIGSERIAL PRIMARY KEY,
    token_hash      CHAR(64) NOT NULL UNIQUE,                  -- SHA-256 of the guest token; the token itself is never stored
    expires_at      TIMESTAMP NOT NULL,                          -- sliding; pruned daily once past
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE cart_items (
    id              BIGSERIAL PRIMARY KEY,
    user_id         UUID REFERENCES users(uuid) ON DELETE CASCADE,
    guest_cart_id   UUID REFERENCES guest_carts(uuid) ON DELETE CASCADE,
    book_id         UUID NOT NULL REFERENCES books(uuid) ON DELETE CASCADE,
    quantity        INT NOT NULL,                               -- >= 1
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (user_id, book_id),
    UNIQUE (guest_cart_id, book_id)
);


-- =====================================================================
-- SECTION 10 — Assessments (الاختبارات / Quiz)
-- =====================================================================
-- There is no standalone `quizzes` table anymore — a quiz IS a
-- course_lessons row with type='quiz'. Its display name comes from that
-- row's course_lesson_translations.title, same as any lesson. Everything
-- below hangs off course_lessons.uuid (via lesson_id) instead of a
-- separate quizzes.uuid.

-- question_text/option_text are not translated — a quiz is authored in a
-- single language, unlike other trainer-authored content in this schema.
CREATE TABLE quiz_questions (
    id              BIGSERIAL PRIMARY KEY,
    uuid            UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    lesson_id       UUID NOT NULL REFERENCES course_lessons(uuid) ON DELETE CASCADE,
    question_type   VARCHAR(20) NOT NULL
                        CHECK (question_type IN ('multiple_choice','true_false')),
    question_text   TEXT NOT NULL,
    score           NUMERIC(5,2) NOT NULL DEFAULT 1,
    sort_order      INT NOT NULL DEFAULT 0
);

CREATE TABLE quiz_options (
    id              BIGSERIAL PRIMARY KEY,
    uuid            UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    question_id     UUID NOT NULL REFERENCES quiz_questions(uuid) ON DELETE CASCADE,
    option_text     VARCHAR(255) NOT NULL,
    is_correct      BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE quiz_attempts (
    id              BIGSERIAL PRIMARY KEY,
    uuid            UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    lesson_id       UUID NOT NULL REFERENCES course_lessons(uuid) ON DELETE CASCADE,
    user_id         UUID NOT NULL REFERENCES users(uuid) ON DELETE CASCADE,
    attempt_number  INT NOT NULL,
    score           NUMERIC(5,2),
    passed          BOOLEAN,
    started_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at    TIMESTAMP,
    UNIQUE (lesson_id, user_id, attempt_number)
);

CREATE TABLE quiz_answers (
    id                  BIGSERIAL PRIMARY KEY,
    uuid                UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    attempt_id          UUID NOT NULL REFERENCES quiz_attempts(uuid) ON DELETE CASCADE,
    question_id         UUID NOT NULL REFERENCES quiz_questions(uuid),
    selected_option_id  UUID REFERENCES quiz_options(uuid),
    is_correct          BOOLEAN
);


-- =====================================================================
-- SECTION 11 — Certificates (الشهادات)
-- =====================================================================

CREATE TABLE certificates (
    id                  BIGSERIAL PRIMARY KEY,
    uuid                UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    user_id             UUID NOT NULL REFERENCES users(uuid) ON DELETE CASCADE,
    course_id           UUID REFERENCES courses(uuid),
    program_id          UUID REFERENCES training_programs(uuid),
    certificate_number  VARCHAR(50) NOT NULL UNIQUE,
    qr_token            VARCHAR(100) NOT NULL UNIQUE,     -- encoded in the QR code for public verification
    issued_at           TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status              VARCHAR(20) NOT NULL DEFAULT 'valid'
                            CHECK (status IN ('valid','revoked')),
    CHECK (
        (course_id IS NOT NULL AND program_id IS NULL) OR
        (course_id IS NULL AND program_id IS NOT NULL)
    )
);
-- Note: the certificate PDF/HTML is rendered at issue-time in the user's
-- preferred_language using courses/training_program translated names —
-- no separate certificate translation table is needed.


-- =====================================================================
-- SECTION 12 — Payments & pricing (المدفوعات / الأسعار والعملات)
-- =====================================================================

CREATE TABLE payments (
    id                      BIGSERIAL PRIMARY KEY,
    uuid                    UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    user_id                 UUID NOT NULL REFERENCES users(uuid) ON DELETE CASCADE,
    enrollment_id           UUID REFERENCES enrollments(uuid),         -- course or program subscription
    consultation_booking_id UUID REFERENCES consultation_bookings(uuid),
    order_id                UUID REFERENCES orders(uuid),              -- physical or digital book purchase
    amount                  NUMERIC(10,2) NOT NULL,
    shipping_fee            NUMERIC(10,2) DEFAULT 0,
    total_amount            NUMERIC(10,2) NOT NULL,
    currency_country_id     UUID REFERENCES countries(uuid),
    payment_method          VARCHAR(50),
    status                  VARCHAR(20) NOT NULL DEFAULT 'pending'
                                CHECK (status IN ('success','failed','pending')),
    transaction_reference   VARCHAR(150),
    paid_at                 TIMESTAMP,
    created_at              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CHECK (
        (CASE WHEN enrollment_id IS NOT NULL THEN 1 ELSE 0 END +
         CASE WHEN consultation_booking_id IS NOT NULL THEN 1 ELSE 0 END +
         CASE WHEN order_id IS NOT NULL THEN 1 ELSE 0 END) = 1
    )
);

-- Per-country price overrides for the remaining catalog item types.
-- Courses and programs use their own dedicated *_prices tables instead.
CREATE TABLE item_price_overrides (
    id              BIGSERIAL PRIMARY KEY,
    uuid            UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    item_type       VARCHAR(30) NOT NULL
                        CHECK (item_type IN ('trainer_consultant','book')),
    item_id         UUID NOT NULL,                         -- internal id of the trainer_consultant/book row, per item_type (the actual migration uses a plain bigint FK, not a uuid — see database/migrations/2026_09_06_000020_create_payment_tables.php)
    country_id      UUID NOT NULL REFERENCES countries(uuid),
    price           NUMERIC(10,2) NOT NULL,
    UNIQUE (item_type, item_id, country_id)
);


-- =====================================================================
-- SECTION 13 — Reviews (التقييمات) — user-written text, not translated
-- =====================================================================

CREATE TABLE reviews (
    id                      BIGSERIAL PRIMARY KEY,
    uuid                    UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    user_id                 UUID NOT NULL REFERENCES users(uuid) ON DELETE CASCADE,
    trainer_id              UUID REFERENCES trainer_profiles(uuid) ON DELETE SET NULL, -- trainer at review time; NULL for books
    reviewable_type         VARCHAR(255) NOT NULL,         -- App\Models\Course | TrainingProgram | ConsultationBooking | Book
    reviewable_id           BIGINT NOT NULL,               -- id in the table reviewable_type names (polymorphic: no FK)
    rating                  SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    comment                 TEXT,                          -- written by the user in whatever language they used
    status                  VARCHAR(20) NOT NULL DEFAULT 'visible'
                                CHECK (status IN ('visible','hidden')),
    created_at              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (user_id, reviewable_type, reviewable_id)       -- one review per user per item
);
CREATE INDEX reviews_reviewable_idx ON reviews (reviewable_type, reviewable_id);
-- A reviewed item cannot be deleted (HasReviews guard, spec §6.9).


-- =====================================================================
-- SECTION 14 — Notifications (الإشعارات) — template + params, not free text
-- =====================================================================

CREATE TABLE notification_templates (
    id              BIGSERIAL PRIMARY KEY,
    uuid            UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    template_key    VARCHAR(80) NOT NULL UNIQUE,           -- e.g. 'course_enrollment_confirmed'
    description     VARCHAR(255)                           -- internal note for admins, not shown to users
);

CREATE TABLE notification_template_translations (
    id              BIGSERIAL PRIMARY KEY,
    template_id     UUID NOT NULL REFERENCES notification_templates(uuid) ON DELETE CASCADE,
    locale          VARCHAR(5) NOT NULL CHECK (locale IN ('ar','en')),
    title_template  VARCHAR(255) NOT NULL,                 -- e.g. "تم تأكيد اشتراكك في {{course_name}}"
    body_template   TEXT NOT NULL,
    UNIQUE (template_id, locale)
);

CREATE TABLE notifications (
    id              BIGSERIAL PRIMARY KEY,
    uuid            UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    user_id         UUID NOT NULL REFERENCES users(uuid) ON DELETE CASCADE,  -- recipient (any user_type — user/trainer/admin)
    template_id     UUID NOT NULL REFERENCES notification_templates(uuid),
    params          JSONB,                                 -- e.g. {"course_name": "...", "session_date": "..."}
    related_type    VARCHAR(50),                            -- e.g. 'course','program','consultation_booking','certificate'
    related_id      UUID,                                  -- uuid of the row named in related_type
    is_read         BOOLEAN NOT NULL DEFAULT FALSE,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- Rendering: look up the recipient's preferred_language (users.preferred_language),
-- pull the matching notification_template_translations row for template_id,
-- and interpolate `params` into its title_template/body_template placeholders.


-- =====================================================================
-- SECTION 15 — Contact / CMS (طلبات التواصل)
-- =====================================================================

CREATE TABLE contact_request_types (                        -- استفسار عام / طلب كورس خاص / طلب شراكة ...
    id              BIGSERIAL PRIMARY KEY,
    uuid            UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    is_visible      BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE contact_request_type_translations (
    id              BIGSERIAL PRIMARY KEY,
    type_id         UUID NOT NULL REFERENCES contact_request_types(uuid) ON DELETE CASCADE,
    locale          VARCHAR(5) NOT NULL CHECK (locale IN ('ar','en')),
    name            VARCHAR(150) NOT NULL,
    UNIQUE (type_id, locale)
);

CREATE TABLE contact_requests (
    id                  BIGSERIAL PRIMARY KEY,
    uuid                UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    name                VARCHAR(150) NOT NULL,
    phone               VARCHAR(30),
    email               VARCHAR(150),
    request_type_id     UUID REFERENCES contact_request_types(uuid),
    subject             VARCHAR(200),
    description         TEXT,                               -- written by the user, not translated
    status              VARCHAR(20) NOT NULL DEFAULT 'new'
                            CHECK (status IN ('new','in_review','replied','closed')),
    created_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);


-- =====================================================================
-- SECTION 16 — Helpful indexes
-- =====================================================================

CREATE INDEX idx_users_user_type              ON users(user_type);
CREATE INDEX idx_cities_country               ON cities(country_id);
CREATE INDEX idx_trainer_categories_trainer   ON trainer_categories(trainer_id);
CREATE INDEX idx_trainer_categories_category  ON trainer_categories(category_id);
CREATE INDEX idx_courses_category             ON courses(category_id);
CREATE INDEX idx_courses_trainer              ON courses(trainer_id);
CREATE INDEX idx_course_prices_course         ON course_prices(course_id);
CREATE INDEX idx_course_prices_country        ON course_prices(country_id);
CREATE INDEX idx_programs_category            ON training_programs(category_id);
CREATE INDEX idx_programs_trainer             ON training_programs(trainer_id);
CREATE INDEX idx_program_prices_program       ON program_prices(program_id);
CREATE INDEX idx_program_prices_country       ON program_prices(country_id);
CREATE INDEX idx_sessions_owner               ON sessions(owner_type, owner_id);
CREATE INDEX idx_session_attendance_user      ON session_attendance(user_id);
CREATE INDEX idx_enrollments_user             ON enrollments(user_id);
CREATE INDEX idx_enrollments_course           ON enrollments(course_id);
CREATE INDEX idx_enrollments_program          ON enrollments(program_id);
CREATE INDEX idx_lesson_progress_lesson       ON lesson_progress(lesson_id);
CREATE INDEX idx_bookings_user                ON consultation_bookings(user_id);
CREATE INDEX idx_bookings_trainer_consultant  ON consultation_bookings(trainer_consultant_id);
CREATE INDEX idx_orders_user                  ON orders(user_id);
CREATE INDEX idx_orders_book                  ON orders(book_id);
CREATE INDEX idx_payments_user                ON payments(user_id);
CREATE INDEX idx_payments_enrollment          ON payments(enrollment_id);
CREATE INDEX idx_payments_booking             ON payments(consultation_booking_id);
CREATE INDEX idx_payments_order               ON payments(order_id);
CREATE INDEX idx_reviews_user                 ON reviews(user_id);
CREATE INDEX idx_reviews_course               ON reviews(course_id);
CREATE INDEX idx_reviews_program              ON reviews(program_id);
CREATE INDEX idx_reviews_booking              ON reviews(consultation_booking_id);
CREATE INDEX idx_reviews_book                 ON reviews(book_id);
CREATE INDEX idx_notifications_user           ON notifications(user_id);
CREATE INDEX idx_certificates_user            ON certificates(user_id);
CREATE INDEX idx_certificates_course          ON certificates(course_id);
CREATE INDEX idx_certificates_program         ON certificates(program_id);
CREATE INDEX idx_quiz_attempts_user           ON quiz_attempts(user_id);
CREATE INDEX idx_quiz_questions_lesson        ON quiz_questions(lesson_id);


-- =====================================================================
-- SECTION 17 — Computed views (derived, not stored)
-- =====================================================================

-- Total hours for a course: sum of recorded-lesson durations for
-- 'recorded' courses, or sum of scheduled session durations for 'live'
-- courses. Not stored on the row — always derived from the real content.
CREATE VIEW course_total_hours AS
SELECT
    c.id   AS course_id,
    c.uuid AS course_uuid,
    CASE
        WHEN c.course_type = 'recorded' THEN
            COALESCE((SELECT SUM(cl.duration_seconds)
                      FROM course_lessons cl
                      WHERE cl.course_id = c.uuid AND cl.type = 'lesson'), 0) / 3600.0
        ELSE
            COALESCE((SELECT SUM(s.duration_minutes)
                      FROM sessions s
                      WHERE s.owner_type = 'course' AND s.owner_id = c.uuid), 0) / 60.0
    END AS total_hours
FROM courses c;

-- Total hours for a training program: sum of all its scheduled sessions'
-- durations, regardless of mode (online/onsite/hybrid all live in `sessions`).
CREATE VIEW program_total_hours AS
SELECT
    p.id   AS program_id,
    p.uuid AS program_uuid,
    COALESCE((SELECT SUM(s.duration_minutes)
              FROM sessions s
              WHERE s.owner_type = 'program' AND s.owner_id = p.uuid), 0) / 60.0 AS total_hours
FROM training_programs p;


-- =====================================================================
-- SECTION 18 — Generic media / file storage (polymorphic)
-- =====================================================================
-- This table is an existing, fixed schema (a Laravel HasMedia-style
-- module) — kept unchanged from its original definition rather than
-- adapted to this file's id/uuid convention. Notably: `model_id` here is
-- a plain BIGINT matching the target model's Eloquent auto-increment
-- `id` (NOT its `uuid`) — intentional, since this table already exists
-- and its polymorphic attachment mechanism keys off bigint ids.
-- All uploaded files/images/videos across the whole app go through this
-- one table (avatars, course/program demo assets, lesson videos, book
-- covers/files, attachments, etc.) via (model_type, model_id, collection)
-- rather than ad-hoc URL columns scattered across other tables. Variants/
-- thumbnails live inside `meta` (JSON), not as separate rows.
CREATE TABLE media (
    id              BIGSERIAL PRIMARY KEY,
    hash            VARCHAR(255),
    disk            VARCHAR(255) NOT NULL DEFAULT 'public',
    path            VARCHAR(255) NOT NULL,
    original_name   VARCHAR(255),
    mime_type       VARCHAR(255),
    size            BIGINT,
    is_temp         BOOLEAN      NOT NULL DEFAULT TRUE,
    model_type      VARCHAR(255),
    model_id        BIGINT,
    collection      VARCHAR(255),
    sort_order      INT          NOT NULL DEFAULT 0,
    meta            JSONB,
    deleted_at      TIMESTAMP,
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP
);

CREATE INDEX media_collection_index           ON media(collection);
CREATE INDEX media_is_temp_index              ON media(is_temp);
CREATE INDEX media_hash_index                 ON media(hash);
CREATE INDEX media_model_type_model_id_index  ON media(model_type, model_id);


-- =====================================================================
-- SECTION 19 — Notifications module (existing, fixed schema)
-- =====================================================================
-- Like `media` above, these two tables belong to an existing Laravel
-- module (a bigint-id-based notifications layer sitting alongside
-- Laravel's own notifiable/database-channel convention) and are kept
-- unchanged from their original definitions rather than adapted to this
-- file's id/uuid convention. `admin_notifications.admin_id` and
-- `notifications.notifiable_id` reference users(id) (bigint), NOT
-- users(uuid), the same bigint-FK exception already noted for `media`.
-- This is a DIFFERENT notification mechanism from SECTION 14's
-- notification_templates/notifications pair (that section models the
-- business's own translated, templated user notifications; this one is
-- the underlying module used to actually broadcast/store them, plus a
-- separate admin-facing broadcast log). The two are not meant to be
-- joined.

-- One row per admin-initiated broadcast (e.g. "notify all supervisors").
-- `content` holds the actual message payload (already resolved, not a
-- template) and `receivers_data` optionally narrows the target audience
-- (specific user ids/groups) beyond the `type` targeting.
CREATE TABLE admin_notifications (
    id              BIGSERIAL PRIMARY KEY,
    admin_id        BIGINT REFERENCES users(id) ON DELETE SET NULL,  -- bigint FK, see note above
    type            VARCHAR(255) NOT NULL,           -- target user type: supervisor, etc.
    content         JSONB NOT NULL,
    receivers_data  JSONB,                           -- optional extra targeting (specific users/groups)
    is_sent         BOOLEAN NOT NULL DEFAULT FALSE,
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP
);

-- Laravel's standard database-notifications table (Notifiable::notify()),
-- extended with an optional link back to the admin_notifications broadcast
-- that generated it. Primary key is a UUID (Laravel's own convention for
-- this table), not this file's usual BIGSERIAL id + separate uuid column.
CREATE TABLE notifications (
    id                      UUID PRIMARY KEY,
    admin_notification_id   BIGINT REFERENCES admin_notifications(id) ON DELETE SET NULL,
    notifiable_type         VARCHAR(255) NOT NULL,     -- polymorphic recipient, e.g. App\Models\User
    notifiable_id           BIGINT NOT NULL,           -- bigint FK (users.id), see note above
    type                    VARCHAR(255) NOT NULL,     -- Laravel Notification class name
    data                    TEXT,                      -- per-recipient payload
    read_at                 TIMESTAMP,
    created_at              TIMESTAMP,
    updated_at              TIMESTAMP
);

CREATE INDEX notifications_notifiable_type_notifiable_id_index ON notifications(notifiable_type, notifiable_id);


-- =====================================================================
-- SECTION 20 — Verification module (existing, fixed schema)
-- =====================================================================
-- OTP/token verification for email and phone (signup confirmation,
-- phone change, password reset, etc.), attached polymorphically to
-- whatever model is being verified. Same as `media`/SECTION 19: an
-- existing module kept in its original bigint-id form, not adapted to
-- this file's id/uuid convention — `verifiable_id` is a bigint FK
-- matching the target model's own `id`.
CREATE TABLE verifications (
    id              BIGSERIAL PRIMARY KEY,
    token           UUID NOT NULL,                    -- opaque handle returned to the client for this verification flow
    verifiable_type VARCHAR(255),                      -- polymorphic target, e.g. App\Models\User
    verifiable_id   BIGINT,                            -- bigint FK, see note above
    type            VARCHAR(20) NOT NULL CHECK (type IN ('email','phone')),
    phone_code      VARCHAR(255),                      -- e.g. +966, type='phone' only
    target          VARCHAR(255) NOT NULL,             -- the email address or phone number being verified
    code            VARCHAR(255) NOT NULL,             -- the OTP code sent to the target
    expires_at      TIMESTAMP NOT NULL,
    verified_at     TIMESTAMP,
    attempts        SMALLINT NOT NULL DEFAULT 0,
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP
);

CREATE INDEX verifications_verifiable_type_verifiable_id_index ON verifications(verifiable_type, verifiable_id);
CREATE INDEX verifications_token_type_target_index             ON verifications(token, type, target);