<?php

/**
 * Price talbe creation
 * This table stores the all 
 * Price value  
 */
class geocraft_db {

	var $table_prefix = '';
	var $price_table_name = '';
	var $transection_table_name = '';
	var $inquiry_tbl_name = '';
	var $currency_tbl_name = '';
	var $cfield_tbl_name = '';
	var $expiry_tbl_name = '';

	public function __construct() {
		global $wpdb;
		$this->table_prefix = $wpdb->prefix;
		$this->price_table_name = $this->table_prefix . 'price';
		$this->transection_table_name = $this->table_prefix . 'transactions';
		$this->inquiry_tbl_name = $this->table_prefix . 'inquiry';
		$this->currency_tbl_name = $this->table_prefix . 'currency';
		$this->cfield_tbl_name = $this->table_prefix . 'custom_field';
		$this->expiry_tbl_name = $this->table_prefix . 'listing_expiry';
	}

}

global $wpdb;
$gcdb_obj = new geocraft_db();
$price_table_name = $gcdb_obj->price_table_name;
$transection_table_name = $gcdb_obj->transection_table_name;
$inquiry_tbl_name = $gcdb_obj->inquiry_tbl_name;
$currency_tbl_name = $gcdb_obj->currency_tbl_name;
$cfield_tbl_name = $gcdb_obj->cfield_tbl_name;
$expiry_tbl_name = $gcdb_obj->expiry_tbl_name;
define( 'GC_PRICE_TBL', $price_table_name );
define( 'GC_INQUIRY_TBL', $inquiry_tbl_name );
define( 'GC_EXPIRY_TBL', $expiry_tbl_name );
define( 'GC_CFIELD_TBL', $cfield_tbl_name );
define( 'GC_CURRENCY_TBL', $currency_tbl_name );
define( 'GC_TRANS_TBL', $transection_table_name );
define( 'GC_RATING_TBL', $wpdb->prefix . 'post_ratings' );

if ( $wpdb->get_var( "SHOW TABLES LIKE \"$price_table_name\"" ) != $price_table_name ) {
	$price_table = 'CREATE TABLE IF NOT EXISTS ' . $price_table_name . ' (
	  `pid` int(11) NOT NULL AUTO_INCREMENT,
	  `price_title` varchar(255) NOT NULL,
	  `price_desc` varchar(1000) NOT NULL,
	  `price_post_cat` varchar(100) NOT NULL,
	  `is_show` varchar(10) NOT NULL,
	  `package_cost` int(255) NOT NULL,
	  `validity` int(10) NOT NULL,
	  `validity_per` varchar(10) NOT NULL,
	  `status` int(10) NOT NULL ,
	  `is_recurring` int(10) NOT NULL ,
          `first_billing_per` int(10) NOT NULL,
	  `first_billing_cycle` varchar(10) NOT NULL,
	  `rebill_time` int(10) NOT NULL,
          `rebill_period` int(10) NOT NULL,
          `second_price` int(255) NOT NULL,
          `second_billing_per` int(10) NOT NULL,
          `second_billing_cycle` varchar(10) NOT NULL,          	  
	  `is_featured` int(10) NOT NULL,
	  `feature_amount` int(10) NOT NULL,
	  `feature_cat_amount` int(10) NOT NULL,
          `package_type` varchar(100) NOT NULL,
          `package_type_period` varchar(100) NOT NULL,
	  PRIMARY KEY (`pid`)
	)';
	$wpdb->query( $price_table );
	$insert_value1 = array(
		'pid' => 1,
		'price_title' => 'Free Business Listing',
		'price_desc' => "No Charge for placing your listing. You won't receive any leads. Listing Expires after 4 Months. You can reactivate listing later",
		'price_post_cat' => '',
		'is_show' => '1',
		'package_cost' => 0,
		'validity' => 4,
		'validity_per' => 'M',
		'status' => 1,
		'is_recurring' => '',
		'first_billing_per' => '',
		'first_billing_cycle' => '',
		'rebill_time' => '',
		'rebill_period' => '',
		'second_price' => '',
		'second_billing_per' => '',
		'second_billing_cycle' => '',
		'is_featured' => '',
		'feature_amount' => '',
		'feature_cat_amount' => '',
		'package_type' => 'pkg_free',
		'package_type_period' => 'pkg_period_one'
	);
	$insert_value2 = array(
		'pid' => 2,
		'price_title' => 'One Time Listing Payment',
		'price_desc' => "One time charge for placing your listing. You can feature the listing on Homepage or Category Page. You will receive leads in your Dashboard. Listing Expires after 6 Months. You can reactivate listing later for no extra charge.",
		'price_post_cat' => '',
		'is_show' => 1,
		'package_cost' => 26,
		'validity' => 6,
		'validity_per' => 'M',
		'status' => 1,
		'is_recurring' => '',
		'first_billing_per' => '',
		'first_billing_cycle' => '',
		'rebill_time' => '',
		'rebill_period' => '',
		'second_price' => '',
		'second_billing_per' => '',
		'second_billing_cycle' => '',
		'is_featured' => 1,
		'feature_amount' => 12,
		'feature_cat_amount' => 10,
		'package_type' => 'pkg_one_time',
		'package_type_period' => 'pkg_period_two'
	);
	$insert_value3 = array(
		'pid' => 3,
		'price_title' => "Recurring Listing Payment",
		'price_desc' => "Small recurring charge for placing your listing. You can feature the listing on Homepage or Category Page. You will receive leads in your Dashboard. No Listing expiry till you have active subscription.",
		'price_post_cat' => '',
		'is_show' => 1,
		'package_cost' => 36,
		'validity' => 6,
		'validity_per' => 'M',
		'status' => 1,
		'is_recurring' => 1,
		'first_billing_per' => 1,
		'first_billing_cycle' => 'M',
		'rebill_time' => 1,
		'rebill_period' => 12,
		'second_price' => 15,
		'second_billing_per' => 6,
		'second_billing_cycle' => 'M',
		'is_featured' => 1,
		'feature_amount' => 22,
		'feature_cat_amount' => 15,
		'package_type' => 'pkg_recurring',
		'package_type_period' => 'pkg_period_three'
	);
	$wpdb->insert( $price_table_name, $insert_value1 );
	$wpdb->insert( $price_table_name, $insert_value2 );
	$wpdb->insert( $price_table_name, $insert_value3 );
} else {
	if ( !get_option( 'gc_price_column_altered' ) ) {
		$wpdb->query( "ALTER TABLE $price_table_name MODIFY package_cost FLOAT(100,2);" );
		$wpdb->query( "ALTER TABLE $price_table_name MODIFY second_price FLOAT(100,2);" );
		$wpdb->query( "ALTER TABLE $price_table_name MODIFY feature_amount FLOAT(100,2);" );
		$wpdb->query( "ALTER TABLE $price_table_name MODIFY feature_cat_amount FLOAT(100,2);" );
		update_option( 'gc_price_column_altered', 'altered' );
	}
}


