<?php

namespace Tenant\Database\Migrations;

use CodeIgniter\Database\Migration;

/**
 * One cached AI trend note per (corporate_account_id, month) statement —
 * see CorporateAccountController::statement(). A statement PDF can be
 * re-downloaded any number of times; caching here means only the FIRST
 * generation of a given month's statement calls Claude, every later
 * download for that same month reuses the stored note rather than
 * re-narrating (and re-billing an API call for) numbers that haven't
 * changed. `data_snapshot` freezes the real current/prior-month totals
 * the note was built from.
 */
class CreateAiCorporateSummariesTable extends Migration
{
    public function up()
    {
        $this->forge->addField([
            'id' => [
                'type'           => 'INT',
                'constraint'     => 11,
                'unsigned'       => true,
                'auto_increment' => true,
            ],
            'corporate_account_id' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
            ],
            'month' => [
                'type'       => 'VARCHAR',
                'constraint' => 7,
                'comment'    => 'YYYY-MM',
            ],
            'summary_text' => [
                'type' => 'TEXT',
            ],
            'data_snapshot' => [
                'type' => 'TEXT',
                'null' => true,
            ],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
        ]);

        $this->forge->addKey('id', true);
        $this->forge->addUniqueKey(['corporate_account_id', 'month']);
        $this->forge->addForeignKey('corporate_account_id', 'corporate_accounts', 'id', '', 'CASCADE');
        $this->forge->createTable('ai_corporate_summaries');
    }

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