<?php

$method = 'POST';
require_once __DIR__.'/common.inc.php';

$table = 'xoonips_item_basic';
if (!MyDumpTool::tableExists($table)) {
    exit('xoonips module not found'.PHP_EOL);
}

$tree = getPublicIndex();
$items = getPublicItems();
checkNimgCenterBaseItem();
checkBadIndexItemLink();
checkBadItemTitle();
checkNimgCenterItem($items);

// check nimgcenter item
function checkNimgCenterItem($items)
{
    global $xoopsDB;
    $prefix = $xoopsDB->prefix();
    foreach ($items as $item) {
        if ('xnpnimgcenter' !== $item['item_type_name']) {
            continue;
        }
        appendNimgcenterInfo($item);
        $itemId = $item['item_id'];
        $indexIds = getPublicIndexIdsByItemId($itemId);
        $baseItemId = $item['baseitem_id'];
        $baseIndexIds = getPublicIndexIdsByItemId($baseItemId);
        if (empty($baseIndexIds)) {
            // private item found.
            foreach ($indexIds as $indexId) {
                $sql = <<< SQL
INSERT INTO `${prefix}_xoonips_index_item_link` VALUES (NULL, $indexId, $baseItemId, 2)
SQL;
                var_dump($sql);
                if (!($res = $xoopsDB->queryF($sql))) {
                    var_dump($xoopsDB);
                    exit();
                }
            }
        }
    }
}

// check bad item title
function checkBadItemTitle()
{
    global $xoopsDB;
    $prefix = $xoopsDB->prefix();
    $sql = <<< SQL
SELECT `it`.*
  FROM `${prefix}_xoonips_item_title` AS `it`
  LEFT JOIN `${prefix}_xoonips_item_basic` AS `ib` ON `it`.`item_id`=`ib`.`item_id`
  WHERE `ib`.`item_id` IS NULL
SQL;
    if (!($res = $xoopsDB->query($sql))) {
        var_dump($xoopsDB);
        exit();
    }
    $itemIds = [];
    while ($row = $xoopsDB->fetchArray($res)) {
        $itemIds[] = $row['item_id'];
    }
    $xoopsDB->freeRecordSet($res);
    $itemIds = array_unique($itemIds);
    foreach ($itemIds as $itemId) {
        $sql = <<< SQL
DELETE FROM `${prefix}_xoonips_item_title` WHERE `item_id`=$itemId
SQL;
        var_dump($sql);
        if (!($res = $xoopsDB->queryF($sql))) {
            var_dump($xoopsDB);
            exit();
        }
    }
    var_dump($itemIds);
}

// check bad index item link
function checkBadIndexItemLink()
{
    global $xoopsDB;
    $prefix = $xoopsDB->prefix();
    $sql = <<< SQL
SELECT `iil`.*
  FROM `${prefix}_xoonips_index_item_link` AS `iil`
  LEFT JOIN `${prefix}_xoonips_item_basic` AS `ib` ON `iil`.`item_id`=`ib`.`item_id`
  WHERE `ib`.`item_id` IS NULL
SQL;
    if (!($res = $xoopsDB->query($sql))) {
        var_dump($xoopsDB);
        exit();
    }
    $itemIds = [];
    while ($row = $xoopsDB->fetchArray($res)) {
        $itemIds[] = $row['item_id'];
    }
    $xoopsDB->freeRecordSet($res);
    $itemIds = array_unique($itemIds);
    foreach ($itemIds as $itemId) {
        $sql = <<< SQL
DELETE FROM `${prefix}_xoonips_index_item_link` WHERE `item_id`=$itemId
SQL;
        var_dump($sql);
        if (!($res = $xoopsDB->queryF($sql))) {
            var_dump($xoopsDB);
            exit();
        }
    }
    var_dump($itemIds);
}

