<?php
require_once('config.php');
$db = mysqli_init();
$db->real_connect(DB_HOST, DB_USERNAME, DB_PASS);
$db->set_charset('utf8mb4');

// check for existence of db
$db->real_query('create database if not exists '.DB_NAME.';');
// select db
$db->real_query('use '.DB_NAME.';');
// create tables if they don't exist
$db->real_query('create table if not exists user_cache (did varchar(64) not null unique primary key, handle varchar(255) not null unique, display_name text, description text, avatar text, banner text, pds text not null, pinned_post text, expires int not null);');
$db->real_query('create table if not exists minidoc_cache (did varchar(64) not null primary key, handle varchar(255) not null unique, pds text not null, expires int not null);');
$db->real_query('create table if not exists post_cache (rkey varchar(64) not null primary key, did varchar(64) not null, text text, embed_type varchar(32), embed_data text, created_at datetime, expires int not null);');
$db->real_query('create table if not exists plc_cache (did varchar(64) not null primary key, plcdoc text, expires int not null);');
$db->real_query('create table if not exists feed_cache (at_uri varchar(255) not null primary key, title varchar(255) not null, description text, avatar text, creator_did varchar(64) not null, expires int not null);');
$db->real_query('create table if not exists pds_bans (domain varchar(255) not null primary key, reason text);');
$db->real_query('create table if not exists client_suspensions (id int not null auto_increment primary key, did varchar(64) not null, reason text, expires int not null);');
$db->real_query('create table if not exists bans (did varchar(64) not null primary key, reason text);');
// ok! we're good let's go
$db->close();

function newConn() {
  $db = mysqli_init();
  $db->real_connect(DB_HOST, DB_USERNAME, DB_PASS);
  $db->real_query('use '.DB_NAME.';');
  $db->set_charset('utf8mb4');
  return $db;
}

function deleteExpired(string $table): void {
  $db = newConn();
  $current_time = strtotime('now');
  $db->real_query('delete from '.$table.' where expires < '.$current_time.';');
  $db->close();
}

function requestUserCache(string $value, string $field = 'handle'): ?object {
  $db = newConn();
  deleteExpired('user_cache');
  $result = $db->query('select * from user_cache where '.$field.'="'.$db->real_escape_string($value).'";');
  if ($result->num_rows === 0) return null;
  $obj = $result->fetch_object();
  $obj->displayName = $obj->display_name;
  $obj->pinnedPost = $obj->pinned_post;
  unset($obj->display_name);
  unset($obj->pinned_post);
  return $obj;
  $db->close();
}

function requestMinidocCache(string $value, string $field = 'handle'): ?object {
  $db = newConn();
  deleteExpired('minidoc_cache');
  $result = $db->query('select * from minidoc_cache where '.$field.'="'.$db->real_escape_string($value).'";');
  if ($result->num_rows === 0) return null;
  return $result->fetch_object();
  $db->close();
}

function requestPostCache(string $rkey): ?object {
  $db = newConn();
  deleteExpired('post_cache');
  $result = $db->query('select * from post_cache where rkey="'.$db->real_escape_string($rkey).'";');
  if ($result->num_rows === 0) return null;
  $obj = $result->fetch_object();
  $obj->createdAt = $obj->created_at;
  $obj->embedType = $obj->embed_type;
  $obj->embeds = $obj->embed_data;
  unset($obj->created_at);
  unset($obj->embed_type);
  unset($obj->embed_data);
  return $obj;
  $db->close();
}

function requestPlcCache(string $did): ?object {
  $db = newConn();
  deleteExpired('plc_cache');
  $result = $db->query('select * from plc_cache where did="'.$db->real_escape_string($did).'";');
  if ($result->num_rows === 0) return null;
  return $result->fetch_object();
  $db->close();
}

function requestFeedCache(string $at_uri): ?object {
  $db = newConn();
  deleteExpired('feed_cache');
  $result = $db->query('select * from feed_cache where at_uri="'.$db->real_escape_string($at_uri).'";');
  if ($result->num_rows === 0) return null;
  $obj = $result->fetch_object();
  $obj->atUri = $obj->at_uri;
  $obj->creatorDid = $obj->creator_did;
  unset($obj->at_uri);
  unset($obj->creator_did);
  return $obj;
  $db->close();
}

