芝麻web文件管理V1.00

编辑当前文件:/home2/sdektunc/airport-back/migrations/m240815_090342_create_base_tables.php

<?php

use yii\db\Migration;
use yii\helpers\Json;

/**
 * Class m240815_090342_create_base_tables
 */
class m240815_090342_create_base_tables extends Migration
{
    /**
     * {@inheritdoc}
     */
    public function safeUp()
    {

/*****Los nombres de tablas son todos en singular 
         * Todas las tablas tienen "id" como nombre de clave primaria
         * 
         * 
         */

//Table profile (perfil)
        $this->createTable(
            '{{%profile}}',
            [
                'id' => 'pk',
                'name' => $this->string(200),
                'permissions' => $this->text(),
                'sort_order' => $this->integer(),
                'created' => $this->timestamp(),
                'updated' => $this->timestamp(),
                'trash' => $this->tinyInteger(1),
                'active' => $this->tinyInteger(1)
            ]
        );

//Table user (usuario)
        $this->createTable(
            '{{%user}}',
            [
                'id' => 'pk',                
                'profile_id'=>$this->integer(),
                'name' => $this->string(200),
                'email' => $this->string(255),
                'mobile' => $this->string(200),
                'username' => $this->string(50),
                'password'=>$this->string(200),
                'access_token'=>$this->string(200),
                'refresh_token'=>$this->string(200),
                'recover_token'=>$this->string(200),
                'profile_changes'=>$this->tinyInteger(1),
                'sort_order' => $this->integer(),
                'created' => $this->timestamp(),
                'updated' => $this->timestamp(),
                'trash' => $this->tinyInteger(1),
                'active' => $this->tinyInteger(1)
            ]
        );

$this->execute("

-- -----------------------------------------------------
                    -- Table {{%airline}}
                    -- -----------------------------------------------------
                    CREATE TABLE IF NOT EXISTS {{%airline}} (
                    `id` INT NOT NULL AUTO_INCREMENT,
                    `name` VARCHAR(50) NULL,
                    `logotype` VARCHAR(45) NULL,
                    `created` TIMESTAMP NULL,
                    `updated` TIMESTAMP NULL,
                    `active` TINYINT(1) NULL,
                    `trash` TINYINT(1) NULL,
                    PRIMARY KEY (`id`))
                    ENGINE = InnoDB;

-- -----------------------------------------------------
                    -- Table {{%aircraft}}
                    -- -----------------------------------------------------
                    CREATE TABLE IF NOT EXISTS {{%aircraft}} (
                    `id` INT NOT NULL AUTO_INCREMENT,
                    `airline_id` INT NULL,
                    `name` VARCHAR(45) NULL,
                    `code` VARCHAR(45) NULL,
                    `unit_number` INT(10) NULL,
                    `model` VARCHAR(50) NULL,
                    `capacity` TINYINT(5) NULL,
                    `seat_map` VARCHAR(250) NULL,
                    `seat_distribution` VARCHAR(45) NULL,
                    `created` TIMESTAMP NULL,
                    `updated` TIMESTAMP NULL,
                    `active` TINYINT(1) NULL,
                    `trash` TINYINT(1) NULL,
                    PRIMARY KEY (`id`),
                    INDEX `fk_aircraft_airline_idx` (`airline_id` ASC),
                    CONSTRAINT `fk_aircraft_airline`
                        FOREIGN KEY (`airline_id`)
                        REFERENCES {{%airline}} (`id`)
                        ON DELETE NO ACTION
                        ON UPDATE NO ACTION)
                    ENGINE = InnoDB;

-- -----------------------------------------------------
                    -- Table {{%seat_class}}
                    -- -----------------------------------------------------
                    CREATE TABLE IF NOT EXISTS {{%seat_class}} (
                    `id` INT NOT NULL AUTO_INCREMENT,
                    `aircraft_id` INT NULL,
                    `airline_id` INT NULL,
                    `name` VARCHAR(45) NULL,
                    `price` VARCHAR(45) NULL,
                    `created` TIMESTAMP NULL,
                    `updated` TIMESTAMP NULL,
                    `active` TINYINT(1) NULL,
                    `trash` TINYINT(1) NULL,
                    PRIMARY KEY (`id`),
                    INDEX `fk_seat_class_aircraft1_idx` (`aircraft_id` ASC),
                    INDEX `fk_seat_class_airline1_idx` (`airline_id` ASC),
                    CONSTRAINT `fk_seat_class_aircraft1`
                        FOREIGN KEY (`aircraft_id`)
                        REFERENCES {{%aircraft}} (`id`)
                        ON DELETE NO ACTION
                        ON UPDATE NO ACTION,
                    CONSTRAINT `fk_seat_class_airline1`
                        FOREIGN KEY (`airline_id`)
                        REFERENCES {{%airline}} (`id`)
                        ON DELETE NO ACTION
                        ON UPDATE NO ACTION)
                    ENGINE = InnoDB;

-- -----------------------------------------------------
                    -- Table {{%seat}}
                    -- -----------------------------------------------------
                    CREATE TABLE IF NOT EXISTS {{%seat}} (
                      `id` INT NOT NULL AUTO_INCREMENT,
                      `aircraft_id` INT NULL,
                      `seat_class_id` INT NULL,
                      `airline_id` INT NULL,
                      `seat_number` CHAR(10) NULL,
                      `position` VARCHAR(20) NULL,
                      `row` VARCHAR(45) NULL,
                      `map_x` VARCHAR(45) NULL,
                      `map_y` VARCHAR(45) NULL,
                      `seat_class` CHAR(10) NULL,
                      `created` TIMESTAMP NULL,
                      `updated` TIMESTAMP NULL,
                      `active` TINYINT(1) NULL,
                      `trash` TINYINT(1) NULL,
                      PRIMARY KEY (`id`),
                      INDEX `fk_seat_aircraft1_idx` (`aircraft_id` ASC),
                      INDEX `fk_seat_seat_class1_idx` (`seat_class_id` ASC),
                      INDEX `fk_seat_airline1_idx` (`airline_id` ASC),
                      CONSTRAINT `fk_seat_aircraft1`
                        FOREIGN KEY (`aircraft_id`)
                        REFERENCES {{%aircraft}} (`id`)
                        ON DELETE NO ACTION
                        ON UPDATE NO ACTION,
                      CONSTRAINT `fk_seat_seat_class1`
                        FOREIGN KEY (`seat_class_id`)
                        REFERENCES {{%seat_class}} (`id`)
                        ON DELETE NO ACTION
                        ON UPDATE NO ACTION,
                      CONSTRAINT `fk_seat_airline1`
                        FOREIGN KEY (`airline_id`)
                        REFERENCES {{%airline}} (`id`)
                        ON DELETE NO ACTION
                        ON UPDATE NO ACTION)
                    ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS {{%destination}} (
                        `id` INT NOT NULL AUTO_INCREMENT,
                        `name` VARCHAR(100) NULL,
                        `city` VARCHAR(45) NULL,
                        `country` VARCHAR(45) NULL,
                        `active` TINYINT(1) NULL,
                        `trash` TINYINT(1) NULL,
                        PRIMARY KEY (`id`))
                      ENGINE = InnoDB;

-- -----------------------------------------------------
                    -- Table {{%flight}}
                    -- -----------------------------------------------------
                    CREATE TABLE IF NOT EXISTS {{%flight}} (
                    `id` INT NOT NULL AUTO_INCREMENT,
                    `aircraft_id` INT NULL,
                    `airline_id` INT NULL,
                    `destination_id` INT NULL,
                    `departure_time` TIMESTAMP NULL,
                    `arrival_time` TIMESTAMP NULL,
                    `price` VARCHAR(10) NULL,
                    `created` TIMESTAMP NULL,
                    `updated` TIMESTAMP NULL,
                    `active` TINYINT(1) NULL,
                    `trash` TINYINT(1) NULL,
                    PRIMARY KEY (`id`),
                    INDEX `fk_flight_aircraft1_idx` (`aircraft_id` ASC),
                    INDEX `fk_flight_airline1_idx` (`airline_id` ASC),
                    INDEX `fk_flight_destination1_idx` (`destination_id` ASC),
                    CONSTRAINT `fk_flight_aircraft1`
                        FOREIGN KEY (`aircraft_id`)
                        REFERENCES {{%aircraft}} (`id`)
                        ON DELETE NO ACTION
                        ON UPDATE NO ACTION,
                    CONSTRAINT `fk_flight_airline1`
                        FOREIGN KEY (`airline_id`)
                        REFERENCES {{%airline}} (`id`)
                        ON DELETE NO ACTION
                        ON UPDATE NO ACTION,
                    CONSTRAINT `fk_flight_destination1`
                        FOREIGN KEY (`destination_id`)
                        REFERENCES {{%destination}} (`id`)
                        ON DELETE NO ACTION
                        ON UPDATE NO ACTION)
                    ENGINE = InnoDB;

-- -----------------------------------------------------
                    -- Table {{%customer}}
                    -- -----------------------------------------------------
                    CREATE TABLE IF NOT EXISTS {{%customer}} (
                    `id` INT NOT NULL AUTO_INCREMENT,
                    `name` VARCHAR(150) NULL,
                    `email` VARCHAR(45) NULL,
                    `phone_number` VARCHAR(45) NULL,
                    `customer_type` VARCHAR(20) NULL,
                    `password` VARCHAR(100) NULL,
                    `created` TIMESTAMP NULL,
                    `updated` TIMESTAMP NULL,
                    `active` TINYINT(1) NULL,
                    `trash` TINYINT(1) NULL,
                    PRIMARY KEY (`id`))
                    ENGINE = InnoDB;

-- -----------------------------------------------------
                    -- Table {{%booking}}
                    -- -----------------------------------------------------
                    CREATE TABLE IF NOT EXISTS {{%booking}} (
                    `id` INT NOT NULL AUTO_INCREMENT,
                    `flight_id` INT NULL,
                    `customer_id` INT NULL,
                    `airline_id` INT NULL,
                    `total` VARCHAR(45) NULL,
                    `code` VARCHAR(45) NULL,
                    `status` VARCHAR(45) NULL,
                    `date` TIMESTAMP NULL,
                    `created` TIMESTAMP NULL,
                    `updated` TIMESTAMP NULL,
                    `active` TINYINT(1) NULL,
                    `trash` TINYINT(1) NULL,
                    PRIMARY KEY (`id`),
                    INDEX `fk_booking_flight1_idx` (`flight_id` ASC),
                    INDEX `fk_booking_customer1_idx` (`customer_id` ASC),
                    INDEX `fk_booking_airline1_idx` (`airline_id` ASC),
                    CONSTRAINT `fk_booking_flight1`
                        FOREIGN KEY (`flight_id`)
                        REFERENCES {{%flight}} (`id`)
                        ON DELETE NO ACTION
                        ON UPDATE NO ACTION,
                    CONSTRAINT `fk_booking_customer1`
                        FOREIGN KEY (`customer_id`)
                        REFERENCES {{%customer}} (`id`)
                        ON DELETE NO ACTION
                        ON UPDATE NO ACTION,
                    CONSTRAINT `fk_booking_airline1`
                        FOREIGN KEY (`airline_id`)
                        REFERENCES {{%airline}} (`id`)
                        ON DELETE NO ACTION
                        ON UPDATE NO ACTION)
                    ENGINE = InnoDB;

-- -----------------------------------------------------
                    -- Table {{%payment}}
                    -- -----------------------------------------------------
                    CREATE TABLE IF NOT EXISTS {{%payment}} (
                    `id` INT NOT NULL AUTO_INCREMENT,
                    `booking_id` INT NULL,
                    `customer_id` INT NULL,
                    `amount` VARCHAR(10) NULL,
                    `payment_method` VARCHAR(45) NULL,
                    `payment_reference` VARCHAR(45) NULL,
                    `payment_date` TIMESTAMP NULL,
                    `trash` TINYINT(1) NULL,
                    PRIMARY KEY (`id`),
                    INDEX `fk_payment_booking1_idx` (`booking_id` ASC),
                    INDEX `fk_payment_customer1_idx` (`customer_id` ASC),
                    CONSTRAINT `fk_payment_booking1`
                        FOREIGN KEY (`booking_id`)
                        REFERENCES {{%booking}} (`id`)
                        ON DELETE NO ACTION
                        ON UPDATE NO ACTION,
                    CONSTRAINT `fk_payment_customer1`
                        FOREIGN KEY (`customer_id`)
                        REFERENCES {{%customer}} (`id`)
                        ON DELETE NO ACTION
                        ON UPDATE NO ACTION)
                    ENGINE = InnoDB;

-- -----------------------------------------------------
                    -- Table {{%booking_item}}
                    -- -----------------------------------------------------
                    CREATE TABLE IF NOT EXISTS {{%booking_item}} (
                    `id` INT NOT NULL AUTO_INCREMENT,
                    `booking_id` INT NULL,
                    `seat_id` INT NULL,
                    PRIMARY KEY (`id`),
                    INDEX `fk_booking_items_booking1_idx` (`booking_id` ASC),
                    INDEX `fk_booking_items_seat1_idx` (`seat_id` ASC),
                    CONSTRAINT `fk_booking_items_booking1`
                        FOREIGN KEY (`booking_id`)
                        REFERENCES {{%booking}} (`id`)
                        ON DELETE NO ACTION
                        ON UPDATE NO ACTION,
                    CONSTRAINT `fk_booking_items_seat1`
                        FOREIGN KEY (`seat_id`)
                        REFERENCES {{%seat}} (`id`)
                        ON DELETE NO ACTION
                        ON UPDATE NO ACTION)
                    ENGINE = InnoDB;

-- -----------------------------------------------------
                    -- Table {{%flight_rate}}
                    -- -----------------------------------------------------
                    CREATE TABLE IF NOT EXISTS {{%flight_rate}} (
                      `id` INT NOT NULL AUTO_INCREMENT,
                      `flight_ratecol` VARCHAR(45) NULL,
                      `seatclass_id` INT NULL,
                      `flight_id` INT NULL,
                      `price` VARCHAR(45) NULL,
                      PRIMARY KEY (`id`),
                      INDEX `fk_flight_rate_seat_class1_idx` (`seatclass_id` ASC),
                      INDEX `fk_flight_rate_flight1_idx` (`flight_id` ASC),
                      CONSTRAINT `fk_flight_rate_seat_class1`
                        FOREIGN KEY (`seatclass_id`)
                        REFERENCES {{%seat_class}} (`id`)
                        ON DELETE NO ACTION
                        ON UPDATE NO ACTION,
                      CONSTRAINT `fk_flight_rate_flight1`
                        FOREIGN KEY (`flight_id`)
                        REFERENCES {{%flight}} (`id`)
                        ON DELETE NO ACTION
                        ON UPDATE NO ACTION)
                    ENGINE = InnoDB;

");

$this->createIndex('user_profile_idx','{{%user}}','profile_id');
        $this->addForeignKey('user_profile_fk','{{%user}}','profile_id','{{%profile}}','id','NO ACTION','NO ACTION');

$this->insert('{{%profile}}',['name'=>'Administrador','permissions'=>$this->encodePermissions(),'trash'=>0,'active'=>1]);
        $this->insert('{{%user}}',['profile_id'=>1,'mobile'=>'--','name'=>'Administrador','username'=>'ejercicio','email'=>'app@domain.com','password'=>Yii::$app->security->generatePasswordHash('ejercicio'),'active'=>1,'trash'=>0]);

}

private function encodePermissions(){

$permissions=[];
        $system_permissions=Yii::$app->params['permissions'];
        
        foreach($system_permissions as $permission){

$permissions[]=['controller'=>$permission['controller'],'actions'=>array_keys($permission['actions'])];

}

$encoded_permissions=Json::encode($permissions);

return $encoded_permissions;

}

/**
     * {@inheritdoc}
     */
    public function safeDown()
    {

return false;

}

/*
    // Use up()/down() to run migration code without a transaction.
    public function up()
    {

}

public function down()
    {
        echo "m240530_050342_create_base_tables cannot be reverted.\n";

return false;
    }
    */
}