// check baseitem of nimgcenter item
function checkNimgCenterBaseItem()
{
    global $xoopsDB;
    $prefix = $xoopsDB->prefix();
    $sql = <<< SQL
SELECT `id`.*
  FROM `${prefix}_xnpnimgcenter_item_detail` AS `id`
  INNER JOIN `${prefix}_xoonips_index_item_link` AS `iil` ON `id`.`nimgcenter_id`=`iil`.`item_id`
  INNER JOIN `${prefix}_xoonips_index` AS `idx` ON `iil`.`index_id`=`idx`.`index_id`
  LEFT JOIN `${prefix}_xoonips_item_basic` AS `ib` ON `id`.`baseitem_id`=`ib`.`item_id`
  WHERE `ib`.`item_id` IS NULL
     AND `iil`.`certify_state`=2
     AND `idx`.`open_level`=1
  GROUP BY `id`.`nimgcenter_id`
  ORDER BY `id`.`nimgcenter_id` ASC
SQL;
    if (!($res = $xoopsDB->query($sql))) {
        var_dump($xoopsDB);
        exit();
    }
    while ($detail = $xoopsDB->fetchArray($res)) {
        echo 'baseitem not found: nimgcenter_item_id:'.$detail['nimgcenter_id'].', baseitem_id:'.$detail['baseitem_id'].PHP_EOL;
        withdrawItem($detail['nimgcenter_id']);
    }
    $xoopsDB->freeRecordSet($res);
}

// withdraw item
function withdrawItem($item_id)
{
    $index_item_link_handler = &xoonips_getormhandler('xoonips', 'index_item_link');
    $criteria = new CriteriaCompo(new Criteria('item_id', $item_id));
    $criteria->add(new Criteria('certify_state', 2));
    $index_item_link_handler->deleteAll($criteria);

    $item_show_handler = &xoonips_getormhandler('xoonips', 'item_show');
    $item_show_handler->deleteAll(new Criteria('item_id', $item_id));

    $item_status_handler = &xoonips_getormhandler('xoonips', 'item_status');
    $item_status_handler->updateItemStatus($item_id);

    return true;
}

// append nimgcenter info
function appendNimgcenterInfo(&$row)
{
    global $xoopsDB;
    $prefix = $xoopsDB->prefix();
    $coord_type_names = [
        0 => 'Talairach',
        1 => 'NMI',
        2 => 'Unclear',
    ];
    $item_id = $row['item_id'];
    $sql = <<< SQL
SELECT `id`.*,
      `ity`.`display_name` AS `base_type_display_name`
  FROM `${prefix}_xnpnimgcenter_item_detail` AS `id`
  INNER JOIN `${prefix}_xoonips_item_type` AS `ity` ON `id`.`base_type_name`=`ity`.`name`
  WHERE `id`.`nimgcenter_id`=$item_id
SQL;
    if (!($res = $xoopsDB->query($sql))) {
        var_dump($xoopsDB);
        exit();
    }
    while ($detail = $xoopsDB->fetchArray($res)) {
        MyDumpTool::decode($detail);
        MyDumpTool::convertToInt($detail, ['baseitem_id', 'coord_type']);
        $detail['coord_type_name'] = $coord_type_names[$detail['coord_type']];
        MyDumpTool::dropColumn($detail, ['nimgcenter_id']);
        $row += $detail;
    }
    $xoopsDB->freeRecordSet($res);
    $sql = <<< SQL
SELECT *
  FROM `${prefix}_xnpnimgcenter_talairach_list`
  WHERE `nimgcenter_id`=$item_id
SQL;
    if (!($res = $xoopsDB->query($sql))) {
        var_dump($xoopsDB);
        exit();
    }
    $row['coordinate'] = [];
    while ($t = $xoopsDB->fetchArray($res)) {
        MyDumpTool::dropColumn($t, ['nimgcenter_id']);
        MyDumpTool::convertToInt($t, ['x', 'y', 'z']);
        $row['coordinate'][] = $t;
    }
    $xoopsDB->freeRecordSet($res);
}

