<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateSubscriptions extends Migration
{
    public function up(): void
    {
        $this->forge->addField([
            'id'                     => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'name'                   => ['type' => 'VARCHAR', 'constraint' => 100],
            'slug'                   => ['type' => 'VARCHAR', 'constraint' => 100],
            'price_monthly'          => ['type' => 'DECIMAL', 'constraint' => '10,2', 'default' => 0],
            'price_yearly'           => ['type' => 'DECIMAL', 'constraint' => '10,2', 'default' => 0],
            'candidate_view_limit'   => ['type' => 'INT', 'default' => 0],
            'contact_reveal_limit'   => ['type' => 'INT', 'default' => 0],
            'resume_download_limit'  => ['type' => 'INT', 'default' => 0],
            'recruiter_limit'        => ['type' => 'INT', 'default' => 1],
            'job_posting_limit'      => ['type' => 'INT', 'default' => 1],
            'features'               => ['type' => 'JSON', 'null' => true],
            'is_active'              => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 1],
            'sort_order'             => ['type' => 'TINYINT', 'unsigned' => true, 'default' => 0],
            'created_at'             => ['type' => 'TIMESTAMP', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addUniqueKey('slug');
        $this->forge->createTable('subscription_plans', true, ['ENGINE' => 'InnoDB']);

        $this->forge->addField([
            'id'                    => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'agency_id'             => ['type' => 'BIGINT', 'unsigned' => true],
            'plan_id'               => ['type' => 'BIGINT', 'unsigned' => true],
            'billing_cycle'         => ['type' => 'ENUM', 'constraint' => ['monthly', 'yearly'], 'default' => 'monthly'],
            'status'                => ['type' => 'ENUM', 'constraint' => ['trialing', 'active', 'past_due', 'cancelled', 'expired'], 'default' => 'trialing'],
            'current_period_start'  => ['type' => 'DATE'],
            'current_period_end'    => ['type' => 'DATE'],
            'auto_renew'            => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 1],
            'cancelled_at'          => ['type' => 'TIMESTAMP', 'null' => true],
            'created_at'            => ['type' => 'TIMESTAMP', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey(['status', 'current_period_end']);
        $this->forge->addKey('agency_id');
        $this->forge->addForeignKey('agency_id', 'agencies', 'id', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('plan_id', 'subscription_plans', 'id', false, 'RESTRICT');
        $this->forge->createTable('agency_subscriptions', true, ['ENGINE' => 'InnoDB']);

        $this->forge->addField([
            'id'                        => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'agency_subscription_id'    => ['type' => 'BIGINT', 'unsigned' => true],
            'candidate_views_used'      => ['type' => 'INT', 'unsigned' => true, 'default' => 0],
            'contact_reveals_used'      => ['type' => 'INT', 'unsigned' => true, 'default' => 0],
            'resume_downloads_used'     => ['type' => 'INT', 'unsigned' => true, 'default' => 0],
            'jobs_posted_used'          => ['type' => 'INT', 'unsigned' => true, 'default' => 0],
            'period_start'              => ['type' => 'DATE'],
            'period_end'                => ['type' => 'DATE'],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addForeignKey('agency_subscription_id', 'agency_subscriptions', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('subscription_usage', true, ['ENGINE' => 'InnoDB']);
    }

    public function down(): void
    {
        $this->forge->dropTable('subscription_usage', true);
        $this->forge->dropTable('agency_subscriptions', true);
        $this->forge->dropTable('subscription_plans', true);
    }
}
