<?php

use yii\db\Migration;

/**
 * Creates diet_plans and diet_plan_meals tables for the diet builder module.
 */
class m240001_000009_create_diet_plans_table extends Migration
{
    public function safeUp(): void
    {
        // --- diet_plans ---
        $this->createTable('{{%diet_plans}}', [
            'id'            => $this->primaryKey()->unsigned()->comment('Primary key'),
            'customer_id'   => $this->integer()->unsigned()->notNull()->comment('FK customers'),
            'title'         => $this->string(200)->notNull()->comment('Plan title'),
            'description'   => $this->text()->null()->comment('Plan description / goals'),
            'start_date'    => $this->date()->null()->comment('Plan start date'),
            'end_date'      => $this->date()->null()->comment('Plan end date'),
            'total_calories'=> $this->decimal(8, 2)->notNull()->defaultValue(0.00)->comment('Calculated total kcal/day'),
            'total_protein' => $this->decimal(8, 2)->notNull()->defaultValue(0.00)->comment('Total protein g/day'),
            'total_carbs'   => $this->decimal(8, 2)->notNull()->defaultValue(0.00)->comment('Total carbs g/day'),
            'total_fats'    => $this->decimal(8, 2)->notNull()->defaultValue(0.00)->comment('Total fats g/day'),
            'is_template'   => $this->tinyInteger(1)->unsigned()->notNull()->defaultValue(0)->comment('1=reusable template'),
            'template_name' => $this->string(150)->null()->comment('Template name if is_template=1'),
            'status'        => "ENUM('draft','active','archived') NOT NULL DEFAULT 'draft' COMMENT 'Plan status'",
            'created_by'    => $this->integer()->unsigned()->null()->comment('Nutritionist user ID'),
            'created_at'    => $this->integer()->unsigned()->notNull()->comment('Unix timestamp'),
            'updated_at'    => $this->integer()->unsigned()->notNull()->comment('Unix timestamp'),
        ], 'ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT="Diet plans for customers"');

        $this->createIndex('idx_dp_customer', '{{%diet_plans}}', 'customer_id');
        $this->createIndex('idx_dp_status', '{{%diet_plans}}', 'status');
        $this->createIndex('idx_dp_template', '{{%diet_plans}}', 'is_template');
        $this->createIndex('idx_dp_created_by', '{{%diet_plans}}', 'created_by');

        $this->addForeignKey('fk_dp_customer', '{{%diet_plans}}', 'customer_id', '{{%customers}}', 'id', 'CASCADE', 'CASCADE');

        // --- diet_plan_meals ---
        $this->createTable('{{%diet_plan_meals}}', [
            'id'              => $this->primaryKey()->unsigned()->comment('Primary key'),
            'diet_plan_id'    => $this->integer()->unsigned()->notNull()->comment('FK diet_plans'),
            'meal_type'       => "ENUM('breakfast','morning_snack','lunch','evening_snack','dinner','bedtime_snack') NOT NULL COMMENT 'Meal category'",
            'food_library_id' => $this->integer()->unsigned()->null()->comment('FK food_library; NULL for manual entry'),
            'food_name'       => $this->string(200)->null()->comment('Manual food name (if food_library_id is NULL)'),
            'quantity'        => $this->decimal(8, 2)->notNull()->defaultValue(100.00)->comment('Quantity in grams/ml'),
            'unit'            => $this->string(20)->notNull()->defaultValue('g')->comment('Unit: g, ml, piece, tsp, tbsp'),
            'calories'        => $this->decimal(8, 2)->notNull()->defaultValue(0.00)->comment('Calculated kcal for this item'),
            'protein'         => $this->decimal(8, 2)->notNull()->defaultValue(0.00)->comment('Protein g'),
            'carbs'           => $this->decimal(8, 2)->notNull()->defaultValue(0.00)->comment('Carbs g'),
            'fats'            => $this->decimal(8, 2)->notNull()->defaultValue(0.00)->comment('Fats g'),
            'notes'           => $this->string(300)->null()->comment('Preparation notes'),
            'sort_order'      => $this->smallInteger()->unsigned()->notNull()->defaultValue(0)->comment('Display order within meal type'),
        ], 'ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT="Individual meal items in a diet plan"');

        $this->createIndex('idx_dpm_plan', '{{%diet_plan_meals}}', 'diet_plan_id');
        $this->createIndex('idx_dpm_food', '{{%diet_plan_meals}}', 'food_library_id');
        $this->createIndex('idx_dpm_meal_type', '{{%diet_plan_meals}}', 'meal_type');

        $this->addForeignKey('fk_dpm_plan', '{{%diet_plan_meals}}', 'diet_plan_id', '{{%diet_plans}}', 'id', 'CASCADE', 'CASCADE');
        $this->addForeignKey('fk_dpm_food', '{{%diet_plan_meals}}', 'food_library_id', '{{%food_library}}', 'id', 'SET NULL', 'CASCADE');
    }

    public function safeDown(): void
    {
        $this->dropForeignKey('fk_dpm_plan', '{{%diet_plan_meals}}');
        $this->dropForeignKey('fk_dpm_food', '{{%diet_plan_meals}}');
        $this->dropTable('{{%diet_plan_meals}}');

        $this->dropForeignKey('fk_dp_customer', '{{%diet_plans}}');
        $this->dropTable('{{%diet_plans}}');
    }
}
