<?php
namespace app\command;

use libs\db\Db;
use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;
use Symfony\Component\Console\Input\InputArgument;

class CreateWmsTableCommand extends Command
{
    protected function configure()
    {
        $this
            // 命令的名称 （"php console_command" 后面的部分）
            ->setName('table:create')
            // 运行 "php console_command list" 时的简短描述
            ->setDescription('Create new model')
            // 运行命令时使用 "--help" 选项时的完整命令描述
            ->setHelp('This command allow you to create models...')
            // 配置一个参数m
            ->addArgument('name', InputArgument::REQUIRED, 'what\'s model you want to create ?')
            // 配置一个可选参数
            ->addArgument('optional_argument', InputArgument::OPTIONAL, 'this is a optional argument');
    }

    protected function execute(InputInterface $input, OutputInterface $output)
    {
        $name = $input->getArgument('name');
        if($name == 'wms'){
            $allTables = $this->getDbTables();
            $this->hf_mes_customer($allTables);
            $this->hf_mes_wms_material($allTables);
            $this->hf_mes_wms_material_barcode($allTables);
            $this->hf_mes_wms_storage_area_category($allTables);
            $this->hf_mes_wms_storage_area($allTables);
            $this->hf_mes_wms_storage_location($allTables);
        }

        $output->writeln('created ' . $input->getArgument('name') . ' table success !');

        return 1;
    }

    /**
     * 获取数据库表名
     * @return array
     */
    private function getDbTables(): array
    {
        $sql  = <<<SQL
SELECT tablename  
FROM pg_tables  
WHERE schemaname = 'public' AND tablename NOT IN ('pg_catalog', 'information_schema')
SQL;
        $ret  = Db::query($sql);
        $data = [];
        if (!empty($ret)) {
            foreach ($ret as $item) {
                array_push($data, $item['tablename']);
            }
        }

        return $data;
    }

    /**
     * 客户表
     * @param string $table
     * @return array
     */
    private function hf_mes_customer($allTables, $table='hf_mes_customer')
    {
        if(in_array($table, $allTables)){
            return false;
        }

        $sql = <<<SQL
CREATE TABLE IF NOT EXISTS "{$table}" (
"id" serial PRIMARY KEY,
"name" varchar(45) COLLATE "pg_catalog"."default" NOT NULL,
"address" varchar(200) COLLATE "pg_catalog"."default",
"contact_person" varchar(45) COLLATE "pg_catalog"."default",
"contact_number" varchar(20) COLLATE "pg_catalog"."default",
"email" varchar(45) COLLATE "pg_catalog"."default",
"city" varchar(30) COLLATE "pg_catalog"."default",
"remark" text COLLATE "pg_catalog"."default", 
"sort" int4 NOT NULL DEFAULT 0,
"create_time" TIMESTAMP(6),
"update_time" TIMESTAMP(6),
"delete_time" TIMESTAMP(6)
)
SQL;

        $row = Db::query($sql);

        $comment = <<<SQL
CREATE INDEX idx_{$table}_name ON {$table} (name);
CREATE INDEX idx_{$table}_create_time ON {$table} (create_time);
COMMENT ON COLUMN {$table}.name IS '客户名称';
COMMENT ON COLUMN {$table}.address IS '客户地址';
COMMENT ON COLUMN {$table}.contact_person IS '联系人姓名';
COMMENT ON COLUMN {$table}.contact_number IS '联系人电话';
COMMENT ON COLUMN {$table}.email IS '联系人邮箱';
COMMENT ON COLUMN {$table}.city IS '城市';
COMMENT ON COLUMN {$table}.remark IS '备注';
COMMENT ON COLUMN {$table}.sort IS '排序';
COMMENT ON COLUMN {$table}.create_time IS '创建时间';
COMMENT ON COLUMN {$table}.update_time IS '修改时间';
COMMENT ON COLUMN {$table}.delete_time IS '删除时间';
SQL;

        $comment_arr = explode(';', $comment);

        foreach ($comment_arr as $item) {
            if (!empty($item)) {
                if (strpos($item, 'idx') !== false) {
                    $idx = Db::query("SELECT tablename FROM pg_indexes WHERE indexname='{$item}';");
                    if (!empty($idx)) {
                        Db::query("DROP INDEX {$item};");
                    }
                }

                Db::query($item);
            }
        }

        return $row;
    }

