<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateUsernamesAndContactViewLogs extends Migration
{
    public function up(): void
    {
        // Username lives on `users` (not candidate_profiles/agencies) since
        // BOTH candidates and agencies need one, and it's fundamentally an
        // identity/routing concern, not profile content.
        $this->forge->addColumn('users', [
            'username' => ['type' => 'VARCHAR', 'constraint' => 50, 'null' => true, 'after' => 'email'],
        ]);
        $this->db->query('ALTER TABLE users ADD UNIQUE KEY uq_users_username (username)');

        // Tracks every username change so we can enforce an admin-configurable
        // "max N changes per period" limit (see settings group 'profile').
        $this->forge->addField([
            'id'         => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'user_id'    => ['type' => 'BIGINT', 'unsigned' => true],
            'old_username' => ['type' => 'VARCHAR', 'constraint' => 50, 'null' => true],
            'new_username' => ['type' => 'VARCHAR', 'constraint' => 50],
            'created_at' => ['type' => 'TIMESTAMP', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey(['user_id', 'created_at']);
        $this->forge->addForeignKey('user_id', 'users', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('username_change_log', true, ['ENGINE' => 'InnoDB']);

        // Daily contact-view limit tracking (10/day per spec, admin
        // configurable). One row per (viewer, day) with a running count,
        // rather than one row per view, so checking "how many today" is a
        // single indexed lookup instead of a COUNT(*) over a growing table.
        $this->forge->addField([
            'id'           => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'viewer_user_id' => ['type' => 'BIGINT', 'unsigned' => true],
            'view_date'    => ['type' => 'DATE'],
            'view_count'   => ['type' => 'INT', 'unsigned' => true, 'default' => 0],
            'last_viewed_at' => ['type' => 'TIMESTAMP', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addUniqueKey(['viewer_user_id', 'view_date']);
        $this->forge->addForeignKey('viewer_user_id', 'users', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('contact_view_daily_limits', true, ['ENGINE' => 'InnoDB']);
    }

    public function down(): void
    {
        $this->forge->dropTable('contact_view_daily_limits', true);
        $this->forge->dropTable('username_change_log', true);
        $this->db->query('ALTER TABLE users DROP INDEX uq_users_username');
        $this->forge->dropColumn('users', 'username');
    }
}
