<?php
include dirname(__FILE__) . '/nocache.php';

include dirname(__FILE__) . '/dbconfig.php';
include dirname(__FILE__) . '/error.php';
include dirname(__FILE__) . '/utils.php';
include dirname(__FILE__) . '/const.php';

include dirname(__FILE__) . '/session.php';

function get_period_master($dbh) {
  $sql = "SELECT * FROM period_master WHERE del_flg='0'";
  $stmt = $dbh->prepare($sql);
  $stmt->execute();
  $list = $stmt->fetchAll(PDO::FETCH_ASSOC);
  $result = array();
  foreach( $list as $item ) {
    $result[$item['id']] = $item;
  }
  return $result;
}

function get_capacity_master($dbh) {
  $sql = "SELECT id,gb FROM capacity_master WHERE del_flg='0'";
  $stmt = $dbh->prepare($sql);
  $stmt->execute();
  $list = $stmt->fetchAll(PDO::FETCH_ASSOC);
  $result = array();
  foreach( $list as $item ) {
    $result[$item['id']] = $item['gb'];
  }
  return $result;
}

function get_session_master($dbh) {
  $sql = "SELECT id,session FROM session_master WHERE del_flg='0'";
  $stmt = $dbh->prepare($sql);
  $stmt->execute();
  $list = $stmt->fetchAll(PDO::FETCH_ASSOC);
  $result = array();
  foreach( $list as $item ) {
    $result[$item['id']] = $item['session'];
  }
  return $result;
}