    /**
     * WMS仓库物料表
     * @param string $table
     * @return array
     */
    private function hf_mes_wms_material($allTables, $table='hf_mes_wms_material')
    {
        if(in_array($table, $allTables)){
            return false;
        }

        $sql = <<<SQL
CREATE TABLE IF NOT EXISTS "{$table}" (
"id" serial PRIMARY KEY,
"bom_source_category_id" int4 NOT NULL DEFAULT 0,
"unit_id" int4 NOT NULL DEFAULT 0,
"supplier_id" int4 NOT NULL DEFAULT 0,
"user_id" int4 NOT NULL DEFAULT 0,
"code" varchar(45) COLLATE "pg_catalog"."default" NOT NULL,
"name" varchar(45) COLLATE "pg_catalog"."default" NOT NULL,
"brand" varchar(45) COLLATE "pg_catalog"."default",
"remark" text COLLATE "pg_catalog"."default", 
"create_time" TIMESTAMP(6),
"update_time" TIMESTAMP(6),
"delete_time" TIMESTAMP(6)
)
SQL;

        $row = Db::query($sql);

        $comment = <<<SQL
CREATE INDEX idx_{$table}_create_time ON {$table} (create_time);
CREATE INDEX idx_{$table}_code ON {$table} (code);
CREATE INDEX idx_{$table}_name ON {$table} (name);
COMMENT ON COLUMN {$table}.bom_source_category_id IS '物料类别ID';
COMMENT ON COLUMN {$table}.unit_id IS '单位ID';
COMMENT ON COLUMN {$table}.supplier_id IS '供应商ID';
COMMENT ON COLUMN {$table}.user_id IS '创建人ID';
COMMENT ON COLUMN {$table}.code IS '物料编码';
COMMENT ON COLUMN {$table}.name IS '物料名称';
COMMENT ON COLUMN {$table}.brand IS '品牌';
COMMENT ON COLUMN {$table}.remark IS '备注';
COMMENT ON COLUMN {$table}.create_time IS '创建时间';
COMMENT ON COLUMN {$table}.update_time IS '修改时间';
COMMENT ON COLUMN {$table}.delete_time IS '删除时间';
SQL;

        $comment_arr = explode(';', $comment);

        foreach ($comment_arr as $item) {
            if (!empty($item)) {
                if (strpos($item, 'idx') !== false) {
                    $idx = Db::query("SELECT tablename FROM pg_indexes WHERE indexname='{$item}';");
                    if (!empty($idx)) {
                        Db::query("DROP INDEX {$item};");
                    }
                }

                Db::query($item);
            }
        }

        return $row;
    }

    /**
     * WMS仓库物料条形码
     * @param string $table
     * @return array
     */
    private function hf_mes_wms_material_barcode($allTables, $table='hf_mes_wms_material_barcode')
    {
        if(in_array($table, $allTables)){
            return false;
        }

        $sql = <<<SQL
CREATE TABLE IF NOT EXISTS "{$table}" (
"id" serial PRIMARY KEY,
"wms_material_id" int4 NOT NULL DEFAULT 0,
"barcode" varchar(100) COLLATE "pg_catalog"."default" NOT NULL,
"delete_time" TIMESTAMP(6)
)
SQL;

        $row = Db::query($sql);

        $comment = <<<SQL
CREATE INDEX idx_{$table}_barcode ON {$table} (barcode);
COMMENT ON COLUMN {$table}.wms_material_id IS '物料ID';
COMMENT ON COLUMN {$table}.barcode IS '条形码';
COMMENT ON COLUMN {$table}.delete_time IS '删除时间';
SQL;

        $comment_arr = explode(';', $comment);

        foreach ($comment_arr as $item) {
            if (!empty($item)) {
                if (strpos($item, 'idx') !== false) {
                    $idx = Db::query("SELECT tablename FROM pg_indexes WHERE indexname='{$item}';");
                    if (!empty($idx)) {
                        Db::query("DROP INDEX {$item};");
                    }
                }

                Db::query($item);
            }
        }

        return $row;
    }

