<?php

namespace Tenant\Database\Migrations;

use CodeIgniter\Database\Migration;

/**
 * Email marketing campaigns — manual send only (the actual ask was "email
 * marketing integration", not a drip-automation engine that wasn't
 * requested). Audience targeting reuses GuestModel::filterList()'s
 * existing segment/nationality filtering — audience_filter stores that
 * same filter shape as JSON rather than adding parallel columns, since
 * it's genuinely just "the same query, saved". Blacklisted guests and
 * guests with no email on file are always excluded, regardless of what's
 * in the filter — see CampaignController::audienceQuery().
 *
 * campaign_recipients is a per-guest send log, reusing EmailService (and
 * therefore email_log too — this table is campaign-centric where
 * email_log is guest-centric; each answers a different real question:
 * "who got this blast and did it succeed" vs. "every email this guest
 * has ever received").
 */
class CreateCampaignTables extends Migration
{
    public function up()
    {
        $this->forge->addField([
            'id' => [
                'type'           => 'INT',
                'constraint'     => 11,
                'unsigned'       => true,
                'auto_increment' => true,
            ],
            'subject' => [
                'type'       => 'VARCHAR',
                'constraint' => 150,
            ],
            'body' => [
                'type' => 'TEXT',
            ],
            'audience_filter' => [
                'type' => 'TEXT',
                'null' => true,
                'comment' => 'JSON — same shape GuestModel::filterList() accepts (segment, nationality).',
            ],
            'status' => [
                'type'       => 'VARCHAR',
                'constraint' => 20,
                'default'    => 'draft',
                'comment'    => 'draft|sent',
            ],
            'sent_count' => [
                'type'       => 'INT',
                'constraint' => 11,
                'default'    => 0,
            ],
            'sent_at' => [
                'type' => 'DATETIME',
                'null' => true,
            ],
            '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->addForeignKey('created_by', 'users', 'id', '', 'SET NULL');
        $this->forge->createTable('campaigns');

        $this->forge->addField([
            'id' => [
                'type'           => 'INT',
                'constraint'     => 11,
                'unsigned'       => true,
                'auto_increment' => true,
            ],
            'campaign_id' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
            ],
            'guest_id' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
            ],
            'status' => [
                'type'       => 'VARCHAR',
                'constraint' => 20,
                'comment'    => 'sent|failed',
            ],
            'error' => [
                'type'       => 'VARCHAR',
                'constraint' => 255,
                'null'       => true,
            ],
            'sent_at' => [
                'type' => 'DATETIME',
            ],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey('campaign_id');
        $this->forge->addForeignKey('campaign_id', 'campaigns', 'id', '', 'CASCADE');
        $this->forge->addForeignKey('guest_id', 'guests', 'id', '', 'CASCADE');
        $this->forge->createTable('campaign_recipients');
    }

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