function check()
{
$POST = json_decode(file_get_contents('php://input'), true);

if (!isset($POST['set_now'])) {
  $POST['set_now'] = 0;
}

check_session();

setTextDomain('myapps');

$options = array(PDO::MYSQL_ATTR_INIT_COMMAND=>"SET CHARACTER SET 'utf8'");
$dbh = new PDO(DB_DSN, DB_USER, DB_PASSWORD, $options);

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

setTimezone($dbh);

error_reporting(E_ALL & ~E_NOTICE);

$dbh->query('BEGIN');

error_log('[[------------------------]]'); 
error_log('[[usr_id: '. var_export($_SESSION['usr_id'], true) . ']]'); 
error_log('[[key: '. var_export($POST["key"], true) . ']]'); 

//入力されたアクティベーションコードの情報取得
$sql = "SELECT id,code, period, capacity, session, plan_id from activation WHERE (linkuser=0 or linkuser=:ids) and exp_flg=0 and del_flg=0 and used = 0 and code = :keys";
$stmt = $dbh->prepare($sql);
$params = array(':keys' => $POST["key"] , ':ids' => $_SESSION['usr_id']);
$stmt->execute($params);
$key_result = $stmt->fetch(PDO::FETCH_ASSOC);

if (!$key_result) {
	return [
    'result' => 'NG',
		'keymessage' => _('アクティベーションコードが正しくありません。')
	];
}
//error_log('[[####################]]'); 
//error_log('[key_result['.var_export($key_result, true).']]'); 
//error_log('[period['.$key_result['period'].']]');

$sql = "SELECT custom_code FROM activation_custom WHERE code = :keys and del_flg='0'";
$stmt = $dbh->prepare($sql);
$stmt->execute(array(':keys' => $POST['key']));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
$custom_codes = array();
foreach($rows as $row) {
    $custom_codes[] = $row['custom_code'];
}

// ユーザーデータ取得
$sql = "SELECT id,member_id, freeend_date, start_date, end_date , capacity, session from userdata WHERE member_id=:mids";
$stmt = $dbh->prepare($sql);
$params = array(':mids' => $_SESSION['member_id']);
$stmt->execute($params);
$userdata_result = $stmt->fetch(PDO::FETCH_ASSOC);

$sql = "SELECT * from user_custom WHERE member_id=:mids";
$stmt = $dbh->prepare($sql);
$stmt->execute(array(':mids' => $_SESSION['member_id']));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
$user_custom = array();
foreach($rows as $row) {
    $user_custom[$row['custom_code']] = $row;
}

$period_master = get_period_master($dbh);

// 期間延長の場合
if((int)$key_result['period'] > 0){
	//期間算出
	$period = $period_master[$key_result['period']];
	$add_days = $period['days'];

	error_log('[[------]]'); 
	error_log('[['.$userdata_result['freeend_date'].']]'); 

  if ($POST['today'] == 1) {
    $sql = <<<EOF
    UPDATE activation as x
      INNER JOIN userdata as u
      SET
        x.end_date=CONVERT_TZ(CAST(
          CONVERT_TZ(NOW(), 'UTC', u.tz)
          as DATE), u.tz, 'UTC'),
        x.lastupdate=NOW(),
        x.upd_log = 'activateKeyEnter.php',
        x.upd_date = NOW()
      WHERE
        u.id=:uid
      AND
        x.used=u.id
      AND
        x.del_flg='0'
      AND
        x.start_date <= NOW()
      AND
        NOW() < x.end_date
EOF;
    $stmt = $dbh->prepare($sql);
    $stmt->execute(array(':uid' => $userdata_result['id']));

    $sql = <<<EOF
    SELECT * 
    FROM activation as x
    WHERE
      x.used=:uid
    AND
      x.del_flg='0'
    AND
      x.start_date > NOW()
    AND
      x.end_date IS NOT NULL
    ORDER BY
      x.start_date ASC
EOF;
    $stmt = $dbh->prepare($sql);
    $stmt->execute(array(':uid' => $userdata_result['id']));
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $sql = <<<EOF
    UPDATE activation as x,
      (SELECT
        @x:=CAST(
          CONVERT_TZ(
            NOW(),
            'UTC', u.tz)
          as DATE) as st,
        u.tz,
        CONVERT_TZ(@x, u.tz, 'UTC') as cst
       FROM userdata as u
       WHERE u.id=:uid) as t
      SET 
      x.start_date=t.cst,
      x.end_date=DATE_ADD(t.cst, INTERVAL :add DAY),
      x.lastupdate=NOW(),
      x.upd_log = 'activateKeyEnter.php',
      x.upd_date = NOW()
      WHERE
      x.id=:id
EOF;
    $stmt = $dbh->prepare($sql);
    $stmt->execute(array(
      ':uid' => $userdata_result['id'],
      ':id' => $key_result['id'],
      ':add' => $add_days
    ));

    if (count($rows) > 0) {
      $sql = <<<EOF
      SELECT
        DATEDIFF(
          a.end_date,
          b.start_date) as diff
      FROM
        activation as a,
        activation as b
      WHERE
        a.id=:id1
      AND
        b.id=:id2
EOF;
      $stmt = $dbh->prepare($sql);
      $stmt->execute(array(
        ':id1' => $key_result['id'],
        ':id2' => $rows[0]['id']
      ));
      $diff = $stmt->fetch(PDO::FETCH_ASSOC);

      $sql = <<<EOF
      UPDATE activation as x
      SET
        x.start_date=DATE_ADD(x.start_date, INTERVAL :diff DAY),
        x.end_date=DATE_ADD(x.end_date, INTERVAL :diff DAY),
        x.lastupdate=NOW(),
        x.upd_log = 'activateKeyEnter.php',
        x.upd_date = NOW()
      WHERE
        x.id=:id
EOF;
      $stmt = $dbh->prepare($sql);
      foreach($rows as $row) {
        $stmt->execute(array(
          ':id' => $row['id'],
          ':diff' => $diff['diff']
        ));
      }
    }

    $sql = <<<EOF
    UPDATE userdata as u
    INNER JOIN activation as a
    ON
      a.used=u.id
    AND
      a.plan_id > 0
    AND
      a.del_flg='0'
    AND
      a.start_date <= NOW()
    AND
      NOW() < a.end_date
    SET
      u.capacity = a.capacity,
      u.session = a.session,
      u.upd_log = 'activateKeyEnter.php',
      u.upd_date = NOW()
    WHERE
      u.id=:id
EOF;
    $stmt = $dbh->prepare($sql);
    $stmt->execute(array(':id' => $userdata_result['id']));

  } else {

    $sql = <<<EOF
  UPDATE activation as x,
    (SELECT
      @x:=CAST(
        CONVERT_TZ(
          GREATEST(
            NOW(),
            u.freeend_date,
            IFNULL((SELECT MAX(end_date)
              FROM activation
              WHERE used=u.id
              AND end_date IS NOT NULL
              AND del_flg='0'
              AND exp_flg='0'),
            u.freeend_date)),
          'UTC', u.tz)
        as DATE) as st,
      u.tz,
      CONVERT_TZ(@x, u.tz, 'UTC') as cst
    FROM userdata as u
    WHERE u.id=:uid) as t
    SET 
      x.start_date=t.cst,
      x.end_date=DATE_ADD(t.cst, INTERVAL :add DAY),
      x.lastupdate=NOW(),
      x.upd_log = 'activateKeyEnter.php',
      x.upd_date = NOW()
    WHERE
      x.id=:id
EOF;
    $stmt = $dbh->prepare($sql);
    $stmt->execute(array(
      ':uid' => $userdata_result['id'],
      ':id' => $key_result['id'],
      ':add' => $add_days
    ));
  }
}

$capacityText = get_capacity_master($dbh);
$sessionText  = get_session_master($dbh);

$new_capacity = $userdata_result['capacity'];
$new_session = $userdata_result['session'];

if (intval($key_result['plan_id']) == 0) {
  // 容量拡張
  if((int)$key_result['capacity'] > 0){
  error_log('[[容量拡張]]'); 
    //現在値と比較
    if($userdata_result['capacity'] < $key_result['capacity']) {
      $new_capacity = $key_result['capacity'];
    } else {
      return [
        'result' => 'NG',              
        'keymessage' => _('既にスペースを'.$capacityText[$userdata_result['capacity']].'GB にしているため入力できません')
      ];
    }
  }
  // セッション拡張
  if((int)$key_result['session'] > 0){
  error_log('[[セッション拡張]]'); 
    //現在値と比較
    if($userdata_result['session'] < $key_result['session']) {
      $new_session = $key_result['session'];
    } else {
      return [
        'result' => 'NG',              
        'keymessage' => _('既にセッション数を'.$sessionText[$userdata_result['session']].'にしているため入力できません')
      ];
    }
  }
  // カスタムコード追加
  if(!empty($custom_codes)) {
    foreach($custom_codes as $c) {
        if (isset($user_custom[$c]) && $user_custom[$c]['del_flg'] == '0') {
            return [
                'result' => 'NG',              
                'keymessage' => _('既にカスタムコードを適用済みのため入力できません')
            ];
        }
    }
    foreach($custom_codes as $c) {
      if (!isset($user_custom[$c])) {
          $sql = "INSERT INTO user_custom (member_id,custom_code,ins_log,ins_date) VALUES (:mids,:mcode,'activatekeyEnter.php',NOW())";
          $stmt = $dbh->prepare($sql);
          $stmt->execute(array(':mids'=>$_SESSION['member_id'], ':mcode'=>$c));
      } else if ($user_custom[$c]['del_flg'] != '0') {
          $sql = "UPDATE user_custom SET del_flg='0',upd_date=NOW(),upd_log='activateKeyEnter.php' WHERE member_id=:mids and custom_code=:mcode";
          $stmt = $dbh->prepare($sql);
          $stmt->execute(array(':mids'=>$_SESSION['member_id'], ':mcode'=>$c));
      }
    }
  }
}

// アクティベーションコード使用フラグ立て
$sql = "UPDATE activation SET used=:mids, upd_log='activatekeyEnter.php', upd_date=NOW() WHERE id = :keyids";
$stmt = $dbh->prepare($sql);
$params = array(':mids' => $_SESSION['usr_id'] , ':keyids' => $key_result['id']);
$stmt->execute($params);

// ユーザーデータ更新
$sql = <<<END
UPDATE userdata
LEFT OUTER JOIN (
  SELECT 
    used,
    MIN(start_date) as st,
    MAX(end_date)   as ed
  FROM
    activation
  WHERE
    end_date IS NOT NULL
  AND
    del_flg='0'
  GROUP BY used
) as t
on
  t.used = userdata.id
SET 
  start_date=LEAST(IFNULL(start_date,t.st),t.st),
  end_date=(@x:=GREATEST(IFNULL(end_date,t.ed),t.ed)),
  expire_date=CONVERT_TZ(
		CAST(DATE_ADD(CONVERT_TZ(IFNULL(@x,freeend_date),'UTC',tz), INTERVAL :expire DAY) as DATE),
		tz, 'UTC'),
  capacity=:cap,
  session=:sess,
  upd_log='activatekeyEnter.php',
  upd_date=NOW() 
WHERE member_id=:mids
END;
$stmt = $dbh->prepare($sql);
$params = array(
  ':mids' => $_SESSION['member_id'], 
  ':cap' => $new_capacity ,
  ':sess' => $new_session, 
  ':expire' => EXPIRE_DAYS_AFTER_CONTRACT + 1);

//error_log('[[***************************]]'); 
//error_log('[['.var_export($params, true).']]'); 
//error_log('[['.var_export($sql, true).']]'); 

$stmt->execute($params);

$sql = "UPDATE ftp INNER JOIN userdata as u ON u.id=ftp.id SET ftp.user_quota_size=:cap, ftp.per_user_max=:sess, ftp.upd_log='activatekeyEnter.php', ftp.upd_date=NOW() WHERE u.member_id=:mids AND ftp.del_flg='0'";
$stmt = $dbh->prepare($sql);
$params = array(':mids' => $_SESSION['member_id'], ':cap' => $capacityText[$new_capacity] ,':sess' => $sessionText[$new_session]);
$stmt->execute($params);

	$dbh->query('COMMIT');

	return [
    'result' => 'OK',
		'keymessage' => _('アクティベーションコードを入力しました')
	];
}

wrap(function() {
	echo json_encode(check());
});