CREATE TABLE IF NOT EXISTS `therapist_profiles` (
    `id`                INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id`           INT UNSIGNED NOT NULL,
    `slug`              VARCHAR(120) NOT NULL,
    `city_id`           INT UNSIGNED NOT NULL,
    `address`           VARCHAR(255) NULL,
    `lat`               DECIMAL(10,8) NULL,
    `lng`               DECIMAL(11,8) NULL,
    `bio_lv`            TEXT NULL,
    `bio_ru`            TEXT NULL,
    `bio_en`            TEXT NULL,
    `experience_years`  TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `diploma`           VARCHAR(255) NULL,
    `is_verified`       TINYINT(1) NOT NULL DEFAULT 0,
    `verified_at`       DATETIME NULL,
    `is_featured`       TINYINT(1) NOT NULL DEFAULT 0,
    `is_active`         TINYINT(1) NOT NULL DEFAULT 1,
    `home_visit`        TINYINT(1) NOT NULL DEFAULT 0,
    `outcall`           TINYINT(1) NOT NULL DEFAULT 0,
    `gender`            ENUM('male','female','other') NULL,
    `rating_avg`        DECIMAL(3,2) NOT NULL DEFAULT 0.00,
    `reviews_count`     INT UNSIGNED NOT NULL DEFAULT 0,
    `bookings_count`    INT UNSIGNED NOT NULL DEFAULT 0,
    `views_count`       INT UNSIGNED NOT NULL DEFAULT 0,
    `company_id`        INT UNSIGNED NULL COMMENT 'Linked company user_id',
    `meta_title_lv`     VARCHAR(160) NULL,
    `meta_title_ru`     VARCHAR(160) NULL,
    `meta_title_en`     VARCHAR(160) NULL,
    `meta_desc_lv`      VARCHAR(320) NULL,
    `meta_desc_ru`      VARCHAR(320) NULL,
    `meta_desc_en`      VARCHAR(320) 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 `therapist_profiles_user_id_unique` (`user_id`),
    UNIQUE KEY `therapist_profiles_slug_unique` (`slug`),
    KEY `therapist_profiles_city_id_index` (`city_id`),
    KEY `therapist_profiles_is_active_verified` (`is_active`, `is_verified`),
    KEY `therapist_profiles_is_featured` (`is_featured`),
    CONSTRAINT `fk_tp_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_tp_city` FOREIGN KEY (`city_id`) REFERENCES `cities` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
