<?php

namespace Tenant\Database\Migrations;

use CodeIgniter\Database\Migration;

/**
 * Loyalty programme — entirely missing before (see the requirements
 * audit). Rules (points-per-cedi rate, tier thresholds, redemption
 * value) are manager-configurable Settings rather than hardcoded, same
 * pattern as billing.tax_rate_percent, since these are genuinely the
 * hotel owner's call, not something to invent a fixed number for.
 *
 * `loyalty_accounts.points_balance` is a maintained running total (like
 * inventory_items.quantity_in_stock) fed by the immutable
 * `loyalty_transactions` ledger (like folio_payments feeding a folio's
 * balance) — the same "denormalized total + append-only ledger" shape
 * used elsewhere in this schema, not a novel pattern.
 */
class CreateLoyaltyTables extends Migration
{
    public function up()
    {
        $this->forge->addField([
            'id' => [
                'type'           => 'INT',
                'constraint'     => 11,
                'unsigned'       => true,
                'auto_increment' => true,
            ],
            'guest_id' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
            ],
            'points_balance' => [
                'type'       => 'INT',
                'constraint' => 11,
                'default'    => 0,
            ],
            'tier' => [
                'type'       => 'VARCHAR',
                'constraint' => 20,
                'default'    => 'bronze',
                'comment'    => 'bronze|silver|gold — recomputed from points_balance whenever it changes.',
            ],
            'enrolled_at' => [
                'type' => 'DATETIME',
            ],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addUniqueKey('guest_id');
        $this->forge->addForeignKey('guest_id', 'guests', 'id', '', 'CASCADE');
        $this->forge->createTable('loyalty_accounts');

        $this->forge->addField([
            'id' => [
                'type'           => 'INT',
                'constraint'     => 11,
                'unsigned'       => true,
                'auto_increment' => true,
            ],
            'guest_id' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
            ],
            'type' => [
                'type'       => 'VARCHAR',
                'constraint' => 10,
                'comment'    => 'earn|redeem|adjust',
            ],
            'points' => [
                'type'       => 'INT',
                'constraint' => 11,
                'comment'    => 'Signed — positive for earn, negative for redeem, either for a manual adjust.',
            ],
            'reference_type' => [
                'type'       => 'VARCHAR',
                'constraint' => 20,
                'null'       => true,
                'comment'    => 'folio_payment|folio_redemption|manual',
            ],
            'reference_id' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
                'null'       => true,
            ],
            'notes' => [
                'type'       => 'VARCHAR',
                'constraint' => 255,
                'null'       => true,
            ],
            'created_by' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
                'null'       => true,
            ],
            'created_at' => [
                'type' => 'DATETIME',
            ],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey('guest_id');
        $this->forge->addForeignKey('guest_id', 'guests', 'id', '', 'CASCADE');
        $this->forge->addForeignKey('created_by', 'users', 'id', '', 'SET NULL');
        $this->forge->createTable('loyalty_transactions');
    }

    public function down()
    {
        $this->forge->dropTable('loyalty_transactions', true);
        $this->forge->dropTable('loyalty_accounts', true);
    }
}