// get public indexes
function getPublicIndex()
{
    global $xoopsDB;
    $prefix = $xoopsDB->prefix();
    $sql = <<< SQL
SELECT 
    `idx`.*,
    `it`.`title`
  FROM `${prefix}_xoonips_index` AS `idx`
  INNER JOIN `${prefix}_xoonips_item_title` AS `it` ON `idx`.`index_id`=`it`.`item_id`
  WHERE `it`.`title_id`=0
     AND `idx`.`open_level`=1
SQL;

    if (!($res = $xoopsDB->query($sql))) {
        var_dump($xoopsDB);
        exit();
    }
    $tree = [];
    while ($row = $xoopsDB->fetchArray($res)) {
        MyDumpTool::decode($row);
        MyDumpTool::convertToInt($row, ['index_id', 'parent_index_id', 'uid', 'gid', 'open_level', 'sort_number']);
        $tree[$row['index_id']] = $row;
    }
    $xoopsDB->freeRecordSet($res);

    return $tree;
}

// get public indexes by item id
function getPublicIndexIdsByItemId($item_id)
{
    global $xoopsDB;
    $prefix = $xoopsDB->prefix();
    $sql = <<< SQL
SELECT
    `iil`.`index_id`
  FROM `${prefix}_xoonips_index_item_link` AS `iil`
  INNER JOIN `${prefix}_xoonips_index` AS `idx` ON `iil`.`index_id`=`idx`.`index_id`
  WHERE `iil`.`item_id`=$item_id
     AND `idx`.`open_level`=1
  ORDER BY `iil`.`index_id`
SQL;
    if (!($res = $xoopsDB->query($sql))) {
        var_dump($xoopsDB);
        exit();
    }
    $indexes = [];
    while ($row = $xoopsDB->fetchArray($res)) {
        MyDumpTool::convertToInt($row, ['index_id']);
        $indexes[] = $row['index_id'];
    }
    $xoopsDB->freeRecordSet($res);

    return $indexes;
}

// get public items
function getPublicItems()
{
    global $xoopsDB;
    $prefix = $xoopsDB->prefix();
    $sql = <<< SQL
SELECT 
    `ib`.*,
    GROUP_CONCAT(DISTINCT `it`.`title` ORDER BY `it`.`title_id` ASC SEPARATOR '\n') AS `title`,
    `ity`.`display_name` AS `item_type_display_name`, `ity`.`name` AS `item_type_name`,
    `u`.`uname`, `u`.`name`, `u`.`email`
  FROM `${prefix}_xoonips_item_basic` AS `ib`
  INNER JOIN `${prefix}_xoonips_item_title` AS `it` ON `ib`.`item_id`=`it`.`item_id`
  INNER JOIN `${prefix}_xoonips_item_type` AS `ity` ON `ib`.`item_type_id`=`ity`.`item_type_id`
  INNER JOIN `${prefix}_xoonips_index_item_link` AS `iil` ON `ib`.`item_id`=`iil`.`item_id`
  INNER JOIN `${prefix}_xoonips_index` AS `idx` ON `iil`.`index_id`=`idx`.`index_id`
  LEFT JOIN `${prefix}_users` AS `u` ON `ib`.`uid`=`u`.`uid`
  WHERE `ity`.`display_name` != 'Index'
     AND `iil`.`certify_state`=2
     AND `idx`.`open_level`=1
  GROUP BY `ib`.`item_id`
  ORDER BY `ib`.`item_id` ASC
SQL;

    if (!($res = $xoopsDB->query($sql))) {
        var_dump($xoopsDB);
        exit();
    }
    $items = [];
    $itemIds = [];
    $baseItemIds = [];
    while ($row = $xoopsDB->fetchArray($res)) {
        MyDumpTool::decode($row);
        MyDumpTool::convertToInt($row, ['item_id', 'item_type_id', 'uid', 'last_update_date', 'creation_date', 'publication_year', 'publication_month', 'publication_mday']);
        $row['index'] = getPublicIndexIdsByItemId($row['item_id']);
        if (empty($row['index'])) {
            continue;
        }
        $items[$row['item_id']] = $row;
    }
    $xoopsDB->freeRecordSet($res);

    return $items;
}