<?php

use Licenzo\Framework\Database\Migration\Migration;

if ( ! defined( 'ABSPATH' ) ) exit; // Exit if accessed directly

return new class extends Migration {
    protected $table = 'licenzo_subscriptions';

    public function up()
    {
        return $this->create($this->table, [
            'id'                        => 'BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY',
            'uuid'                      => 'CHAR(36) NOT NULL UNIQUE',
            'customer_id'               => 'BIGINT UNSIGNED NOT NULL',
            'parent_order_id'           => 'BIGINT UNSIGNED NULL',

            'provider'    => 'VARCHAR(100) NULL',
            'provider_id' => 'VARCHAR(255) NULL',

            // Subscription details (Fno product_id - products in subscription_products table)
            'subscription_name'         => 'VARCHAR(255) NOT NULL COMMENT "Bundle name or subscription title"',

            // Billing details
            'currency'                  => 'VARCHAR(10) NOT NULL DEFAULT "USD"',
            'billing_cycle'             => 'VARCHAR(50) NOT NULL', // onetime, split, weekly, monthly, yearly, lifetime
            'signup_fee'                => 'BIGINT NOT NULL DEFAULT 0',
            'initial_tax_total'         => 'BIGINT NOT NULL DEFAULT 0',
            'recurring_amount'          => 'BIGINT NOT NULL',
            'recurring_tax_total'       => 'BIGINT NOT NULL DEFAULT 0',
            'recurring_total'           => 'BIGINT NOT NULL',

            // Billing cycle
            'billing_total_cycles'      => 'INT UNSIGNED DEFAULT 0', // 0 = unlimited
            'bill_count'                => 'INT UNSIGNED DEFAULT 0', // How many times billed

            // Revenue tracking
            'total_paid'                => 'BIGINT NOT NULL DEFAULT 0 COMMENT "Total successfully paid amount (cents)"',
            'total_refunded'            => 'BIGINT NOT NULL DEFAULT 0 COMMENT "Total refunded amount (cents)"',

            // Dates
            'expire_at'                 => 'TIMESTAMP NULL',
            'trial_ends_at'             => 'TIMESTAMP NULL',
            'canceled_at'               => 'TIMESTAMP NULL',
            'restored_at'               => 'TIMESTAMP NULL',
            'next_billing_date'         => 'TIMESTAMP NULL',

            // Trial
            'trial_days'                => 'INT UNSIGNED DEFAULT 0',

            // Provider integration (Stripe, PayPal, WooCommerce, etc.)
            'provider_customer_id'      => 'VARCHAR(255) NULL',
            'provider_plan_id'          => 'VARCHAR(255) NULL',
            'provider_subscription_id'  => 'VARCHAR(255) NULL',

            // Status
            'status'                    => 'VARCHAR(50) NOT NULL DEFAULT "pending"', // pending, active, paused, canceled, expired

            // amount
            'amount'                    => 'BIGINT NOT NULL DEFAULT 0', // For initial payment amount (cents)

            // Payment
            'collection_method'         => 'VARCHAR(50) NULL', // charge_automatically, send_invoice
            'current_payment_method'    => 'VARCHAR(50) NULL',

            // Metadata
            'original_plan'             => 'LONGTEXT NULL', // JSON - Original plan details
            'provider_response'         => 'LONGTEXT NULL', // JSON - Provider response
            'config'                    => 'LONGTEXT NULL', // JSON - Additional config

            // Dunning management
            'failed_payment_count'      => 'INT UNSIGNED DEFAULT 0',
            'last_payment_attempt'      => 'TIMESTAMP NULL',
            'next_payment_attempt'      => 'TIMESTAMP NULL',
            'dunning_status'            => 'VARCHAR(50) NULL', // active, paused, exhausted

            'created_at'                => 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP',
            'updated_at'                => 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'
        ], [
            ['type' => 'KEY', 'name' => 'idx_customer_id', 'columns' => ['customer_id']],
            ['type' => 'KEY', 'name' => 'idx_status', 'columns' => ['status']],
        ]);
    }

    public function down()
    {
        return $this->drop($this->table);
    }
};
