<?php
/**
 * @package Pods\Upgrade
 */
global $wpdb;

if ( version_compare( $old_version, '1.2.6', '<' ) ) {
    // Add the "required" option
    pods_query( "ALTER TABLE @wp_pod_fields ADD COLUMN required BOOL default 0 AFTER sister_field_id" );

    // Add the "label" option
    pods_query( "ALTER TABLE @wp_pod_fields ADD COLUMN label VARCHAR(32) AFTER name" );

    // Fix table prefixes
    if ( !empty( $table_prefix ) ) {
        $result = $wpdb->get_results( "SHOW TABLES LIKE 'tbl_%'", ARRAY_N );

        foreach ( $result as $row ) {
            pods_query( "RENAME TABLE `{$row[0]}` TO `@wp_{$row[0]}`" );
        }
    }

    // Change the "post_type" of all pod items
    $result = pods_query( "SELECT id, name FROM @wp_pod_types" );

    foreach ( $result as $row ) {
        $datatypes[ $row->id ] = $row->name;
    }

    $result = pods_query( "SELECT post_id, datatype FROM @wp_pod" );

    foreach ( $result as $row ) {
        $datatype = $datatypes[ $row->datatype ];

        pods_query( "UPDATE @wp_posts SET post_type = '$datatype' WHERE ID = $row[0] LIMIT 1" );
    }

    update_option( 'pods_version', '126' );
}

if ( version_compare( $old_version, '1.2.7', '<' ) ) {
    // Add the "comment" option
    pods_query( "ALTER TABLE @wp_pod_fields ADD COLUMN comment VARCHAR(128) AFTER label" );

    update_option( 'pods_version', '127' );
}

if ( version_compare( $old_version, '1.3.1', '<' ) ) {
    $result = $wpdb->get_results( "SHOW TABLES LIKE '{$wpdb->prefix}tbl_%'", ARRAY_N );

    foreach ( $result as $row ) {
        $rename = explode( 'tbl_', $row[ 0 ] );
        pods_query( "RENAME TABLE `{$row[0]}` TO `@wp_pod_tbl_{$rename[1]}`" );
    }

    update_option( 'pods_version', '131' );
}

if ( version_compare( $old_version, '1.3.2', '<' ) ) {
    pods_query( "UPDATE @wp_pod_pages SET phpcode = CONCAT('<" . "?" . "php\n', phpcode) WHERE phpcode NOT LIKE '" . "?" . ">%'" );
    pods_query( "UPDATE @wp_pod_pages SET phpcode = SUBSTR(phpcode, 3) WHERE phpcode LIKE '" . "?" . ">%'" );
    pods_query( "UPDATE @wp_pod_widgets SET phpcode = CONCAT('<" . "?" . "php\n', phpcode) WHERE phpcode NOT LIKE '" . "?" . ">%'" );
    pods_query( "UPDATE @wp_pod_widgets SET phpcode = SUBSTR(phpcode, 3) WHERE phpcode LIKE '" . "?" . ">%'" );

    update_option( 'pods_version', '132' );
}

if ( version_compare( $old_version, '1.4.3', '<' ) ) {
    $result = pods_query( "SHOW COLUMNS FROM @wp_pod_types LIKE 'description'" );

    if ( 0 < count( $result ) )
        pods_query( "ALTER TABLE @wp_pod_types CHANGE description label VARCHAR(32)" );

    pods_query( "ALTER TABLE @wp_pod_types ADD COLUMN is_toplevel BOOL default 0 AFTER label" );

    update_option( 'pods_version', '143' );
}

if ( version_compare( $old_version, '1.4.5', '<' ) ) {
    pods_query( "ALTER TABLE @wp_pod_pages ADD COLUMN title VARCHAR(128) AFTER uri" );

    $sql = "
        CREATE TABLE @wp_pod_menu (
            id INT unsigned auto_increment primary key,
            uri VARCHAR(128),
            title VARCHAR(128),
            lft INT unsigned,
            rgt INT unsigned,
            weight TINYINT unsigned default 0)";
    pods_query( $sql );

    pods_query( "INSERT INTO @wp_pod_menu (uri, title, lft, rgt) VALUES ('/', 'Home', 1, 2)" );

    update_option( 'pods_version', '145' );
}

if ( version_compare( $old_version, '1.4.8', '<' ) ) {
    add_option( 'pods_roles' );

    update_option( 'pods_version', '148' );
}