    /**
     * WMS库区类别
     * @param string $table
     * @return array
     */
    private function hf_mes_wms_storage_area_category($allTables, $table='hf_mes_wms_storage_area_category')
    {
        if(in_array($table, $allTables)){
            return false;
        }

        $sql = <<<SQL
CREATE TABLE IF NOT EXISTS "{$table}" (
"id" serial PRIMARY KEY,
"name" varchar(45) COLLATE "pg_catalog"."default" NOT NULL,
"remark" text COLLATE "pg_catalog"."default",
"create_time" TIMESTAMP(6),
"update_time" TIMESTAMP(6),
"delete_time" TIMESTAMP(6)
)
SQL;

        $row = Db::query($sql);

        $comment = <<<SQL
COMMENT ON COLUMN {$table}.name IS 'WMS库区类别名称';
COMMENT ON COLUMN {$table}.remark IS '备注';
COMMENT ON COLUMN {$table}.create_time IS '创建时间';
COMMENT ON COLUMN {$table}.update_time IS '修改时间';
COMMENT ON COLUMN {$table}.delete_time IS '删除时间';
SQL;

        $comment_arr = explode(';', $comment);

        foreach ($comment_arr as $item) {
            if (!empty($item)) {
                if (strpos($item, 'idx') !== false) {
                    $idx = Db::query("SELECT tablename FROM pg_indexes WHERE indexname='{$item}';");
                    if (!empty($idx)) {
                        Db::query("DROP INDEX {$item};");
                    }
                }

                Db::query($item);
            }
        }

        return $row;
    }

    /**
     * WMS库区
     * @param string $table
     * @return array
     */
    private function hf_mes_wms_storage_area($allTables, $table='hf_mes_wms_storage_area')
    {
        if(in_array($table, $allTables)){
            return false;
        }

        $sql = <<<SQL
CREATE TABLE IF NOT EXISTS "{$table}" (
"id" serial PRIMARY KEY,
"storage_area_category_id" int4 NOT NULL DEFAULT 0,
"user_id" int4 NOT NULL DEFAULT 0,
"area_id" int4 NOT NULL DEFAULT 0,
"line_id" int4 NOT NULL DEFAULT 0,
"code" varchar(45) COLLATE "pg_catalog"."default" NOT NULL,
"name" varchar(45) COLLATE "pg_catalog"."default" NOT NULL,
"status" int2 NOT NULL DEFAULT 0,
"remark" text COLLATE "pg_catalog"."default", 
"create_time" TIMESTAMP(6),
"update_time" TIMESTAMP(6),
"delete_time" TIMESTAMP(6)
)
SQL;

        $row = Db::query($sql);

        $comment = <<<SQL
CREATE INDEX idx_{$table}_create_time ON {$table} (create_time);
CREATE INDEX idx_{$table}_code ON {$table} (code);
CREATE INDEX idx_{$table}_name ON {$table} (name);
COMMENT ON COLUMN {$table}.storage_area_category_id IS '库区类别ID';
COMMENT ON COLUMN {$table}.user_id IS '创建人ID';
COMMENT ON COLUMN {$table}.area_id IS '厂区ID';
COMMENT ON COLUMN {$table}.line_id IS '产线ID';
COMMENT ON COLUMN {$table}.code IS 'WMS库区编码';
COMMENT ON COLUMN {$table}.name IS 'WMS库区名称';
COMMENT ON COLUMN {$table}.status IS '任务状态(0 关闭, 1开启)';
COMMENT ON COLUMN {$table}.remark IS '备注';
COMMENT ON COLUMN {$table}.create_time IS '创建时间';
COMMENT ON COLUMN {$table}.update_time IS '修改时间';
COMMENT ON COLUMN {$table}.delete_time IS '删除时间';
SQL;

        $comment_arr = explode(';', $comment);

        foreach ($comment_arr as $item) {
            if (!empty($item)) {
                if (strpos($item, 'idx') !== false) {
                    $idx = Db::query("SELECT tablename FROM pg_indexes WHERE indexname='{$item}';");
                    if (!empty($idx)) {
                        Db::query("DROP INDEX {$item};");
                    }
                }

                Db::query($item);
            }
        }

        return $row;
    }

