-- CreateTable
CREATE TABLE `member_medical_records` (
    `id` CHAR(36) NOT NULL,
    `gym_id` CHAR(36) NOT NULL,
    `member_id` CHAR(36) NOT NULL,
    `created_by_id` CHAR(36) NOT NULL,
    `allergies` TEXT NULL,
    `injuries` TEXT NULL,
    `conditions` TEXT NULL,
    `medications` TEXT NULL,
    `contraindications` TEXT NULL,
    `doctor_notes` TEXT NULL,
    `recorded_at` DATE NOT NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    INDEX `member_medical_records_gym_id_member_id_created_at_idx`(`gym_id`, `member_id`, `created_at`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `member_nutrition_plans` (
    `id` CHAR(36) NOT NULL,
    `gym_id` CHAR(36) NOT NULL,
    `member_id` CHAR(36) NOT NULL,
    `created_by_id` CHAR(36) NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `goal_type` ENUM('weight_loss', 'muscle_gain', 'maintenance', 'custom') NOT NULL,
    `calorie_target` INTEGER NULL,
    `macro_split` JSON NULL,
    `meal_template` JSON NULL,
    `notes` TEXT NULL,
    `start_date` DATE NOT NULL,
    `end_date` DATE NULL,
    `is_active` BOOLEAN NOT NULL DEFAULT true,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    INDEX `member_nutrition_plans_gym_id_member_id_created_at_idx`(`gym_id`, `member_id`, `created_at`),
    INDEX `member_nutrition_plans_gym_id_member_id_is_active_idx`(`gym_id`, `member_id`, `is_active`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `member_supplements` (
    `id` CHAR(36) NOT NULL,
    `gym_id` CHAR(36) NOT NULL,
    `member_id` CHAR(36) NOT NULL,
    `created_by_id` CHAR(36) NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `dosage` VARCHAR(100) NULL,
    `timing` VARCHAR(100) NULL,
    `frequency` ENUM('daily', 'weekly', 'as_needed') NOT NULL DEFAULT 'daily',
    `start_date` DATE NOT NULL,
    `end_date` DATE NULL,
    `adherence_notes` TEXT NULL,
    `is_active` BOOLEAN NOT NULL DEFAULT true,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    INDEX `member_supplements_gym_id_member_id_created_at_idx`(`gym_id`, `member_id`, `created_at`),
    INDEX `member_supplements_gym_id_member_id_is_active_idx`(`gym_id`, `member_id`, `is_active`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `member_workout_plans` (
    `id` CHAR(36) NOT NULL,
    `gym_id` CHAR(36) NOT NULL,
    `member_id` CHAR(36) NOT NULL,
    `created_by_id` CHAR(36) NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `description` TEXT NULL,
    `exercises` JSON NOT NULL,
    `status` ENUM('draft', 'active', 'archived') NOT NULL DEFAULT 'draft',
    `start_date` DATE NULL,
    `end_date` DATE NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    INDEX `member_workout_plans_gym_id_member_id_created_at_idx`(`gym_id`, `member_id`, `created_at`),
    INDEX `member_workout_plans_gym_id_member_id_status_idx`(`gym_id`, `member_id`, `status`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `member_workout_sessions` (
    `id` CHAR(36) NOT NULL,
    `gym_id` CHAR(36) NOT NULL,
    `member_id` CHAR(36) NOT NULL,
    `workout_plan_id` CHAR(36) NULL,
    `created_by_id` CHAR(36) NOT NULL,
    `session_date` DATE NOT NULL,
    `exercises` JSON NOT NULL,
    `duration_minutes` INTEGER NULL,
    `completed` BOOLEAN NOT NULL DEFAULT true,
    `notes` TEXT NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `member_workout_sessions_gym_id_member_id_session_date_idx`(`gym_id`, `member_id`, `session_date`),
    INDEX `member_workout_sessions_gym_id_member_id_created_at_idx`(`gym_id`, `member_id`, `created_at`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `member_assessments` (
    `id` CHAR(36) NOT NULL,
    `gym_id` CHAR(36) NOT NULL,
    `member_id` CHAR(36) NOT NULL,
    `created_by_id` CHAR(36) NOT NULL,
    `assessed_at` DATE NOT NULL,
    `weight_kg` DECIMAL(5, 2) NULL,
    `body_fat_pct` DECIMAL(4, 1) NULL,
    `measurements` JSON NULL,
    `photo_urls` JSON NULL,
    `notes` TEXT NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    INDEX `member_assessments_gym_id_member_id_assessed_at_idx`(`gym_id`, `member_id`, `assessed_at`),
    INDEX `member_assessments_gym_id_member_id_created_at_idx`(`gym_id`, `member_id`, `created_at`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `member_progress_events` (
    `id` CHAR(36) NOT NULL,
    `gym_id` CHAR(36) NOT NULL,
    `member_id` CHAR(36) NOT NULL,
    `event_type` ENUM('payment', 'medical_record', 'nutrition_plan', 'supplement', 'workout_plan', 'workout_session', 'assessment') NOT NULL,
    `source_id` CHAR(36) NOT NULL,
    `title` VARCHAR(200) NOT NULL,
    `summary` VARCHAR(500) NULL,
    `payload` JSON NULL,
    `occurred_at` DATETIME(3) NOT NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `member_progress_events_gym_id_member_id_occurred_at_idx`(`gym_id`, `member_id`, `occurred_at`),
    UNIQUE INDEX `member_progress_events_gym_id_member_id_event_type_source_id_key`(`gym_id`, `member_id`, `event_type`, `source_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `member_progress_audit_logs` (
    `id` CHAR(36) NOT NULL,
    `gym_id` CHAR(36) NOT NULL,
    `member_id` CHAR(36) NOT NULL,
    `actor_id` CHAR(36) NOT NULL,
    `action` VARCHAR(50) NOT NULL,
    `resource_type` VARCHAR(50) NOT NULL,
    `resource_id` CHAR(36) NOT NULL,
    `changes` JSON NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `member_progress_audit_logs_gym_id_member_id_created_at_idx`(`gym_id`, `member_id`, `created_at`),
    INDEX `member_progress_audit_logs_resource_type_resource_id_idx`(`resource_type`, `resource_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `member_medical_records` ADD CONSTRAINT `member_medical_records_gym_id_fkey` FOREIGN KEY (`gym_id`) REFERENCES `gyms`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `member_medical_records` ADD CONSTRAINT `member_medical_records_member_id_fkey` FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `member_medical_records` ADD CONSTRAINT `member_medical_records_created_by_id_fkey` FOREIGN KEY (`created_by_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE `member_nutrition_plans` ADD CONSTRAINT `member_nutrition_plans_gym_id_fkey` FOREIGN KEY (`gym_id`) REFERENCES `gyms`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `member_nutrition_plans` ADD CONSTRAINT `member_nutrition_plans_member_id_fkey` FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `member_nutrition_plans` ADD CONSTRAINT `member_nutrition_plans_created_by_id_fkey` FOREIGN KEY (`created_by_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE `member_supplements` ADD CONSTRAINT `member_supplements_gym_id_fkey` FOREIGN KEY (`gym_id`) REFERENCES `gyms`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `member_supplements` ADD CONSTRAINT `member_supplements_member_id_fkey` FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `member_supplements` ADD CONSTRAINT `member_supplements_created_by_id_fkey` FOREIGN KEY (`created_by_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE `member_workout_plans` ADD CONSTRAINT `member_workout_plans_gym_id_fkey` FOREIGN KEY (`gym_id`) REFERENCES `gyms`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `member_workout_plans` ADD CONSTRAINT `member_workout_plans_member_id_fkey` FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `member_workout_plans` ADD CONSTRAINT `member_workout_plans_created_by_id_fkey` FOREIGN KEY (`created_by_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE `member_workout_sessions` ADD CONSTRAINT `member_workout_sessions_gym_id_fkey` FOREIGN KEY (`gym_id`) REFERENCES `gyms`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `member_workout_sessions` ADD CONSTRAINT `member_workout_sessions_member_id_fkey` FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `member_workout_sessions` ADD CONSTRAINT `member_workout_sessions_workout_plan_id_fkey` FOREIGN KEY (`workout_plan_id`) REFERENCES `member_workout_plans`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `member_workout_sessions` ADD CONSTRAINT `member_workout_sessions_created_by_id_fkey` FOREIGN KEY (`created_by_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE `member_assessments` ADD CONSTRAINT `member_assessments_gym_id_fkey` FOREIGN KEY (`gym_id`) REFERENCES `gyms`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `member_assessments` ADD CONSTRAINT `member_assessments_member_id_fkey` FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `member_assessments` ADD CONSTRAINT `member_assessments_created_by_id_fkey` FOREIGN KEY (`created_by_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE `member_progress_events` ADD CONSTRAINT `member_progress_events_gym_id_fkey` FOREIGN KEY (`gym_id`) REFERENCES `gyms`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `member_progress_events` ADD CONSTRAINT `member_progress_events_member_id_fkey` FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE `member_progress_audit_logs` ADD CONSTRAINT `member_progress_audit_logs_gym_id_fkey` FOREIGN KEY (`gym_id`) REFERENCES `gyms`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `member_progress_audit_logs` ADD CONSTRAINT `member_progress_audit_logs_member_id_fkey` FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `member_progress_audit_logs` ADD CONSTRAINT `member_progress_audit_logs_actor_id_fkey` FOREIGN KEY (`actor_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;