if ( version_compare( $old_version, '1.4.9', '<' ) ) {
    pods_query( "RENAME TABLE @wp_pod_widgets TO @wp_pod_helpers" );

    update_option( 'pods_version', '149' );
}

if ( version_compare( $old_version, '1.5', '<' ) ) {
    pods_query( "ALTER TABLE @wp_pod_fields ADD COLUMN `unique` BOOL default 0 AFTER required" );
    pods_query( "ALTER TABLE @wp_pod_fields ADD COLUMN `multiple` BOOL default 0 AFTER `unique`" );
    pods_query( "ALTER TABLE @wp_pod_pages ADD COLUMN page_template VARCHAR(128) AFTER phpcode" );
    pods_query( "ALTER TABLE @wp_pod_helpers ADD COLUMN helper_type VARCHAR(16) AFTER name" );
    pods_query( "ALTER TABLE @wp_pod ADD COLUMN name VARCHAR(128) AFTER datatype" );
    pods_query( "ALTER TABLE @wp_pod ADD COLUMN created VARCHAR(128) AFTER name" );
    pods_query( "ALTER TABLE @wp_pod ADD COLUMN modified VARCHAR(128) AFTER created" );
    pods_query( "ALTER TABLE @wp_pod CHANGE row_id tbl_row_id INT unsigned" );
    pods_query( "ALTER TABLE @wp_pod_rel CHANGE term_id tbl_row_id INT unsigned" );
    pods_query( "ALTER TABLE @wp_pod_rel CHANGE post_id pod_id INT unsigned" );
    pods_query( "ALTER TABLE @wp_pod_rel CHANGE sister_post_id sister_pod_id INT unsigned" );

    // Make all pick columns "multiple" for consistency
    pods_query( "UPDATE @wp_pod_fields SET `multiple` = 1 WHERE coltype = 'pick'" );

    // Use "display" as the default helper type
    pods_query( "UPDATE @wp_pod_helpers SET helper_type = 'display'" );

    // Replace all post_ids with its associated pod_id
    $sql = "
    SELECT
        p.id, p.post_id, r.post_title AS name, r.post_date AS created, r.post_modified AS modified
    FROM
        @wp_pod p
    INNER JOIN
        @wp_posts r ON r.ID = p.post_id
    ";

    $result = pods_query( $sql );

    foreach ( $result as $row ) {
        $row = get_object_vars( $row );

        foreach ( $row as $key => $val ) {
            ${$key} = esc_sql( trim( $val ) );
        }

        $posts_to_delete[] = $post_id;
        $all_pod_ids[ $post_id ] = $id;

        pods_query( "UPDATE @wp_pod SET name = '$name', created = '$created', modified = '$modified' WHERE id = $id LIMIT 1" );
    }

    // Replace post_id with pod_id
    $result = pods_query( "SELECT id, pod_id, sister_pod_id FROM @wp_pod_rel" );

    foreach ( $result as $row ) {
        $id = $row->id;
        $new_pod_id = $all_pod_ids[ $row->pod_id ];
        $new_sister_pod_id = $all_pod_ids[ $row->sister_pod_id ];

        pods_query( "UPDATE @wp_pod_rel SET pod_id = '$new_pod_id', sister_pod_id = '$new_sister_pod_id' WHERE id = '$id' LIMIT 1" );
    }

    $posts_to_delete = implode( ',', $posts_to_delete );

    // Remove all traces from wp_posts
    pods_query( "ALTER TABLE @wp_pod DROP COLUMN post_id" );
    pods_query( "DELETE FROM @wp_posts WHERE ID IN ($posts_to_delete)" );

    update_option( 'pods_version', '150' );
}

if ( version_compare( $old_version, '1.5.1', '<' ) ) {
    pods_query( "ALTER TABLE @wp_pod_fields ADD COLUMN helper VARCHAR(32) AFTER label" );
    pods_query( "ALTER TABLE @wp_pod_types ADD COLUMN before_helpers TEXT AFTER tpl_list" );
    pods_query( "ALTER TABLE @wp_pod_types ADD COLUMN after_helpers TEXT AFTER before_helpers" );

    update_option( 'pods_version', '151' );
}