/**
 * Transaction table for recording all transaction 
 */
if ( $wpdb->get_var( "SHOW TABLES LIKE \"$transection_table_name\"" ) != $transection_table_name ) {
	$transaction_table = 'CREATE TABLE IF NOT EXISTS `' . $transection_table_name . '` (
	`trans_id` bigint(20) NOT NULL AUTO_INCREMENT,
	`user_id` bigint(20) NOT NULL,
	`post_id` bigint(20) NOT NULL,
	`post_title` varchar(255) NOT NULL,
	`status` int(2) NOT NULL,
	`payment_method` varchar(255) NOT NULL,
	`payable_amt` float(25,2) NOT NULL,
	`payment_date` datetime NOT NULL,
	`paypal_transection_id` varchar(255) NOT NULL,
	`user_name` varchar(255) NOT NULL,
	`pay_email` varchar(255) NOT NULL,
	`billing_name` varchar(255) NOT NULL,
	`billing_add` text NOT NULL,
	PRIMARY KEY (`trans_id`)
	)';
	$wpdb->query( $transaction_table );
}

/**
 * Create Payment methods and their each value 
 * Store in wp_options 
 */
//Paypal
$payout = array();
$payout[] = array(
	"title" => 'Merchant Id',
	"fieldname" => "merchantid",
	"value" => "myaccount@paypal.com",
	"description" => MERCHANT_DES,
);
$payout[] = array(
	"title" => 'Cancel Url',
	"fieldname" => "cancel_return",
	"value" => site_url( "" ),
	"description" => sprintf( __( EXMPL . " %s", THEME_SLUG ), site_url( "" ) ),
);
$payout[] = array(
	"title" => 'Return Url',
	"fieldname" => "returnUrl",
	"value" => site_url( "" ),
	"description" => sprintf( __( EXMPL . " %s", THEME_SLUG ), site_url( "" ) ),
);
$payout[] = array(
	"title" => 'Notify Url',
	"fieldname" => "notify_url",
	"value" => site_url( "" ),
	"description" => sprintf( __( EXMPL . " %s", THEME_SLUG ), site_url( "" ) ),
);

