-- MadeEasy / Q8Asia — MySQL schema
-- Ported from the NestJS+Mongoose models in q8service/libs/database/src/schemas.
-- All tables use InnoDB + utf8mb4 and follow the same field semantics as the
-- Mongoose schemas; ObjectId references become BIGINT UNSIGNED FKs.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ─────────────────────────────────────────────────────────────────────────────
-- users  (was: user collection)
-- ─────────────────────────────────────────────────────────────────────────────
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id`                            BIGINT UNSIGNED   NOT NULL AUTO_INCREMENT,
  `type`                          TINYINT UNSIGNED  NOT NULL DEFAULT 1,        -- Role enum
  `gender`                        VARCHAR(20)       NULL,
  `display_name`                  VARCHAR(64)       NULL,
  `profile_icon`                  VARCHAR(16)       NULL,
  `user_name`                     VARCHAR(30)       NULL,
  `dob`                           DATE              NULL,
  `password_hash`                 VARCHAR(255)      NULL,
  `password_salt`                 VARCHAR(255)      NULL,
  `email`                         VARCHAR(128)      NULL,
  `phone`                         VARCHAR(64)       NULL,
  `is_first_time`                 TINYINT(1)        NOT NULL DEFAULT 1,
  `last_login_date`               DATETIME          NULL,
  `login_failed_date`             DATETIME          NULL,
  `login_failed_count`            INT               NULL,
  `is_email_confirmed`            TINYINT(1)        NOT NULL DEFAULT 0,
  `is_phone_confirmed`            TINYINT(1)        NOT NULL DEFAULT 0,
  `is_active`                     TINYINT(1)        NOT NULL DEFAULT 1,
  `email_confirmation_date`       DATETIME          NULL,
  `email_confirmation_expiry_date` DATETIME         NULL,
  `phone_confirmation_date`       DATETIME          NULL,
  `email_confirmation_code`       VARCHAR(64)       NULL,
  `phone_confirmation_code`       VARCHAR(64)       NULL,
  `fcm_token`                     TEXT              NULL,
  `reset_password_count`          INT               NOT NULL DEFAULT 0,
  `reset_password_otp`            VARCHAR(64)       NULL,
  `reset_password_otp_expiry`     DATETIME          NULL,
  `is_photo_upload`               TINYINT(1)        NOT NULL DEFAULT 0,
  `profile_photo_url`             TEXT              NULL,
  `is_profile_updated`            TINYINT(1)        NOT NULL DEFAULT 0,
  `email_notification`            TINYINT(1)        NOT NULL DEFAULT 1,
  `mobile_notification`           TINYINT(1)        NOT NULL DEFAULT 1,
  `delivery_notification`         TINYINT(1)        NOT NULL DEFAULT 1,
  `payment_notification`          TINYINT(1)        NOT NULL DEFAULT 1,
  `dispute_notification`          TINYINT(1)        NOT NULL DEFAULT 1,
  `promo_notification`            TINYINT(1)        NOT NULL DEFAULT 1,
  `is_social_login`               TINYINT(1)        NOT NULL DEFAULT 0,
  `device_token`                  TEXT              NULL,
  `longitude`                     VARCHAR(64)       NULL,
  `latitude`                      VARCHAR(64)       NULL,
  `is_onboarding_complete`        TINYINT(1)        NOT NULL DEFAULT 0,
  `phone_confirmation_otp`        VARCHAR(16)       NULL,
  `phone_confirmation_otp_expiry` DATETIME          NULL,
  `google_refresh_token`          TEXT              NULL,
  `google_access_token`           TEXT              NULL,
  `google_token_expiry`           DATETIME          NULL,
  `created_at`                    DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`                    DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_users_email` (`email`),
  UNIQUE KEY `uniq_users_username` (`user_name`),
  KEY `idx_users_phone` (`phone`),
  KEY `idx_users_type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- admins