if ( version_compare( $old_version, '1.6.0', '<' ) ) {
    // Add the "templates" table
    $sql = "
    CREATE TABLE IF NOT EXISTS @wp_pod_templates (
        id INT unsigned auto_increment primary key,
        name VARCHAR(32),
        code TEXT)";
    pods_query( $sql );

    // Add list and detail template presets
    $tpl_list = '<p><a href="{@detail_url}">{@name}</a></p>';
    $tpl_detail = "<h2>{@name}</h2>\n{@body}";
    pods_query( "INSERT INTO @wp_pod_templates (name, code) VALUES ('detail', '$tpl_detail'),('list', '$tpl_list')" );

    // Try to route old templates as best as possible
    $result = pods_query( "SELECT name, tpl_detail, tpl_list FROM @wp_pod_types" );

    foreach ( $result as $row ) {
        // Create the new template, e.g. "dtname_list" or "dtname_detail"
        $row = pods_sanitize( $row );

        pods_query( "INSERT INTO @wp_pod_templates (name, code) VALUES ('{$row->name}_detail', '{$row->tpl_detail}'),('{$row->name}_list', '{$row->tpl_list}')" );
    }

    // Drop the "tpl_detail" and "tpl_list" columns
    pods_query( "ALTER TABLE @wp_pod_types DROP COLUMN tpl_detail, DROP COLUMN tpl_list" );

    // Add the "pick_filter" column
    pods_query( "ALTER TABLE @wp_pod_fields ADD COLUMN pick_filter VARCHAR(128) AFTER pickval" );

    update_option( 'pods_version', '160' );
}

if ( version_compare( $old_version, '1.6.2', '<' ) ) {
    // Remove all beginning and ending slashes from Pod Pages
    pods_query( "UPDATE @wp_pod_pages SET uri = TRIM(BOTH '/' FROM uri)" );

    update_option( 'pods_version', '162' );
}

if ( version_compare( $old_version, '1.6.4', '<' ) ) {
    pods_query( "ALTER TABLE @wp_pod_fields ADD COLUMN pick_orderby TEXT AFTER pick_filter" );
    pods_query( "ALTER TABLE @wp_pod_fields CHANGE helper display_helper TEXT" );
    pods_query( "ALTER TABLE @wp_pod_fields ADD COLUMN input_helper TEXT AFTER display_helper" );

    update_option( 'pods_version', '164' );
}

if ( version_compare( $old_version, '1.6.7', '<' ) ) {
    pods_query( "ALTER TABLE @wp_pod_pages ADD COLUMN precode LONGTEXT AFTER phpcode" );

    update_option( 'pods_version', '167' );
}

if ( version_compare( $old_version, '1.7.3', '<' ) ) {
    pods_query( "ALTER TABLE @wp_pod_types ADD COLUMN detail_page VARCHAR(128) AFTER is_toplevel" );

    update_option( 'pods_version', '173' );
}

if ( version_compare( $old_version, '1.7.5', '<' ) ) {
    if ( empty( $pods_roles ) && !is_array( $pods_roles ) ) {
        $pods_roles = @unserialize( get_option( 'pods_roles' ) );

        if ( !is_array( $pods_roles ) )
            $pods_roles = array();
    }

    if ( is_array( $pods_roles ) ) {
        foreach ( $pods_roles as $role => $privs ) {
            if ( in_array( 'manage_podpages', $privs ) ) {
                $pods_roles[ $role ][] = 'manage_pod_pages';

                unset( $pods_roles[ $role ][ 'manage_podpages' ] );
            }
        }
    }

    delete_option( 'pods_roles' );
    add_option( 'pods_roles', serialize( $pods_roles ) );

    update_option( 'pods_version', '175' );
}