$paymethods[] = array(
	"name" => __( 'Paypal', THEME_SLUG ),
	"key" => 'paypal',
	"isactive" => '1', // 1->display,0->hide
	"display_order" => '1',
	"paypal_sandbox" => '0',
	"payOpts" => $payout,
);
//Google checkout
$payout = array();
$payout[] = array(
	"title" => MERCHANT_ID_TEXT,
	"fieldname" => "merchantid",
	"value" => "1234567890",
	"description" => __( EXMPL . " 1234567890", THEME_SLUG )
);

$paymethods[] = array(
	"name" => 'Google Checkout',
	"key" => 'googlechkout',
	"isactive" => '1', // 1->display,0->hide
	"display_order" => '2',
	"payOpts" => $payout,
);

//Authorize.net
$payout = array();
$payout[] = array(
	"title" => LOGIN_ID_TEXT,
	"fieldname" => "loginid",
	"value" => "yourname@domain.com",
	"description" => LOGIN_ID_NOTE
);
$payout[] = array(
	"title" => TRANS_KEY_TEXT,
	"fieldname" => "transkey",
	"value" => "1234567890",
	"description" => TRANS_KEY_NOTE,
);

$paymethods[] = array(
	"name" => __( 'Authorize.net', THEME_SLUG ),
	"key" => 'authorizenet',
	"isactive" => '1', // 1->display,0->hide
	"display_order" => '3',
	"payOpts" => $payout,
);

//Worldpay
$payout = array();
$payout[] = array(
	"title" => INSTANT_ID_TEXT,
	"fieldname" => "instId",
	"value" => "123456",
	"description" => INSTANT_ID_NOTE
);
$payout[] = array(
	"title" => ACCOUNT_ID_TEXT,
	"fieldname" => "accId1",
	"value" => "12345",
	"description" => ACCOUNT_ID_NOTE
);

$paymethods[] = array(
	"name" => WORLD_PAY_TEXT,
	"key" => 'worldpay',
	"isactive" => '1', // 1->display,0->hide
	"display_order" => '4',
	"payOpts" => $payout,
);
//////////worldpay end////////
//////////2co start////////

$payout = array();
$payout[] = array(
	"title" => VENDOR_ID_TEXT,
	"fieldname" => "vendorid",
	"value" => "1303908",
	"description" => VENDOR_ID_NOTE
);
$payout[] = array(
	"title" => NOTIFY_URL_TEXT,
	"fieldname" => "ipnfilepath",
	"value" => site_url( "" ),
	"description" => sprintf( __( "Example : %s", THEME_SLUG ), site_url( "" ) ),
);

$paymethods[] = array(
	"name" => __( '2CO (2Checkout)', THEME_SLUG ),
	"key" => '2co',
	"isactive" => '1', // 1->display,0->hide
	"display_order" => '5',
	"payOpts" => $payout,
);

//Pre bank transfer
$payout = array();
$payout[] = array(
	"title" => BANK_INFO_TEXT,
	"fieldname" => "bankinfo",
	"value" => "State Bank Of India",
	"description" => BANK_INFO_NOTE
);
$payout[] = array(
	"title" => ACCOUNT_ID_TEXT,
	"fieldname" => "bank_accountid",
	"value" => "AB1234567890",
	"description" => ACCOUNT_ID_NOTE2,
);

$paymethods[] = array(
	"name" => PRE_BANK_TRANSFER_TEXT,
	"key" => 'prebanktransfer',
	"isactive" => '1', // 1->display,0->hide
	"display_order" => '6',
	"payOpts" => $payout,
);

//Pay cash on delivery
$payout = array();
$paymethods[] = array(
	"name" => PAY_CASH_TEXT,
	"key" => 'payondelevary',
	"isactive" => '1', // 1->display,0->hide
	"display_order" => '7',
	"payOpts" => $payout,
);

//Insert options in payment optiions

for ( $i = 0; $i < count( $paymethods ); $i++ ) {
	$paymentsql = "select * from $wpdb->options where option_name like 'pay_method_" . $paymethods[$i]['key'] . "' order by option_id asc";
	$paymentinfo = $wpdb->get_results( $paymentsql );
	if ( count( $paymentinfo ) == 0 ) {
		$paymethodArray = array(
			"option_name" => 'pay_method_' . $paymethods[$i]['key'],
			"option_value" => serialize( $paymethods[$i] ),
		);
		$wpdb->insert( $wpdb->options, $paymethodArray );
	}
}
//Create table for inquiry form
if ( $wpdb->get_var( "SHOW TABLES LIKE \"$inquiry_tbl_name\"" ) != $inquiry_tbl_name ) {
	$inquiry_table = "CREATE TABLE " . $inquiry_tbl_name . "(
  ID INT(11) NOT NULL AUTO_INCREMENT,
  listing_author VARCHAR(255) DEFAULT NULL,
  listing_id DECIMAL(10, 0) DEFAULT NULL,
  user_name VARCHAR(255) DEFAULT NULL,
  email VARCHAR(255) DEFAULT NULL,
  phone_no VARCHAR(15) DEFAULT NULL,
  message TEXT DEFAULT NULL,
  listing_title VARCHAR(255) DEFAULT NULL,
  inquiry_date datetime DEFAULT NULL,
  form_valid INT(12),
  PRIMARY KEY (ID)
)";
	$wpdb->query( $inquiry_table );
}

