<?php

namespace Vendtics\Database\Migrations;

defined( 'ABSPATH' ) || exit;

use Vendtics\WpMVC\Contracts\Migration;

class CreateDB implements Migration
{
    public function more_than_version() {
        return '0.0.1';
    }

    public function execute(): bool {
        global $wpdb;

        $charset_collate = $wpdb->get_charset_collate();
        $db_prefix       = "{$wpdb->prefix}vendtics_";

        if ( ! function_exists( 'dbDelta' ) ) {
            require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        }

        // -- -----------------------------------------------------
        // -- Table `contacts`
        // -- -----------------------------------------------------
        $sql = "CREATE TABLE `{$db_prefix}contacts` (
            `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            `first_name` VARCHAR(50),
            `last_name` VARCHAR(50),
            `email` VARCHAR(100),
            `phone_number` VARCHAR(20),
            `company` VARCHAR(100),
            `designation` VARCHAR(100),
            `date_of_birth` DATE,
            `timezone` VARCHAR(50),
            `address` VARCHAR(255),
            `city` VARCHAR(100),
            `state` VARCHAR(100),
            `postal_code` VARCHAR(20),
            `country` VARCHAR(100),
            `status` VARCHAR(20) DEFAULT 'pending',
            `type` ENUM('lead', 'customer') NOT NULL DEFAULT 'lead',
            `source` VARCHAR(100),
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (`id`)
        );

        -- -----------------------------------------------------
        -- Table `tags`
        -- -----------------------------------------------------
        CREATE TABLE `{$db_prefix}tags` (
            `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            `name` VARCHAR(50) NOT NULL,
            `description` VARCHAR(255),
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (`id`)
        );

        -- -----------------------------------------------------
        -- Table `lists`
        -- -----------------------------------------------------
        CREATE TABLE `{$db_prefix}lists` (
            `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            `name` VARCHAR(50) NOT NULL,
            `description` VARCHAR(255),
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (`id`)
        );

        -- -----------------------------------------------------
        -- Table `entity_tags`
        -- -----------------------------------------------------
        CREATE TABLE `{$db_prefix}entity_tags` (
            `entity_id` BIGINT UNSIGNED NOT NULL,
            `entity_type` VARCHAR(50) NOT NULL,
            `tag_id` BIGINT UNSIGNED NOT NULL,
            PRIMARY KEY (`entity_id`, `entity_type`, `tag_id`)
        );

        -- -----------------------------------------------------
        -- Table `entity_lists`
        -- -----------------------------------------------------
        CREATE TABLE `{$db_prefix}entity_lists` (
            `entity_id` BIGINT UNSIGNED NOT NULL,
            `entity_type` VARCHAR(50) NOT NULL,
            `list_id` BIGINT UNSIGNED NOT NULL,
            PRIMARY KEY (`entity_id`, `entity_type`, `list_id`)
        );

        -- -----------------------------------------------------
        -- Table `segments`
        -- -----------------------------------------------------
        CREATE TABLE `{$db_prefix}segments` (
            `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            `name` VARCHAR(255) NOT NULL,
            `description` VARCHAR(255),
            `queries` LONGTEXT NOT NULL,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (`id`)
        );

        -- -----------------------------------------------------
        -- Table `notes`
        -- -----------------------------------------------------
        CREATE TABLE `{$db_prefix}notes` (
            `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            `title` VARCHAR(255),
            `note` LONGTEXT,
            `contact_id` BIGINT UNSIGNED NOT NULL,
            `created_by` BIGINT UNSIGNED NOT NULL,
            `priority` ENUM('high', 'medium', 'normal', 'low') NOT NULL DEFAULT 'normal',
            `is_favorite` TINYINT(1) DEFAULT 0,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (`id`)
        );

        -- -----------------------------------------------------
        -- Table `campaigns`
        -- -----------------------------------------------------
        CREATE TABLE `{$db_prefix}campaigns` (
            `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            `parent_id` BIGINT UNSIGNED NULL,
            `name` VARCHAR(255) NOT NULL,
            `type` ENUM('standard', 'recurring', 'split', 'sequence', 'children') NOT NULL,
            `subject` VARCHAR(255) NULL,
            `pre_header` VARCHAR(255),
            `utm_parameters` TEXT,
            `body` LONGTEXT,
            `recipient_type` ENUM('lists_tags', 'segment', 'advanced_filter') NOT NULL,
            `segment_id` BIGINT UNSIGNED NULL,
            `advanced_filters` TEXT,
            `tracking_open_read` TINYINT(1) DEFAULT 0,
            `tracking_link_click` TINYINT(1) DEFAULT 0,
            `schedule_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            `status` ENUM('draft', 'schedule', 'processing', 'cancel', 'paused', 'completed') NOT NULL DEFAULT 'draft',
            `pause_reason` TEXT,
            `cancel_reason` LONGTEXT,
            `created_by` BIGINT UNSIGNED NULL,
            `started_at` TIMESTAMP,
            `canceled_at` TIMESTAMP,
            `paused_at` TIMESTAMP,
            `completed_at` TIMESTAMP,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (`id`)
        );

        -- -----------------------------------------------------
        -- Table `campaign_recipients`
        -- -----------------------------------------------------
        CREATE TABLE `{$db_prefix}campaign_recipients` (
            `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            `campaign_id` BIGINT UNSIGNED NOT NULL,
            `children_campaign_id` BIGINT UNSIGNED NULL,
            `contact_id` BIGINT UNSIGNED NOT NULL,
            `status` ENUM( 'sent', 'failed', 'bounced') NOT NULL,
            `sent_at` TIMESTAMP NULL,
            `opened_at` TIMESTAMP NULL,
            `clicked_at` TIMESTAMP NULL,
            `bounced_at` TIMESTAMP NULL,
            `unsubscribed_at` TIMESTAMP NULL,
            `open` BIGINT UNSIGNED DEFAULT 0,
            `click` BIGINT UNSIGNED DEFAULT 0,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (`id`)
        );