if ( version_compare( $old_version, '1.7.6', '<' ) ) {
    pods_query( "ALTER TABLE @wp_pod_types CHANGE label label VARCHAR(128)" );
    pods_query( "ALTER TABLE @wp_pod_fields CHANGE label label VARCHAR(128)" );

    $result = pods_query( "SELECT f.id AS field_id, f.name AS field_name, f.datatype AS datatype_id, dt.name AS datatype FROM @wp_pod_fields AS f LEFT JOIN @wp_pod_types AS dt ON dt.id = f.datatype WHERE f.coltype='file'" );

    foreach ( $result as $row ) {
        $items = pods_query( "SELECT t.id AS tbl_row_id, t.{$row->field_name} AS file, p.id AS pod_id FROM @wp_pod_tbl_{$row->datatype} AS t LEFT JOIN @wp_pod AS p ON p.tbl_row_id = t.id AND p.datatype = {$row->datatype_id} WHERE t.{$row->field_name} != '' AND t.{$row->field_name} IS NOT NULL" );
        $success = false;
        $rels = array();

        foreach ( (array) $items as $item ) {
            $filename = $item->file;

            if ( strpos( $filename, get_site_url() ) !== false && strpos( $filename, get_site_url() ) == 0 )
                $filename = ltrim( $filename, get_site_url() );

            $upload_dir = wp_upload_dir();

            if ( strpos( $filename, str_replace( get_site_url(), '', $upload_dir[ 'baseurl' ] ) ) === false ) {
                $success = false;

                break;
            }

            $file = str_replace( '//', '/', ( ABSPATH . $filename ) );

            $wp_filetype = wp_check_filetype( basename( $file ), null );

            $attachment = array(
                'post_mime_type' => $wp_filetype[ 'type' ],
                'post_title' => preg_replace( '/\.[^.]+$/', '', basename( $file ) ),
                'guid' => str_replace( '//wp-content/', '/wp-content/', get_site_url() . $filename ),
                'post_content' => '',
                'post_status' => 'inherit'
            );

            $attach_id = wp_insert_attachment( $attachment, $file, 0 );

            if ( $attach_id > 0 ) {
                require_once( ABSPATH . "wp-admin" . '/includes/image.php' );

                $attach_data = wp_generate_attachment_metadata( $attach_id, $file );

                wp_update_attachment_metadata( $attach_id, $attach_data );

                $sizes = array( 'thumb', 'medium', 'large' );

                foreach ( $sizes as $size ) {
                    image_downsize( $attach_id, $size );
                }

                $rels[] = array( 'pod_id' => $item->pod_id, 'tbl_row_id' => $item->tbl_row_id, 'attach_id' => $attach_id, 'field_id' => $row->field_id );

                $success = true;
            }
        }
        if ( false !== $success ) {
            foreach ( $rels as $rel ) {
                pods_query( "INSERT INTO @wp_pod_rel (pod_id, field_id, tbl_row_id) VALUES({$rel['pod_id']}, {$rel['field_id']}, {$rel['attach_id']})" );
            }

            pods_query( "ALTER TABLE @wp_pod_tbl_{$row->datatype} DROP COLUMN {$row->field_name}" );
        }
        else
            pods_query( "UPDATE @wp_pod_fields SET coltype = 'txt' WHERE id = {$row->field_id}" );
    }

    update_option( 'pods_version', '176' );
}

if ( version_compare( $old_version, '1.8.1', '<' ) ) {
    pods_query( "ALTER TABLE @wp_pod_rel ADD COLUMN weight SMALLINT unsigned AFTER tbl_row_id" );
    pods_query( "ALTER TABLE @wp_pod_types CHANGE before_helpers pre_save_helpers TEXT" );
    pods_query( "ALTER TABLE @wp_pod_types CHANGE after_helpers post_save_helpers TEXT" );
    pods_query( "ALTER TABLE @wp_pod_types ADD COLUMN pre_drop_helpers TEXT AFTER pre_save_helpers" );
    pods_query( "ALTER TABLE @wp_pod_types ADD COLUMN post_drop_helpers TEXT AFTER post_save_helpers" );
    pods_query( "UPDATE @wp_pod_helpers SET helper_type = 'pre_save' WHERE helper_type = 'before'" );
    pods_query( "UPDATE @wp_pod_helpers SET helper_type = 'post_save' WHERE helper_type = 'after'" );

    update_option( 'pods_version', '181' );
}

if ( version_compare( $old_version, '1.8.2', '<' ) ) {
    pods_query( "ALTER TABLE @wp_pod ADD COLUMN author_id INT unsigned AFTER modified" );
    pods_query( "UPDATE @wp_pod_fields SET pickval = 'wp_taxonomy' WHERE pickval REGEXP '^[0-9]+$'" );
    pods_query( "UPDATE @wp_pod_menu SET uri = '<root>' WHERE uri = '/' LIMIT 1" );

    // Remove beginning and trailing slashes
    $result = pods_query( "SELECT id, uri FROM @wp_pod_menu" );

    foreach ( $result as $row ) {
        $uri = preg_replace( "@^([/]?)(.*?)([/]?)$@", "$2", $row->uri );
        $uri = pods_sanitize( $uri );
        pods_query( "UPDATE @wp_pod_menu SET uri = '$uri' WHERE id = {$row->id} LIMIT 1" );
    }

    update_option( 'pods_version', '182' );
}