    /**
     * WMS库位
     * @param string $table
     * @return array
     */
    private function hf_mes_wms_storage_location($allTables, $table='hf_mes_wms_storage_location')
    {
        if(in_array($table, $allTables)){
            return false;
        }

        $sql = <<<SQL
CREATE TABLE IF NOT EXISTS "{$table}" (
"id" serial PRIMARY KEY,
"storage_area_id" int4 NOT NULL DEFAULT 0,
"user_id" int4 NOT NULL DEFAULT 0,
"area_id" int4 NOT NULL DEFAULT 0,
"line_id" int4 NOT NULL DEFAULT 0,
"code" varchar(45) COLLATE "pg_catalog"."default" NOT NULL,
"name" varchar(45) COLLATE "pg_catalog"."default" NOT NULL,
"status" int2 NOT NULL DEFAULT 0,
"length" float8,
"width" float8,
"hight" float8,
"volume" float8,
"load" float8,
"capacity" float8,
"roadway_num" float8,
"shelf_num" float8,
"layer_num" float8,
"tag_num" float8,
"remark" text COLLATE "pg_catalog"."default", 
"create_time" TIMESTAMP(6),
"update_time" TIMESTAMP(6),
"delete_time" TIMESTAMP(6)
)
SQL;

        $row = Db::query($sql);

        $comment = <<<SQL
CREATE INDEX idx_{$table}_create_time ON {$table} (create_time);
CREATE INDEX idx_{$table}_code ON {$table} (code);
CREATE INDEX idx_{$table}_name ON {$table} (name);
COMMENT ON COLUMN {$table}.storage_area_id IS '库区ID';
COMMENT ON COLUMN {$table}.user_id IS '创建人ID';
COMMENT ON COLUMN {$table}.area_id IS '厂区ID';
COMMENT ON COLUMN {$table}.line_id IS '产线ID';
COMMENT ON COLUMN {$table}.code IS 'WMS库位编码';
COMMENT ON COLUMN {$table}.name IS 'WMS库位名称';
COMMENT ON COLUMN {$table}.status IS '任务状态(0 关闭, 1开启)';
COMMENT ON COLUMN {$table}.length IS '长度';
COMMENT ON COLUMN {$table}.width IS '宽度';
COMMENT ON COLUMN {$table}.hight IS '高度';
COMMENT ON COLUMN {$table}.volume IS '容积';
COMMENT ON COLUMN {$table}.load IS '承重';
COMMENT ON COLUMN {$table}.capacity IS '容量';
COMMENT ON COLUMN {$table}.roadway_num IS '巷道';
COMMENT ON COLUMN {$table}.shelf_num IS '货架号';
COMMENT ON COLUMN {$table}.layer_num IS '层号';
COMMENT ON COLUMN {$table}.tag_num IS '位号';
COMMENT ON COLUMN {$table}.remark IS '备注';
COMMENT ON COLUMN {$table}.create_time IS '创建时间';
COMMENT ON COLUMN {$table}.update_time IS '修改时间';
COMMENT ON COLUMN {$table}.delete_time IS '删除时间';
SQL;

        $comment_arr = explode(';', $comment);

        foreach ($comment_arr as $item) {
            if (!empty($item)) {
                if (strpos($item, 'idx') !== false) {
                    $idx = Db::query("SELECT tablename FROM pg_indexes WHERE indexname='{$item}';");
                    if (!empty($idx)) {
                        Db::query("DROP INDEX {$item};");
                    }
                }

                Db::query($item);
            }
        }

        return $row;
    }
}