function updateUserCache(string $handle, string $did, ?string $display_name, string $pds, ?string $avatar, ?string $banner, string $description, ?string $pinned): void {
  $db = newConn();
  $expires = strtotime('now') + 60*60;
  if (!empty(requestUserCache($did, 'did'))) {
    $db->query('update user_cache set handle="'.$db->real_escape_string($handle).'", display_name="'.$db->real_escape_string($display_name).'", description="'.$db->real_escape_string($description).'", avatar="'.$db->real_escape_string($avatar).'", banner="'.$db->real_escape_string($banner).'", pds="'.$db->real_escape_string($pds).'", pinned_post="'.$db->real_escape_string($pinned).'", expires='.$expires.' where did="'.$db->real_escape_string($did).'";');
    return;
  }
  $db->query('insert into user_cache (did, handle, display_name, description, avatar, banner, pds, pinned_post,  expires) values("'.$db->real_escape_string($did).'", "'.$db->real_escape_string($handle).'", "'.$db->real_escape_string($display_name).'", "'.$db->real_escape_string($description).'", "'.($avatar ? $db->real_escape_string($avatar) : 'NULL').'", "'.($banner ? $db->real_escape_string($banner) : 'NULL').'", "'.$db->real_escape_string($pds).'", "'.($pinned ? $db->real_escape_string($pinned) : 'NULL').'", '.$expires.');');
  $db->close();
}

function updateMinidocCache(string $handle, string $did, string $pds): void {
  $db = newConn();
  $expires = strtotime('now') + 60*60*48;
  if (!empty(requestMinidocCache($did, 'did'))) {
    $db->query('update minidoc_cache set handle="'.$db->real_escape_string($handle).'", pds="'.$db->real_escape_string($pds).'", expires='.$expires.' where did="'.$db->real_escape_string($did).'";');
    return;
  }
  $db->query('insert into minidoc_cache (handle, did, pds, expires) values("'.$db->real_escape_string($handle).'", "'.$db->real_escape_string($did).'", "'.$db->real_escape_string($pds).'", '.$expires.');');
  $db->close();
}

function updatePlcCache(string $did, string $plcdoc): void {
  $db = newConn();
  $expires = strtotime('now') + 60*60*48;
  if (!empty(requestMinidocCache($did))) {
    $db->query('update plc_cache set plcdoc="'.$db->real_escape_string($plcdoc).'", expires='.$expires.' where did="'.$db->real_escape_string($did).'";');
    return;
  }
  $db->query('insert into plc_cache (did, plcdoc, expires) values("'.$db->real_escape_string($did).'", "'.$db->real_escape_string($plcdoc).'", '.$expires.');');
  $db->close();
}

function updateFeedCache(string $atUri, string $title, ?string $description, ?string $avatar, string $creator_did): void {
  $db = newConn();
  $expires = strtotime('now') + 60*60*4;
  if (!empty(requestFeedCache($atUri))) {
    $db->query('update feed_cache set title="'.$db->real_escape_string($title).'", description="'.$db->real_escape_string($description).'", avatar="'.$db->real_escape_string($avatar).'", creator_did="'.$db->real_escape_string($creator_did).'", expires='.$expires.' where at_uri="'.$db->real_escape_string($atUri).'";');
    return;
  }
  $db->query('insert into feed_cache (at_uri, title, description, avatar, creator_did, expires) values("'.$db->real_escape_string($atUri).'", "'.$db->real_escape_string($title).'", "'.$db->real_escape_string($description).'", "'.$db->real_escape_string($avatar).'", "'.$db->real_escape_string($creator_did).'", '.$expires.');');
  $db->close();
}

function updatePostCache(string $rkey, string $did, string $text, string $embedType, string $embedData, string $createdAt): void {
  $db = newConn();
  $dbdate = date('Y-m-d h:i:s', strtotime($createdAt));
  $expires = strtotime('now') + 60*60*48;
  if (!empty(requestPostCache($rkey))) {
    $db->query('update post_cache set did="'.$db->real_escape_string($did).'", text="'.$db->real_escape_string($text).'", embed_type="'.$db->real_escape_string($embedType).'", embed_data="'.$db->real_escape_string($embedData).'", created_at="'.$db->real_escape_string($dbdate).'", expires='.$expires.' where rkey="'.$db->real_escape_string($rkey).'";');
    return;
  }
  $db->query('insert into post_cache (rkey, did, text, embed_type, embed_data, created_at, expires) values("'.$db->real_escape_string($rkey).'", "'.$db->real_escape_string($did).'", "'.$db->real_escape_string($text).'", "'.$db->real_escape_string($embedType).'", "'.$db->real_escape_string($embedData).'", "'.$db->real_escape_string($dbdate).'", '.$expires.');');
  $db->close();
}
?>