<?php

use yii\db\Migration;

/**
 * Creates unified customers table supporting Individual, Business, and Gym Members.
 */
class m240001_000002_create_customers_table extends Migration
{
    public function safeUp(): void
    {
        $this->createTable('{{%customers}}', [
            'id'                 => $this->primaryKey()->unsigned()->comment('Primary key'),
            'name'               => $this->string(150)->notNull()->comment('Customer full name'),
            'phone'              => $this->string(20)->notNull()->comment('Phone number'),
            'email'              => $this->string(150)->null()->comment('Email address'),
            'address'            => $this->text()->null()->comment('Full address'),
            'customer_type'      => "ENUM('individual','business') NOT NULL DEFAULT 'individual' COMMENT 'Customer type'",
            'company_name'       => $this->string(200)->null()->comment('Company/gym name for business type'),
            'gst_number'         => $this->string(20)->null()->comment('GST number for business invoicing'),
            'parent_customer_id' => $this->integer()->unsigned()->null()->comment('For gym members: parent gym customer_id'),
            'notes'              => $this->text()->null()->comment('Internal notes'),
            'is_active'          => $this->tinyInteger(1)->unsigned()->notNull()->defaultValue(1)->comment('1=active, 0=inactive'),
            'lead_id'            => $this->integer()->unsigned()->null()->comment('Source lead if converted'),
            '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="Unified customer table: individual, business, gym members"');

        // Performance indexes
        $this->createIndex('idx_customers_phone', '{{%customers}}', 'phone');
        $this->createIndex('idx_customers_email', '{{%customers}}', 'email');
        $this->createIndex('idx_customers_type', '{{%customers}}', 'customer_type');
        $this->createIndex('idx_customers_parent', '{{%customers}}', 'parent_customer_id');
        $this->createIndex('idx_customers_lead', '{{%customers}}', 'lead_id');
        $this->createIndex('idx_customers_active', '{{%customers}}', 'is_active');

        // Self-referencing FK for parent hierarchy (gym → member)
        $this->addForeignKey(
            'fk_customers_parent',
            '{{%customers}}',
            'parent_customer_id',
            '{{%customers}}',
            'id',
            'SET NULL',
            'CASCADE'
        );

        // FK to leads (nullable)
        $this->addForeignKey(
            'fk_customers_lead',
            '{{%customers}}',
            'lead_id',
            '{{%leads}}',
            'id',
            'SET NULL',
            'CASCADE'
        );
    }

    public function safeDown(): void
    {
        $this->dropForeignKey('fk_customers_parent', '{{%customers}}');
        $this->dropForeignKey('fk_customers_lead', '{{%customers}}');
        $this->dropTable('{{%customers}}');
    }
}
