<?php

namespace Tenant\Database\Migrations;

use CodeIgniter\Database\Migration;

/**
 * WhatsApp Concierge — the last Phase 5 AI feature (see the brief's Tier
 * 2 guardrails). A conversation is one guest+phone thread; messages are
 * the individual inbound/outbound turns within it.
 *
 * `needs_staff` is set whenever Claude calls its escalate_to_staff tool
 * (see ConciergeService) — a booking-change/rate/payment/complaint
 * intent, or a message from an unrecognized number/guest with no
 * active-or-upcoming booking, since neither case has enough safe context
 * to auto-reply. `pending_reply` is set whenever Claude drafted a reply
 * but the tenant hasn't opted this category into autosend (see
 * ai.autosend_concierge_replies) — a human must click Send in the
 * concierge inbox. A manager clears needs_staff (resolved_at/resolved_by)
 * once they've followed up; pending_reply clears itself the moment its
 * message is sent or dismissed. Same "review queue" shape as
 * ai_message_drafts, just conversation-threaded instead of one row per
 * message.
 *
 * `concierge_messages.status` is only meaningful for outbound rows:
 * pending (drafted, awaiting a staff Send click), sent (delivered — by
 * staff or autosend), failed (send attempted and the WhatsApp API
 * failed). Inbound rows are always 'sent' (received) — kept as a single
 * column rather than a nullable one so no special-casing is needed
 * elsewhere. `whatsapp_message_id` on inbound rows dedupes Meta's
 * documented at-least-once webhook redelivery, mirroring the same
 * idempotency reasoning PaymentWebhookController applies via
 * ClientReference.
 */
class CreateConciergeTables 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,
                'null'       => true,
            ],
            'phone' => [
                'type'       => 'VARCHAR',
                'constraint' => 20,
            ],
            'needs_staff' => [
                'type'       => 'TINYINT',
                'constraint' => 1,
                'default'    => 0,
            ],
            'pending_reply' => [
                'type'       => 'TINYINT',
                'constraint' => 1,
                'default'    => 0,
            ],
            'last_message_at' => [
                'type' => 'DATETIME',
                'null' => true,
            ],
            'resolved_at' => [
                'type' => 'DATETIME',
                'null' => true,
            ],
            'resolved_by' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
                'null'       => true,
            ],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
        ]);

        $this->forge->addKey('id', true);
        $this->forge->addKey('phone');
        $this->forge->addKey('needs_staff');
        $this->forge->addForeignKey('guest_id', 'guests', 'id', '', 'SET NULL');
        $this->forge->addForeignKey('resolved_by', 'users', 'id', '', 'SET NULL');
        $this->forge->createTable('concierge_conversations');

        $this->forge->addField([
            'id' => [
                'type'           => 'INT',
                'constraint'     => 11,
                'unsigned'       => true,
                'auto_increment' => true,
            ],
            'conversation_id' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
            ],
            'direction' => [
                'type'       => 'VARCHAR',
                'constraint' => 10,
                'comment'    => 'inbound, outbound',
            ],
            'status' => [
                'type'       => 'VARCHAR',
                'constraint' => 20,
                'default'    => 'sent',
                'comment'    => 'pending, sent, failed — only meaningful for outbound',
            ],
            'body' => [
                'type' => 'TEXT',
            ],
            'whatsapp_message_id' => [
                'type'       => 'VARCHAR',
                'constraint' => 100,
                'null'       => true,
            ],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
        ]);

        $this->forge->addKey('id', true);
        $this->forge->addKey('conversation_id');
        $this->forge->addKey('whatsapp_message_id');
        $this->forge->addForeignKey('conversation_id', 'concierge_conversations', 'id', '', 'CASCADE');
        $this->forge->createTable('concierge_messages');
    }

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