        -- -----------------------------------------------------
        -- Table `emails`
        -- -----------------------------------------------------
        CREATE TABLE `{$db_prefix}emails` (
            `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            `contact_id` BIGINT UNSIGNED NOT NULL,
            `subject` VARCHAR(255) NOT NULL,
            `pre_header` VARCHAR(255),
            `utm_parameters` TEXT,
            `body` LONGTEXT,
            `status` ENUM('pending', 'sent', 'opened', 'clicked', 'bounced', 'unsubscribed') NOT NULL DEFAULT 'pending',
            `sent_at` TIMESTAMP NULL,
            `opened_at` TIMESTAMP NULL,
            `clicked_at` TIMESTAMP NULL,
            `bounced_at` TIMESTAMP NULL,
            `unsubscribed_at` TIMESTAMP NULL,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            `updated_at` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (`id`)
        );";

        dbDelta( $sql );

        // Add constraints if they do not exist
        $this->add_constraints( $db_prefix );

        return true;
    }

    private function add_constraints( $db_prefix ) {
        global $wpdb;

        // Define constraints
        $tables = [
            'entity_tags'         => [
                'tag_id' => "{$db_prefix}tags(id)"
            ],
            'entity_lists'        => [
                'list_id' => "{$db_prefix}lists(id)"
            ],
            'notes'               => [
                'contact_id' => "{$db_prefix}contacts(id)"
            ],
            'campaigns'           => [
                'created_by' => "{$wpdb->prefix}users(ID) - on_delete_null",
                'parent_id'  => "{$db_prefix}campaigns(id)",
                'segment_id' => "{$db_prefix}segments(id) - on_delete_null",
            ],
            'campaign_recipients' => [
                'campaign_id' => "{$db_prefix}campaigns(id)",
                'contact_id'  => "{$db_prefix}contacts(id)",
            ],
            'emails'              => [
                'contact_id' => "{$db_prefix}contacts(id)",
            ]
        ];

        foreach ( $tables as $table => $constraints ) {
            $this->add_constraints_to_table( $table, $constraints, $db_prefix );
        }
    }

    public function add_constraints_to_table( $table, $constraints, $db_prefix ) {
        global $wpdb;

        $table_name = $db_prefix . $table;
        $columns    = $this->get_columns_by_table_name( $table_name );

        // Add foreign key constraints if columns exist
        foreach ( $constraints as $column => $reference ) {
            if ( ! in_array( $column, $columns, true ) ) {
                continue;
            }

            $constraint_name = "fk_{$db_prefix}{$table}_{$column}";
            
            // Check if the constraint already exists
            if ( $this->is_constraint_exists( $table_name, $constraint_name ) ) {
                continue;
            }

            $reference_items    = explode( '-', $reference );
            $reference          = trim( $reference_items[0] );
            $on_delete_behavior = $this->get_delete_behavior( $reference_items );

            // Add foreign key constraint
            // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnquotedComplexPlaceholder, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
            $wpdb->query( $wpdb->prepare( "ALTER TABLE %1s ADD CONSTRAINT %2s FOREIGN KEY ( %3s ) REFERENCES %4s ON DELETE %5s", [$table_name, $constraint_name, $column, $reference, $on_delete_behavior] ) );
        }
    }
    
    private function get_columns_by_table_name( string $table_name ) {
        global $wpdb;
        //phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnquotedComplexPlaceholder, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching	
        return $wpdb->get_col( $wpdb->prepare( "DESCRIBE %1s", [$table_name] ), 0 );
    }

    private function is_constraint_exists( string $table_name, string $constraint_name ) {
        global $wpdb;
        //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching	
        $result = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT CONSTRAINT_NAME 
                FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
                WHERE TABLE_SCHEMA = %s 
                AND TABLE_NAME = %s 
                AND CONSTRAINT_NAME = %s",
                DB_NAME,
                $table_name,
                $constraint_name
            )
        );

        return ! empty( $result );
    }

    private function get_delete_behavior( array $reference_items ) {
        if ( empty( $reference_items[1] ) ) {
            return 'CASCADE';
        }

        $behavior = trim( $reference_items[1] );

        if ( 'on_delete_null' === $behavior ) {
            return 'SET NULL';
        }

        return 'CASCADE';
    }
}