<?php
namespace App\Traits;

defined('BASEPATH') or exit('No direct script access allowed');

trait InventorySchemaTrait
{

    /**
     * Returns the raw array definition of tables
     */
    public function get_vendor_inventory_schema()
    {
        return [
            'inventory_vendor_list' => [
                'fields' => [
                    'id' => ['type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE, 'auto_increment' => TRUE],
                    'user_id' => ['type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE],
                    'shop_id' => ['type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE],
                    'name' => ['type' => 'VARCHAR', 'constraint' => 255],
                    'contact_person' => ['type' => 'VARCHAR', 'constraint' => 255],
                    'phone' => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => TRUE],
                    'email' => ['type' => 'VARCHAR', 'constraint' => 255],
                    'address' => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => TRUE],
                    'created_at' => ['type' => 'DATETIME', 'null' => FALSE]
                ],
                'keys' => [
                    ['id', TRUE],
                    ['user_id', FALSE],
                    ['shop_id', FALSE]
                ]
            ],

            'vendor_inventory_logs' => [
                'fields' => [
                    'id' => ['type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE, 'auto_increment' => TRUE],
                    'user_id' => ['type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE],
                    'shop_id' => ['type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE],
                    'item_id' => ['type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE],
                    'quantity' => ['type' => 'VARCHAR', 'constraint' => 20, 'null' => TRUE],
                    'unit' => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => TRUE],
                    'priority' => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => TRUE],
                    'notes' => ['type' => 'TEXT', 'null' => TRUE],
                    'approved_date' => ['type' => 'DATETIME', 'null' => TRUE],
                    'canceled_date' => ['type' => 'DATETIME', 'null' => TRUE],
                    'created_at' => ['type' => 'DATETIME', 'null' => FALSE],
                    'status' => ['type' => 'ENUM', 'constraint' => ['pending', 'approved', 'canceled'], 'default' => 'pending']
                ],
                'keys' => [
                    ['id', TRUE],
                    ['user_id', FALSE],
                    ['shop_id', FALSE],
                    ['item_id', FALSE],
                    ['status', FALSE]
                ]
            ],

            'vendor_inventory_purchases' => [
                'fields' => [
                    'id' => ['type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE, 'auto_increment' => TRUE],
                    'user_id' => ['type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE],
                    'shop_id' => ['type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE],
                    'vendor_id' => ['type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE],
                    'cat_id' => ['type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE],
                    'item_id' => ['type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE],
                    'stock' => ['type' => 'INT', 'constraint' => 11],
                    'unit_price' => ['type' => 'DECIMAL', 'constraint' => '10,2'],
                    'total_price' => ['type' => 'DECIMAL', 'constraint' => '12,2'],
                    'quantity' => ['type' => 'DECIMAL', 'constraint' => '10,3'],
                    'purchases_date' => ['type' => 'DATE'],
                    'invoice_number' => ['type' => 'VARCHAR', 'constraint' => 100],
                    'unit' => ['type' => 'VARCHAR', 'constraint' => 50],
                    'payment_method' => ['type' => 'VARCHAR', 'constraint' => 50],
                    'payment_status' => ['type' => 'VARCHAR', 'constraint' => 50],
                    'created_at' => ['type' => 'DATETIME', 'null' => FALSE]
                ],
                'keys' => [
                    ['id', TRUE],
                    ['user_id', FALSE],
                    ['shop_id', FALSE],
                    ['vendor_id', FALSE],
                    ['cat_id', FALSE],
                    ['item_id', FALSE]
                ]
            ],

            'vendor_inventory_purchases_category' => [
                'fields' => [
                    'id' => ['type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE, 'auto_increment' => TRUE],
                    'user_id' => ['type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE],
                    'shop_id' => ['type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE],
                    'category' => ['type' => 'VARCHAR', 'constraint' => 50],
                    'status' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 1],
                    'created_at' => ['type' => 'DATETIME', 'null' => FALSE]
                ],
                'keys' => [
                    ['id', TRUE],
                    ['user_id', FALSE],
                    ['shop_id', FALSE]
                ]
            ],

            'vendor_inventory_purchases_items' => [
                'fields' => [
                    'id' => ['type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE, 'auto_increment' => TRUE],
                    'user_id' => ['type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE],
                    'shop_id' => ['type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE],
                    'cat_id' => ['type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE],
                    'name' => ['type' => 'VARCHAR', 'constraint' => 100],
                    'unit' => ['type' => 'VARCHAR', 'constraint' => 50],
                    'min_stock' => ['type' => 'INT', 'constraint' => 11],
                    'status' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 1],
                    'created_at' => ['type' => 'DATETIME', 'null' => FALSE]
                ],
                'keys' => [
                    ['id', TRUE],
                    ['user_id', FALSE],
                    ['shop_id', FALSE],
                    ['cat_id', FALSE]
                ]
            ]
        ];
    }

    public function sync_inventory_tables()
    {
        if (!isset($this->dbforge)) {
            $this->load->dbforge();
        }

        $tables = $this->get_vendor_inventory_schema();
        $results = ['created' => [], 'updated' => [], 'ignored' => []];

        foreach ($tables as $table_name => $definition) {

            // ---------------------------------------------------------
            // 1. CHECK IF TABLE EXISTS
            // ---------------------------------------------------------
            if ($this->db->table_exists($table_name)) {

                // Table exists: Check for missing columns
                $existing_columns = $this->db->list_fields($table_name);
                $columns_to_add = [];

                foreach ($definition['fields'] as $col_name => $col_attr) {
                    if (!in_array($col_name, $existing_columns)) {
                        $columns_to_add[$col_name] = $col_attr;
                    }
                }

                // If there are missing columns, add them
                if (!empty($columns_to_add)) {
                    $this->dbforge->add_column($table_name, $columns_to_add);
                    $results['updated'][] = $table_name;
                } else {
                    $results['ignored'][] = $table_name;
                }
            } else {

                // ---------------------------------------------------------
                // 2. TABLE DOES NOT EXIST -> CREATE IT
                // ---------------------------------------------------------
                $this->dbforge->add_field($definition['fields']);

                // Add Keys (Primary and Index)
                if (!empty($definition['keys'])) {
                    foreach ($definition['keys'] as $key) {
                        $this->dbforge->add_key($key[0], $key[1]);
                    }
                }

                $this->dbforge->create_table($table_name, TRUE);
                $results['created'][] = $table_name;
            }
        }

        return $results;
    }
}