-- ─────────────────────────────────────────────────────────────────────────────
DROP TABLE IF EXISTS `admins`;
CREATE TABLE `admins` (
  `id`                          BIGINT UNSIGNED   NOT NULL AUTO_INCREMENT,
  `first_name`                  VARCHAR(64)       NOT NULL,
  `last_name`                   VARCHAR(64)       NOT NULL,
  `email`                       VARCHAR(128)      NOT NULL,
  `phone`                       VARCHAR(20)       NULL,
  `profile_photo`               TEXT              NULL,
  `password_hash`               VARCHAR(255)      NOT NULL,
  `password_salt`               VARCHAR(255)      NOT NULL,
  `role`                        VARCHAR(32)       NOT NULL DEFAULT 'admin',
  `permissions`                 JSON              NULL,                       -- array of permission strings
  `is_active`                   TINYINT(1)        NOT NULL DEFAULT 1,
  `is_email_confirmed`          TINYINT(1)        NOT NULL DEFAULT 0,
  `must_change_password`        TINYINT(1)        NOT NULL DEFAULT 0,
  `login_failed_count`          INT               NOT NULL DEFAULT 0,
  `locked_until`                DATETIME          NULL,
  `last_login_date`             DATETIME          NULL,
  `last_login_ip`               VARCHAR(64)       NULL,
  `reset_password_otp`          VARCHAR(64)       NULL,
  `reset_password_otp_expiry`   DATETIME          NULL,
  `created_by`                  BIGINT UNSIGNED   NULL,
  `created_at`                  DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`                  DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_admins_email` (`email`),
  KEY `idx_admins_role` (`role`),
  KEY `idx_admins_is_active` (`is_active`),
  CONSTRAINT `fk_admins_created_by` FOREIGN KEY (`created_by`) REFERENCES `admins`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- refresh_tokens  (shared by users + admins, owner_type discriminates)
-- ─────────────────────────────────────────────────────────────────────────────
DROP TABLE IF EXISTS `refresh_tokens`;
CREATE TABLE `refresh_tokens` (
  `id`           BIGINT UNSIGNED   NOT NULL AUTO_INCREMENT,
  `owner_type`   ENUM('user','admin') NOT NULL DEFAULT 'user',
  `owner_id`     BIGINT UNSIGNED   NOT NULL,
  `token`        VARCHAR(64)       NOT NULL,
  `expires_at`   DATETIME          NOT NULL,
  `revoked`      TINYINT(1)        NOT NULL DEFAULT 0,
  `is_active`    TINYINT(1)        NOT NULL DEFAULT 1,
  `ip_address`   VARCHAR(64)       NULL,
  `created_at`   DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`   DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_refresh_tokens_token` (`token`),
  KEY `idx_refresh_tokens_owner` (`owner_type`, `owner_id`, `revoked`, `is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- experts
-- ─────────────────────────────────────────────────────────────────────────────
DROP TABLE IF EXISTS `experts`;
CREATE TABLE `experts` (
  `id`               BIGINT UNSIGNED   NOT NULL AUTO_INCREMENT,
  `name`             VARCHAR(128)      NOT NULL,
  `expertise`        VARCHAR(256)      NOT NULL,
  `bio`              TEXT              NOT NULL,
  `full_bio`         MEDIUMTEXT        NULL,
  `photo_url`        TEXT              NULL,
  `experience`       VARCHAR(64)       NULL,
  `specializations`  JSON              NULL,
  `region`           VARCHAR(128)      NULL,
  `linkedin_url`     VARCHAR(256)      NULL,
  `sort_order`       INT               NOT NULL DEFAULT 0,
  `is_active`        TINYINT(1)        NOT NULL DEFAULT 1,
  `created_by`       BIGINT UNSIGNED   NULL,
  `created_at`       DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`       DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_experts_active_sort` (`is_active`, `sort_order`),
  CONSTRAINT `fk_experts_created_by` FOREIGN KEY (`created_by`) REFERENCES `admins`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- courses
-- ─────────────────────────────────────────────────────────────────────────────
DROP TABLE IF EXISTS `courses`;
CREATE TABLE `courses` (
  `id`                  BIGINT UNSIGNED   NOT NULL AUTO_INCREMENT,
  `title`               VARCHAR(256)      NOT NULL,
  `slug`                VARCHAR(256)      NOT NULL,
  `description`         TEXT              NOT NULL,
  `full_description`    MEDIUMTEXT        NULL,
  `category`            VARCHAR(128)      NOT NULL,
  `subcategory`         VARCHAR(128)      NULL,
  `price`               DECIMAL(12,2)     NOT NULL,
  `early_bird_price`    DECIMAL(12,2)     NULL,
  `early_bird_deadline` DATETIME          NULL,
  `duration`            VARCHAR(64)       NOT NULL,
  `start_date`          DATETIME          NOT NULL,
  `end_date`            DATETIME          NULL,
  `venue`               VARCHAR(256)      NULL,
  `city`                VARCHAR(128)      NULL,
  `country`             VARCHAR(64)       NULL,
  `format`              ENUM('in_person','virtual','hybrid') NOT NULL DEFAULT 'in_person',
  `image_url`           TEXT              NULL,
  `trainer`             VARCHAR(128)      NULL,
  `trainer_ref`         BIGINT UNSIGNED   NULL,
  `curriculum`          JSON              NULL,
  `who_should_attend`   JSON              NULL,
  `learning_outcomes`   JSON              NULL,
  `cpd_hours`           DECIMAL(6,2)      NULL,
  `max_participants`    INT               NOT NULL DEFAULT 30,
  `enrolled_count`      INT               NOT NULL DEFAULT 0,
  `status`              ENUM('draft','published','archived','cancelled') NOT NULL DEFAULT 'draft',
  `is_active`           TINYINT(1)        NOT NULL DEFAULT 1,
  `created_by`          BIGINT UNSIGNED   NULL,
  `created_at`          DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`          DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_courses_slug` (`slug`),
  KEY `idx_courses_category` (`category`),
  KEY `idx_courses_status` (`status`),
  KEY `idx_courses_start_date` (`start_date`),
  KEY `idx_courses_is_active` (`is_active`),
  CONSTRAINT `fk_courses_trainer_ref` FOREIGN KEY (`trainer_ref`) REFERENCES `experts`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_courses_created_by` FOREIGN KEY (`created_by`) REFERENCES `admins`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- blog_posts
-- ─────────────────────────────────────────────────────────────────────────────
DROP TABLE IF EXISTS `blog_posts`;
CREATE TABLE `blog_posts` (
  `id`            BIGINT UNSIGNED   NOT NULL AUTO_INCREMENT,
  `title`         VARCHAR(512)      NOT NULL,
  `slug`          VARCHAR(512)      NOT NULL,
  `excerpt`       TEXT              NOT NULL,
  `content`       JSON              NOT NULL,                                  -- array of paragraphs
  `category`      VARCHAR(128)      NULL,
  `tags`          JSON              NULL,
  `image_url`     TEXT              NULL,
  `author`        VARCHAR(128)      NOT NULL,
  `author_ref`    BIGINT UNSIGNED   NULL,
  `read_time`     VARCHAR(32)       NULL,
  `status`        ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
  `is_active`     TINYINT(1)        NOT NULL DEFAULT 1,
  `published_at`  DATETIME          NULL,
  `view_count`    INT               NOT NULL DEFAULT 0,
  `created_by`    BIGINT UNSIGNED   NULL,
  `created_at`    DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_blog_slug` (`slug`),
  KEY `idx_blog_status` (`status`),
  KEY `idx_blog_category` (`category`),
  KEY `idx_blog_published_at` (`published_at`),
  KEY `idx_blog_is_active` (`is_active`),
  CONSTRAINT `fk_blog_author_ref` FOREIGN KEY (`author_ref`) REFERENCES `admins`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_blog_created_by` FOREIGN KEY (`created_by`) REFERENCES `admins`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- faqs
-- ─────────────────────────────────────────────────────────────────────────────
DROP TABLE IF EXISTS `faqs`;
CREATE TABLE `faqs` (
  `id`           BIGINT UNSIGNED   NOT NULL AUTO_INCREMENT,
  `question`     VARCHAR(512)      NOT NULL,
  `answer`       TEXT              NOT NULL,
  `category`     VARCHAR(128)      NOT NULL,
  `sort_order`   INT               NOT NULL DEFAULT 0,
  `is_active`    TINYINT(1)        NOT NULL DEFAULT 1,
  `created_by`   BIGINT UNSIGNED   NULL,
  `created_at`   DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`   DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_faqs_category_sort` (`category`, `sort_order`),
  KEY `idx_faqs_is_active` (`is_active`),
  CONSTRAINT `fk_faqs_created_by` FOREIGN KEY (`created_by`) REFERENCES `admins`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- registrations + registration_items
-- ─────────────────────────────────────────────────────────────────────────────
DROP TABLE IF EXISTS `registration_items`;
DROP TABLE IF EXISTS `registrations`;
CREATE TABLE `registrations` (
  `id`                    BIGINT UNSIGNED   NOT NULL AUTO_INCREMENT,
  `registration_number`   VARCHAR(32)       NOT NULL,
  `user_id`               BIGINT UNSIGNED   NULL,
  `first_name`            VARCHAR(64)       NOT NULL,
  `last_name`             VARCHAR(64)       NOT NULL,
  `email`                 VARCHAR(128)      NOT NULL,
  `phone`                 VARCHAR(20)       NOT NULL,
  `organization`          VARCHAR(128)      NULL,
  `job_title`             VARCHAR(128)      NULL,
  `special_requirements`  TEXT              NULL,
  `total_amount`          DECIMAL(12,2)     NOT NULL,
  `payment_method`        ENUM('credit_card','invoice','bank_transfer') NOT NULL DEFAULT 'credit_card',
  `payment_status`        ENUM('unpaid','paid','partial','refunded') NOT NULL DEFAULT 'unpaid',
  `status`                ENUM('pending','confirmed','cancelled','completed','refunded') NOT NULL DEFAULT 'pending',
  `billing_address`       TEXT              NULL,
  `invoice_url`           TEXT              NULL,
  `stripe_session_id`     VARCHAR(255)      NULL,
  `stripe_payment_id`     VARCHAR(255)      NULL,
  `notes`                 TEXT              NULL,
  `processed_by`          BIGINT UNSIGNED   NULL,
  `created_at`            DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`            DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_registrations_number` (`registration_number`),
  KEY `idx_registrations_email` (`email`),
  KEY `idx_registrations_user` (`user_id`),
  KEY `idx_registrations_status` (`status`),
  KEY `idx_registrations_payment_status` (`payment_status`),
  KEY `idx_registrations_created` (`created_at`),
  CONSTRAINT `fk_registrations_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_registrations_processed_by` FOREIGN KEY (`processed_by`) REFERENCES `admins`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `registration_items` (
  `id`              BIGINT UNSIGNED   NOT NULL AUTO_INCREMENT,
  `registration_id` BIGINT UNSIGNED   NOT NULL,
  `course_id`       BIGINT UNSIGNED   NULL,
  `title`           VARCHAR(256)      NOT NULL,
  `price`           DECIMAL(12,2)     NOT NULL,
  `course_date`     VARCHAR(32)       NULL,
  PRIMARY KEY (`id`),
  KEY `idx_reg_items_registration` (`registration_id`),
  KEY `idx_reg_items_course` (`course_id`),
  CONSTRAINT `fk_reg_items_registration` FOREIGN KEY (`registration_id`) REFERENCES `registrations`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_reg_items_course` FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- notifications
-- ─────────────────────────────────────────────────────────────────────────────
DROP TABLE IF EXISTS `notifications`;
CREATE TABLE `notifications` (
  `id`             BIGINT UNSIGNED   NOT NULL AUTO_INCREMENT,
  `recipient_id`   BIGINT UNSIGNED   NOT NULL,
  `title`          VARCHAR(256)      NOT NULL,
  `message`        TEXT              NOT NULL,
  `type`           VARCHAR(64)       NULL,
  `is_read`        TINYINT(1)        NOT NULL DEFAULT 0,
  `read_at`        DATETIME          NULL,
  `metadata`       JSON              NULL,
  `created_at`     DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`     DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_notifications_recipient` (`recipient_id`, `is_read`),
  KEY `idx_notifications_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- uploads
-- ─────────────────────────────────────────────────────────────────────────────
DROP TABLE IF EXISTS `uploads`;
CREATE TABLE `uploads` (
  `id`                  BIGINT UNSIGNED   NOT NULL AUTO_INCREMENT,
  `user_id`             BIGINT UNSIGNED   NOT NULL,
  `media_type`          ENUM('video','image') NOT NULL,
  `original_file_name`  VARCHAR(255)      NULL,
  `file_size`           BIGINT            NULL,
  `mime_type`           VARCHAR(128)      NULL,
  `status`              ENUM('pending','uploading','processing','complete','failed') NOT NULL DEFAULT 'pending',
  `progress`            INT               NOT NULL DEFAULT 0,
  `url`                 TEXT              NULL,
  `cloudflare_video_id` VARCHAR(128)      NULL,
  `hls_url`             TEXT              NULL,
  `dash_url`            TEXT              NULL,
  `thumbnail_url`       TEXT              NULL,
  `duration`            DECIMAL(10,2)     NULL,
  `width`               INT               NULL,
  `height`              INT               NULL,
  `error_message`       TEXT              NULL,
  `post_id`             BIGINT UNSIGNED   NULL,
  `created_at`          DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`          DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_uploads_user_created` (`user_id`, `created_at`),
  KEY `idx_uploads_status` (`status`),
  KEY `idx_uploads_cloudflare_id` (`cloudflare_video_id`),
  CONSTRAINT `fk_uploads_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- app_settings  (singleton row keyed by `key`)
-- ─────────────────────────────────────────────────────────────────────────────
DROP TABLE IF EXISTS `app_settings`;
CREATE TABLE `app_settings` (
  `id`               BIGINT UNSIGNED   NOT NULL AUTO_INCREMENT,
  `setting_key`      VARCHAR(64)       NOT NULL DEFAULT 'global',
  `site_name`        VARCHAR(255)      NULL,
  `site_description` TEXT              NULL,
  `contact_email`    VARCHAR(128)      NULL,
  `contact_phone`    VARCHAR(64)       NULL,
  `address`          TEXT              NULL,
  `maintenance_mode` TINYINT(1)        NOT NULL DEFAULT 1,
  `terms_content`    MEDIUMTEXT        NULL,
  `privacy_content`  MEDIUMTEXT        NULL,
  `created_at`       DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`       DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_app_settings_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;