<?php

namespace console\migrations;

use yii\db\Migration;

/**
 * Class m250801_140000_clean_booking_system_setup
 * 
 * Cleanly sets up the booking system by dropping existing tables and recreating them
 */
class m250801_140000_clean_booking_system_setup extends Migration
{
    /**
     * {@inheritdoc}
     */
    public function safeUp()
    {
        // Drop tables in reverse order to respect foreign key constraints
        $tablesToDrop = [
            'booking_history',
            'bookings', 
            'instructor_availability',
            'instructor_sports',
            'sports',
            'instructors'
        ];

        foreach ($tablesToDrop as $table) {
            if ($this->db->schema->getTableSchema($table, true)) {
                echo "Dropping existing table: $table\n";
                $this->dropTable($table);
            }
        }

        // Create instructors table
        $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
        $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
        $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 for instructor_sports
        $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
        $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 for instructor_availability
        $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
        $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 for bookings
        $this->addForeignKey(
            'fk-bookings-sport_id',
            'bookings',
            'sport_id',
            'sports',
            'id',
            'RESTRICT'
        );

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

        // Create 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 for audit trail
        $this->createTable('booking_history', [
            'id' => $this->primaryKey(),
            'booking_id' => $this->integer()->notNull(),
            'action' => $this->string(50)->notNull(),
            'old_values' => $this->text()->comment('JSON string of old values'),
            'new_values' => $this->text()->comment('JSON string of new values'),
            'changed_by' => $this->string(100),
            'notes' => $this->text(),
            'created_at' => $this->timestamp()->defaultExpression('CURRENT_TIMESTAMP'),
        ]);

        // Add foreign key for booking_history
        $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 tables in reverse order to respect foreign key constraints
        $this->dropTable('booking_history');
        $this->dropTable('bookings');
        $this->dropTable('instructor_availability');
        $this->dropTable('instructor_sports');
        $this->dropTable('sports');
        $this->dropTable('instructors');
        
        echo "Booking system tables dropped successfully!\n";
    }
}
