<?php

class ChainPay_Database {
    
    private static $instance = null;
    private $charset_collate;
    
    public static function get_instance() {
        if (null === self::$instance) {
            self::$instance = new self();
        }
        return self::$instance;
    }
    
    private function __construct() {
        global $wpdb;
        $this->charset_collate = $wpdb->get_charset_collate();
    }
    
    public function create_tables() {
        global $wpdb;
        
        $table_licenses = $wpdb->prefix . 'chainpay_licenses';
        $table_subscriptions = $wpdb->prefix . 'chainpay_subscriptions';
        $table_usage = $wpdb->prefix . 'chainpay_usage';
        $table_plans = $wpdb->prefix . 'chainpay_plans';
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        
        $sql = [];
        
        $sql[] = "CREATE TABLE $table_plans (
  id bigint(20) unsigned NOT NULL auto_increment,
  name varchar(255) NOT NULL,
  slug varchar(255) NOT NULL,
  description text,
  price decimal(10,2) NOT NULL default 0.00,
  billing_period varchar(50) default 'monthly',
  features text,
  limits text,
  is_active tinyint(1) default 1,
  created_at datetime default current_timestamp,
  updated_at datetime default current_timestamp on update current_timestamp,
  PRIMARY KEY  (id),
  KEY slug (slug),
  KEY is_active (is_active)
) $this->charset_collate;";
        
        $sql[] = "CREATE TABLE $table_licenses (
  id bigint(20) unsigned NOT NULL auto_increment,
  license_key varchar(64) NOT NULL,
  user_id bigint(20) unsigned default null,
  user_email varchar(255) NOT NULL,
  user_domain varchar(255),
  plan_id bigint(20) unsigned default null,
  status varchar(50) default 'active',
  activation_date datetime default null,
  expiration_date datetime default null,
  max_domains int(11) default 1,
  max_users int(11) default 1,
  metadata text,
  created_at datetime default current_timestamp,
  updated_at datetime default current_timestamp on update current_timestamp,
  PRIMARY KEY  (id),
  UNIQUE KEY license_key (license_key),
  KEY user_id (user_id),
  KEY status (status),
  KEY user_email (user_email)
) $this->charset_collate;";
        
        $sql[] = "CREATE TABLE $table_subscriptions (
  id bigint(20) unsigned NOT NULL auto_increment,
  subscription_id varchar(255) NOT NULL,
  user_id bigint(20) unsigned default null,
  user_email varchar(255) NOT NULL,
  license_id bigint(20) unsigned default null,
  plan_id bigint(20) unsigned default null,
  status varchar(50) default 'active',
  billing_cycle varchar(50) default 'monthly',
  amount decimal(10,2) NOT NULL,
  currency varchar(10) default 'USD',
  start_date datetime default current_timestamp,
  next_billing_date datetime,
  canceled_at datetime default null,
  payment_method varchar(100),
  payment_id varchar(255),
  metadata text,
  created_at datetime default current_timestamp,
  updated_at datetime default current_timestamp on update current_timestamp,
  PRIMARY KEY  (id),
  UNIQUE KEY subscription_id (subscription_id),
  KEY user_id (user_id),
  KEY status (status),
  KEY plan_id (plan_id)
) $this->charset_collate;";
        
        $sql[] = "CREATE TABLE $table_usage (
  id bigint(20) unsigned NOT NULL auto_increment,
  license_id bigint(20) unsigned NOT NULL,
  feature varchar(255) NOT NULL,
  count int(11) default 0,
  limit_value int(11) default 0,
  period varchar(50) default 'daily',
  period_start datetime,
  period_end datetime,
  created_at datetime default current_timestamp,
  updated_at datetime default current_timestamp on update current_timestamp,
  PRIMARY KEY  (id),
  KEY license_id (license_id),
  KEY feature (feature),
  UNIQUE KEY license_feature_period (license_id, feature, period, period_start)
) $this->charset_collate;";
        
        foreach ($sql as $query) {
            dbDelta($query);
        }
        
        $this->insert_default_plans();
    }
    
    private function insert_default_plans() {
        global $wpdb;
        $table_plans = $wpdb->prefix . 'chainpay_plans';
        
        $default_plans = array(
            array(
                'name' => 'Free',
                'slug' => 'free',
                'description' => 'Basic free plan',
                'price' => 0.00,
                'billing_period' => 'lifetime',
                'features' => json_encode(array('basic_features' => true)),
                'limits' => json_encode(array('api_calls' => 100, 'users' => 1))
            ),
            array(
                'name' => 'Starter',
                'slug' => 'starter',
                'description' => 'Starter plan for small teams',
                'price' => 9.99,
                'billing_period' => 'monthly',
                'features' => json_encode(array('basic_features' => true, 'priority_support' => true)),
                'limits' => json_encode(array('api_calls' => 1000, 'users' => 5))
            ),
            array(
                'name' => 'Professional',
                'slug' => 'professional',
                'description' => 'Professional plan for growing businesses',
                'price' => 29.99,
                'billing_period' => 'monthly',
                'features' => json_encode(array('basic_features' => true, 'priority_support' => true, 'advanced_analytics' => true)),
                'limits' => json_encode(array('api_calls' => 10000, 'users' => 25))
            ),
            array(
                'name' => 'Enterprise',
                'slug' => 'enterprise',
                'description' => 'Enterprise plan for large organizations',
                'price' => 99.99,
                'billing_period' => 'monthly',
                'features' => json_encode(array('basic_features' => true, 'priority_support' => true, 'advanced_analytics' => true, 'custom_integrations' => true, 'dedicated_support' => true)),
                'limits' => json_encode(array('api_calls' => -1, 'users' => -1))
            )
        );
        
        foreach ($default_plans as $plan) {
            $wpdb->replace($table_plans, $plan);
        }
    }
    
    public function drop_tables() {
        global $wpdb;
        
        $tables = array(
            $wpdb->prefix . 'chainpay_licenses',
            $wpdb->prefix . 'chainpay_subscriptions',
            $wpdb->prefix . 'chainpay_usage',
            $wpdb->prefix . 'chainpay_plans'
        );
        
        foreach ($tables as $table) {
            $wpdb->query("DROP TABLE IF EXISTS $table");
        }
    }
}
