<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateRewardProgram extends Migration
{
    public function up()
    {
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'name' => ['type' => 'VARCHAR', 'constraint' => 150],
            'slug' => ['type' => 'VARCHAR', 'constraint' => 180],
            'phone' => ['type' => 'VARCHAR', 'constraint' => 50, 'null' => true],
            'address' => ['type' => 'TEXT', 'null' => true],
            'status' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 1],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addUniqueKey('slug');
        $this->forge->createTable('rewarder_shops', true);
        $this->forge->reset();

        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'shop_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'user_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'role' => ['type' => 'VARCHAR', 'constraint' => 20, 'default' => 'OWNER'],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey(['shop_id', 'user_id'], false, true);
        $this->forge->createTable('rewarder_shop_users', true);
        $this->forge->reset();

        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'title' => ['type' => 'VARCHAR', 'constraint' => 120],
            'description' => ['type' => 'TEXT', 'null' => true],
            'price' => ['type' => 'DECIMAL', 'constraint' => '10,2', 'default' => 0],
            'duration_days' => ['type' => 'INT', 'constraint' => 11, 'default' => 30],
            'status' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 1],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->createTable('rewarder_subscriptions', true);
        $this->forge->reset();

        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'shop_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'subscription_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'start_at' => ['type' => 'DATETIME', 'null' => true],
            'end_at' => ['type' => 'DATETIME', 'null' => true],
            'status' => ['type' => 'VARCHAR', 'constraint' => 20, 'default' => 'ACTIVE'],
            'created_by' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey(['shop_id']);
        $this->forge->addKey(['subscription_id']);
        $this->forge->createTable('rewarder_shop_subscriptions', true);
        $this->forge->reset();

        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'shop_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'name' => ['type' => 'VARCHAR', 'constraint' => 120],
            'status' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 1],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey(['shop_id']);
        $this->forge->createTable('rewarder_product_categories', true);
        $this->forge->reset();

        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'shop_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'category_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'name' => ['type' => 'VARCHAR', 'constraint' => 200],
            'sku' => ['type' => 'VARCHAR', 'constraint' => 80, 'null' => true],
            'price' => ['type' => 'DECIMAL', 'constraint' => '10,2', 'default' => 0],
            'status' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 1],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey(['shop_id']);
        $this->forge->addKey(['category_id']);
        $this->forge->createTable('rewarder_products', true);
        $this->forge->reset();

        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'shop_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'name' => ['type' => 'VARCHAR', 'constraint' => 120],
            'status' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 1],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey(['shop_id']);
        $this->forge->createTable('rewarder_service_categories', true);
        $this->forge->reset();

        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'shop_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'category_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'name' => ['type' => 'VARCHAR', 'constraint' => 200],
            'price' => ['type' => 'DECIMAL', 'constraint' => '10,2', 'default' => 0],
            'status' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 1],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey(['shop_id']);
        $this->forge->addKey(['category_id']);
        $this->forge->createTable('rewarder_services', true);
        $this->forge->reset();

        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'shop_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'applies_to' => ['type' => 'VARCHAR', 'constraint' => 30, 'default' => 'ALL'],
            'target_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'discount_type' => ['type' => 'VARCHAR', 'constraint' => 20, 'default' => 'PERCENT'],
            'value' => ['type' => 'DECIMAL', 'constraint' => '10,2', 'default' => 0],
            'min_amount' => ['type' => 'DECIMAL', 'constraint' => '10,2', 'null' => true],
            'start_at' => ['type' => 'DATETIME', 'null' => true],
            'end_at' => ['type' => 'DATETIME', 'null' => true],
            'status' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 1],
            'created_by' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey(['shop_id']);
        $this->forge->addKey(['applies_to', 'target_id']);
        $this->forge->createTable('rewarder_discounts', true);
        $this->forge->reset();

        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'shop_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'applies_to' => ['type' => 'VARCHAR', 'constraint' => 30, 'default' => 'ALL'],
            'target_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'points_type' => ['type' => 'VARCHAR', 'constraint' => 20, 'default' => 'PERCENT'],
            'value' => ['type' => 'DECIMAL', 'constraint' => '10,2', 'default' => 0],
            'start_at' => ['type' => 'DATETIME', 'null' => true],
            'end_at' => ['type' => 'DATETIME', 'null' => true],
            'status' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 1],
            'created_by' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey(['shop_id']);
        $this->forge->addKey(['applies_to', 'target_id']);
        $this->forge->createTable('rewarder_reward_rules', true);
        $this->forge->reset();

        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'shop_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'customer_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'subtotal' => ['type' => 'DECIMAL', 'constraint' => '10,2', 'default' => 0],
            'discount_total' => ['type' => 'DECIMAL', 'constraint' => '10,2', 'default' => 0],
            'total' => ['type' => 'DECIMAL', 'constraint' => '10,2', 'default' => 0],
            'points_earned' => ['type' => 'INT', 'constraint' => 11, 'default' => 0],
            'points_redeemed' => ['type' => 'INT', 'constraint' => 11, 'default' => 0],
            'status' => ['type' => 'VARCHAR', 'constraint' => 20, 'default' => 'COMPLETED'],
            'created_by' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey(['shop_id']);
        $this->forge->addKey(['customer_id']);
        $this->forge->createTable('rewarder_orders', true);
        $this->forge->reset();

        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'order_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'item_type' => ['type' => 'VARCHAR', 'constraint' => 20],
            'item_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'name' => ['type' => 'VARCHAR', 'constraint' => 200],
            'quantity' => ['type' => 'INT', 'constraint' => 11, 'default' => 1],
            'unit_price' => ['type' => 'DECIMAL', 'constraint' => '10,2', 'default' => 0],
            'discount_amount' => ['type' => 'DECIMAL', 'constraint' => '10,2', 'default' => 0],
            'line_total' => ['type' => 'DECIMAL', 'constraint' => '10,2', 'default' => 0],
            'points_earned' => ['type' => 'INT', 'constraint' => 11, 'default' => 0],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey(['order_id']);
        $this->forge->createTable('rewarder_order_items', true);
        $this->forge->reset();

        $this->forge->addField([
            'user_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'points_balance' => ['type' => 'INT', 'constraint' => 11, 'default' => 0],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('user_id', true);
        $this->forge->createTable('rewarder_wallets', true);
        $this->forge->reset();

        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'user_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'shop_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'order_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'points_delta' => ['type' => 'INT', 'constraint' => 11, 'default' => 0],
            'description' => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey(['user_id']);
        $this->forge->addKey(['shop_id']);
        $this->forge->addKey(['order_id']);
        $this->forge->createTable('rewarder_ledger', true);
        $this->forge->reset();

        $roles = ['Shopkeeper', 'Customer'];
        foreach ($roles as $title) {
            $exists = $this->db->table('roles')->where('title', $title)->countAllResults();
            if (!$exists) {
                $this->db->table('roles')->insert(['title' => $title]);
            }
        }

        $permissions = [
            ['title' => 'Subscriptions List', 'code' => 'subscriptions_list'],
            ['title' => 'Add Subscriptions', 'code' => 'subscriptions_add'],
            ['title' => 'Edit Subscriptions', 'code' => 'subscriptions_edit'],
            ['title' => 'Delete Subscriptions', 'code' => 'subscriptions_delete'],
            ['title' => 'Shops List', 'code' => 'shops_list'],
            ['title' => 'Add Shops', 'code' => 'shops_add'],
            ['title' => 'Edit Shops', 'code' => 'shops_edit'],
            ['title' => 'Delete Shops', 'code' => 'shops_delete'],
            ['title' => 'Shop Subscriptions Manage', 'code' => 'shop_subscriptions_manage'],
            ['title' => 'Shop Products Manage', 'code' => 'shop_products_manage'],
            ['title' => 'Shop Services Manage', 'code' => 'shop_services_manage'],
            ['title' => 'Shop Discounts Manage', 'code' => 'shop_discounts_manage'],
            ['title' => 'Shop Rewards Manage', 'code' => 'shop_rewards_manage'],
            ['title' => 'Shop Orders Manage', 'code' => 'shop_orders_manage'],
            ['title' => 'User Wallet View', 'code' => 'user_wallet_view'],
            ['title' => 'User Discounts View', 'code' => 'user_discounts_view'],
            ['title' => 'User Orders View', 'code' => 'user_orders_view'],
        ];

        foreach ($permissions as $perm) {
            $exists = $this->db->table('permissions')->where('code', $perm['code'])->countAllResults();
            if (!$exists) {
                $this->db->table('permissions')->insert($perm);
            }
        }

        $adminRole = $this->db->table('roles')->select('id')->where('title', 'Admin')->get()->getRow();
        $shopkeeperRole = $this->db->table('roles')->select('id')->where('title', 'Shopkeeper')->get()->getRow();
        $customerRole = $this->db->table('roles')->select('id')->where('title', 'Customer')->get()->getRow();

        $codes = array_column($permissions, 'code');
        if ($adminRole) {
            foreach ($codes as $code) {
                $exists = $this->db->table('role_permissions')->where(['role' => $adminRole->id, 'permission' => $code])->countAllResults();
                if (!$exists) {
                    $this->db->table('role_permissions')->insert(['role' => $adminRole->id, 'permission' => $code]);
                }
            }
        }

        if ($shopkeeperRole) {
            $shopCodes = ['shop_products_manage', 'shop_services_manage', 'shop_discounts_manage', 'shop_rewards_manage', 'shop_orders_manage'];
            foreach ($shopCodes as $code) {
                $exists = $this->db->table('role_permissions')->where(['role' => $shopkeeperRole->id, 'permission' => $code])->countAllResults();
                if (!$exists) {
                    $this->db->table('role_permissions')->insert(['role' => $shopkeeperRole->id, 'permission' => $code]);
                }
            }
        }

        if ($customerRole) {
            $userCodes = ['user_wallet_view', 'user_discounts_view', 'user_orders_view'];
            foreach ($userCodes as $code) {
                $exists = $this->db->table('role_permissions')->where(['role' => $customerRole->id, 'permission' => $code])->countAllResults();
                if (!$exists) {
                    $this->db->table('role_permissions')->insert(['role' => $customerRole->id, 'permission' => $code]);
                }
            }
        }
    }

    public function down()
    {
        $this->forge->dropTable('rewarder_ledger', true);
        $this->forge->dropTable('rewarder_wallets', true);
        $this->forge->dropTable('rewarder_order_items', true);
        $this->forge->dropTable('rewarder_orders', true);
        $this->forge->dropTable('rewarder_reward_rules', true);
        $this->forge->dropTable('rewarder_discounts', true);
        $this->forge->dropTable('rewarder_services', true);
        $this->forge->dropTable('rewarder_service_categories', true);
        $this->forge->dropTable('rewarder_products', true);
        $this->forge->dropTable('rewarder_product_categories', true);
        $this->forge->dropTable('rewarder_shop_subscriptions', true);
        $this->forge->dropTable('rewarder_subscriptions', true);
        $this->forge->dropTable('rewarder_shop_users', true);
        $this->forge->dropTable('rewarder_shops', true);
    }
}

