<?php

namespace console\migrations;

use yii\db\Migration;

/**
 * Create tables for sports lesson booking system
 */
class m250801_120000_create_booking_system_tables extends Migration
{
    /**
     * {@inheritdoc}
     */
    public function safeUp()
    {
        // Create instructors table if it doesn't exist
        if (!$this->db->schema->getTableSchema('{{%instructors}}', true)) {
            $this->createTable('{{%instructors}}', [
                'id' => $this->primaryKey(),
                'name' => $this->string(100)->notNull(),
                'email' => $this->string(100)->notNull()->unique(),
                'phone' => $this->string(20),
                'specialization' => $this->text()->comment('Comma-separated list of specializations'),
                'hourly_rate' => $this->decimal(10, 2)->defaultValue(0),
                'bio' => $this->text(),
                'avatar' => $this->string(255),
                'status' => $this->string(20)->defaultValue('active'),
                'created_at' => $this->timestamp()->defaultExpression('CURRENT_TIMESTAMP'),
                'updated_at' => $this->timestamp()->defaultExpression('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'),
            ]);
        }

        // Create sports table if it doesn't exist
        if (!$this->db->schema->getTableSchema('{{%sports}}', true)) {
            $this->createTable('{{%sports}}', [
                'id' => $this->primaryKey(),
                'name' => $this->string(100)->notNull(),
                'description' => $this->text(),
                'duration_minutes' => $this->integer()->defaultValue(60),
                'max_participants' => $this->integer()->defaultValue(1),
                'price' => $this->decimal(10, 2)->defaultValue(0),
                'color' => $this->string(7)->defaultValue('#007bff')->comment('Hex color code for calendar display'),
                'status' => $this->string(20)->defaultValue('active'),
                'created_at' => $this->timestamp()->defaultExpression('CURRENT_TIMESTAMP'),
                'updated_at' => $this->timestamp()->defaultExpression('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'),
            ]);
        }

        // Create instructor_sports junction table if it doesn't exist
        if (!$this->db->schema->getTableSchema('{{%instructor_sports}}', true)) {
            $this->createTable('{{%instructor_sports}}', [
                'id' => $this->primaryKey(),
                'instructor_id' => $this->integer()->notNull(),
                'sport_id' => $this->integer()->notNull(),
                'created_at' => $this->timestamp()->defaultExpression('CURRENT_TIMESTAMP'),
            ]);

            // Add foreign keys
            $this->addForeignKey(
                'fk-instructor_sports-instructor_id',
                '{{%instructor_sports}}',
                'instructor_id',
                '{{%instructors}}',
                'id',
                'CASCADE'
            );

            $this->addForeignKey(
                'fk-instructor_sports-sport_id',
                '{{%instructor_sports}}',
                'sport_id',
                '{{%sports}}',
                'id',
                'CASCADE'
            );

            // Add unique constraint
            $this->createIndex(
                'idx-instructor_sports-unique',
                '{{%instructor_sports}}',
                ['instructor_id', 'sport_id'],
                true
            );
        }

        // Create instructor_availability table if it doesn't exist
        if (!$this->db->schema->getTableSchema('{{%instructor_availability}}', true)) {
            $this->createTable('{{%instructor_availability}}', [
                'id' => $this->primaryKey(),
                'instructor_id' => $this->integer()->notNull(),
                'day_of_week' => "ENUM('monday', 'tuesday', 'wednesday', 'thursday', 'friday', 'saturday', 'sunday')",
                'start_time' => $this->time()->notNull(),
                'end_time' => $this->time()->notNull(),
                'is_available' => $this->boolean()->defaultValue(true),
                'created_at' => $this->timestamp()->defaultExpression('CURRENT_TIMESTAMP'),
                'updated_at' => $this->timestamp()->defaultExpression('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'),
            ]);

            // Add foreign key
            $this->addForeignKey(
                'fk-instructor_availability-instructor_id',
                '{{%instructor_availability}}',
                'instructor_id',
                '{{%instructors}}',
                'id',
                'CASCADE'
            );

            // Add index for efficient lookups
            $this->createIndex(
                'idx-instructor_availability-lookup',
                '{{%instructor_availability}}',
                ['instructor_id', 'day_of_week', 'is_available']
            );
        }

        // Create bookings table if it doesn't exist
        if (!$this->db->schema->getTableSchema('{{%bookings}}', true)) {
            $this->createTable('{{%bookings}}', [
                'id' => $this->primaryKey(),
                'booking_reference' => $this->string(50)->notNull()->unique(),
                'customer_name' => $this->string(100)->notNull(),
                'customer_email' => $this->string(100)->notNull(),
                'customer_phone' => $this->string(20),
                'sport_id' => $this->integer()->notNull(),
                'instructor_id' => $this->integer()->notNull(),
                'lesson_date' => $this->date()->notNull(),
                'start_time' => $this->time()->notNull(),
                'duration_minutes' => $this->integer()->notNull()->defaultValue(60),
                'participants' => $this->integer()->notNull()->defaultValue(1),
                'skill_level' => "ENUM('beginner', 'intermediate', 'advanced', 'expert')",
                'special_requests' => $this->text(),
                'price' => $this->decimal(10, 2)->notNull(),
                'status' => "ENUM('pending', 'confirmed', 'completed', 'cancelled', 'no_show') DEFAULT 'pending'",
                'payment_status' => "ENUM('pending', 'paid', 'refunded', 'partial_refund') DEFAULT 'pending'",
                'created_at' => $this->timestamp()->defaultExpression('CURRENT_TIMESTAMP'),
                'updated_at' => $this->timestamp()->defaultExpression('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'),
            ]);

            // Add foreign keys
            $this->addForeignKey(
                'fk-bookings-sport_id',
                '{{%bookings}}',
                'sport_id',
                '{{%sports}}',
                'id',
                'RESTRICT'
            );

            $this->addForeignKey(
                'fk-bookings-instructor_id',
                '{{%bookings}}',
                'instructor_id',
                '{{%instructors}}',
                'id',
                'RESTRICT'
            );

            // Add indexes for efficient queries
            $this->createIndex('idx-bookings-lesson_date', '{{%bookings}}', 'lesson_date');
            $this->createIndex('idx-bookings-instructor_date', '{{%bookings}}', ['instructor_id', 'lesson_date']);
            $this->createIndex('idx-bookings-status', '{{%bookings}}', 'status');
            $this->createIndex('idx-bookings-customer_email', '{{%bookings}}', 'customer_email');
        }

        // Create booking_history table if it doesn't exist
        if (!$this->db->schema->getTableSchema('{{%booking_history}}', true)) {
            $this->createTable('{{%booking_history}}', [
                'id' => $this->primaryKey(),
                'booking_id' => $this->integer()->notNull(),
                'action' => $this->string(50)->notNull(),
                'old_values' => $this->json(),
                'new_values' => $this->json(),
                'changed_by' => $this->string(100),
                'notes' => $this->text(),
                'created_at' => $this->timestamp()->defaultExpression('CURRENT_TIMESTAMP'),
            ]);

            // Add foreign key
            $this->addForeignKey(
                'fk-booking_history-booking_id',
                '{{%booking_history}}',
                'booking_id',
                '{{%bookings}}',
                'id',
                'CASCADE'
            );

            // Add index for efficient lookups
            $this->createIndex(
                'idx-booking_history-booking_id',
                '{{%booking_history}}',
                ['booking_id', 'created_at']
            );
        }

        echo "Booking system tables created successfully!\n";
    }

    /**
     * {@inheritdoc}
     */
    public function safeDown()
    {
        // Drop foreign keys first
        $this->dropForeignKey('fk-booking_history-booking_id', '{{%booking_history}}');
        $this->dropForeignKey('fk-instructor_availability-instructor_id', '{{%instructor_availability}}');
        $this->dropForeignKey('fk-bookings-sport_id', '{{%bookings}}');
        $this->dropForeignKey('fk-bookings-instructor_id', '{{%bookings}}');
        $this->dropForeignKey('fk-instructor_sports-sport_id', '{{%instructor_sports}}');
        $this->dropForeignKey('fk-instructor_sports-instructor_id', '{{%instructor_sports}}');

        // Drop tables
        $this->dropTable('{{%booking_history}}');
        $this->dropTable('{{%instructor_availability}}');
        $this->dropTable('{{%bookings}}');
        $this->dropTable('{{%instructor_sports}}');
        $this->dropTable('{{%sports}}');
        $this->dropTable('{{%instructors}}');
    }
}