/**
 * Create table for listing activation
 * Authentication.
 */
/**
 * This table is not used since 1.7.1
  global $wpdb, $table_prefix;
  $expiry_tbl_name = $table_prefix . 'listing_expiry';
  global $expiry_tbl_name;
  if ($wpdb->get_var("SHOW TABLES LIKE \"$expiry_tbl_name\"") != $expiry_tbl_name) {
  $activation_table = "CREATE TABLE " . $expiry_tbl_name . "(
  ID INT(100) NOT NULL AUTO_INCREMENT,
  pid INT(100) DEFAULT NULL,
  listing_title VARCHAR(100) DEFAULT NULL,
  validity INT(11) DEFAULT NULL,
  validity_per VARCHAR(11) DEFAULT NULL,
  listing_date datetime DEFAULT NULL,
  `package_type` varchar(100) NOT NULL,
  PRIMARY KEY (ID)
  )";
  $wpdb->query($activation_table);
  }
 */
/**
 * Create table for payment currencies
 * 
 */
if ( !get_option( 'currency_symbol' ) ) {
	add_option( 'currency_symbol', '$' );
}

if ( !get_option( 'currency_code' ) ) {
	add_option( 'currency_code', 'USD' );
}

//Add column for show/hide in free listing
$show_free = $wpdb->get_var( "SHOW COLUMNS FROM $cfield_tbl_name LIKE 'show_free'" );
if ( !isset( $show_free ) ) {
	$wpdb->query( "ALTER TABLE $cfield_tbl_name  ADD `show_free` VARCHAR(10) NOT NULL AFTER `show_on_detail`" );
}
//Add column renewal_per for renew listing
$renewal_per = $wpdb->get_var( "SHOW COLUMNS FROM $price_table_name LIKE 'renewal_per'" );
if ( !isset( $renewal_per ) ) {
	$wpdb->query( "ALTER TABLE $price_table_name  ADD `renewal_per` INT(10) NOT NULL AFTER `is_recurring`" );
}
//Add column renewal_cycle for renew listing
$renewal_cycle = $wpdb->get_var( "SHOW COLUMNS FROM $price_table_name LIKE 'renewal_cycle'" );
if ( !isset( $renewal_cycle ) ) {
	$wpdb->query( "ALTER TABLE $price_table_name  ADD `renewal_cycle` VARCHAR(10) NOT NULL AFTER `renewal_per`" );
}

//Transaction Table
//Add column payment type on transaction table 
$txn_type = $wpdb->get_var( "SHOW COLUMNS FROM $transection_table_name LIKE 'txn_type'" );
if ( !isset( $txn_type ) ) {
	$wpdb->query( "ALTER TABLE $transection_table_name  ADD `txn_type` VARCHAR(100) NOT NULL AFTER `payment_date`" );
}

function gc_altertbls_to_charset() {
	global $wpdb;
	$price_table_name = GC_PRICE_TBL;
	$transection_table_name = GC_TRANS_TBL;
	$inquiry_tbl_name = GC_INQUIRY_TBL;
	$cfield_tbl_name = GC_CFIELD_TBL;
//Alter table wp_price collation and character to utf8_general_ci
	$price = "ALTER TABLE $price_table_name CONVERT TO CHARACTER SET utf8 collate utf8_general_ci";
	$wpdb->query( $price );

//Alter table wp_transaction collation and character to utf8_general_ci
	$transaction = "ALTER TABLE $transection_table_name CONVERT TO CHARACTER SET utf8 collate utf8_general_ci";
	$wpdb->query( $transaction );

//Alter table wp_inquiry collation and character to utf8_general_ci
	$inquiry = "ALTER TABLE $inquiry_tbl_name CONVERT TO CHARACTER SET utf8 collate utf8_general_ci";
	$wpdb->query( $inquiry );

	//Alter table wp_custom_field collation and character to utf8_general_ci
	$cfield = "ALTER TABLE $cfield_tbl_name CONVERT TO CHARACTER SET utf8 collate utf8_general_ci";
	$wpdb->query( $cfield );
}

add_action( 'all_admin_notices', 'gc_altertbls_to_charset' );