if ( version_compare( $old_version, '1.9.0', '<' ) ) {
    pods_query( "ALTER TABLE @wp_pod_templates CHANGE `name` `name` VARCHAR(255)" );
    pods_query( "ALTER TABLE @wp_pod_helpers CHANGE `name` `name` VARCHAR(255)" );
    pods_query( "ALTER TABLE @wp_pod_fields CHANGE `comment` `comment` VARCHAR(255)" );

    // Remove beginning and trailing slashes
    $result = pods_query( "SELECT id, uri FROM @wp_pod_pages" );

    foreach ( $result as $row ) {
        $uri = trim( $row->uri, '/' );
        $uri = pods_sanitize( $uri );
        pods_query( "UPDATE @wp_pod_pages SET uri = '$uri' WHERE id = {$row->id} LIMIT 1" );
    }

    update_option( 'pods_version', '190' );
}

if ( version_compare( $old_version, '1.9.6', '<' ) ) {
    add_option( 'pods_disable_file_browser', 0 );
    add_option( 'pods_files_require_login', 0 );
    add_option( 'pods_files_require_login_cap', 'upload_files' );
    add_option( 'pods_disable_file_upload', 0 );
    add_option( 'pods_upload_require_login', 0 );
    add_option( 'pods_upload_require_login_cap', 'upload_files' );

    update_option( 'pods_version', '196' );
}

if ( version_compare( $old_version, '1.9.7', '<' ) ) {
    pods_query( "ALTER TABLE `@wp_pod` CHANGE `id` `id` BIGINT(15) UNSIGNED NOT NULL AUTO_INCREMENT" );
    pods_query( "ALTER TABLE `@wp_pod` CHANGE `tbl_row_id` `tbl_row_id` BIGINT(15) UNSIGNED NULL DEFAULT NULL" );
    pods_query( "ALTER TABLE `@wp_pod` CHANGE `author_id` `author_id` BIGINT(15) UNSIGNED NULL DEFAULT NULL" );
    pods_query( "ALTER TABLE `@wp_pod_rel` CHANGE `id` `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT" );
    pods_query( "ALTER TABLE `@wp_pod_rel` CHANGE `pod_id` `pod_id` BIGINT(15) UNSIGNED NULL DEFAULT NULL" );
    pods_query( "ALTER TABLE `@wp_pod_rel` CHANGE `sister_pod_id` `sister_pod_id` BIGINT(15) UNSIGNED NULL DEFAULT NULL" );
    pods_query( "ALTER TABLE `@wp_pod_rel` CHANGE `tbl_row_id` `tbl_row_id` BIGINT(15) UNSIGNED NULL DEFAULT NULL" );
    pods_query( "ALTER TABLE `@wp_pod_rel` CHANGE `weight` `weight` INT(10) UNSIGNED NULL DEFAULT '0'" );

    update_option( 'pods_version', '197' );
}

if ( version_compare( $old_version, '1.11', '<' ) ) {
    pods_query( "ALTER TABLE `@wp_pod` CHANGE `datatype` `datatype` INT(10) UNSIGNED NULL DEFAULT NULL" );
    pods_query( "ALTER TABLE `@wp_pod` DROP INDEX `datatype_idx`", false );
    pods_query( "ALTER TABLE `@wp_pod` ADD INDEX `datatype_row_idx` (`datatype`, `tbl_row_id`)", false );
    pods_query( "ALTER TABLE `@wp_pod_rel` DROP INDEX `field_id_idx`", false );
    pods_query( "ALTER TABLE `@wp_pod_rel` ADD INDEX `field_pod_idx` (`field_id`, `pod_id`)", false );
    pods_query( "ALTER TABLE `@wp_pod_fields` CHANGE `datatype` `datatype` INT(10) UNSIGNED NULL DEFAULT NULL" );
    $result = pods_query( "SELECT id, name FROM @wp_pod_types" );
    foreach ( $result as $row ) {
        $pod = pods_sanitize( $row->name );
        pods_query( "ALTER TABLE `@wp_pod_tbl_{$pod}` CHANGE `id` `id` BIGINT(15) UNSIGNED NOT NULL AUTO_INCREMENT" );
    }
    update_option( 'pods_version', '001011000' );
}