#!/bin/bash

mkdir tmp

# TODO: work on the full-text part in PostgreSQL
# a trigger will be required to make it work.

cat << EOF > tmp/archive-schema.sql
CREATE DATABASE "archive";

\c "archive"
CREATE EXTENSION IF NOT EXISTS btree_gist;

DROP TABLE IF EXISTS thread;
CREATE TABLE IF NOT EXISTS thread (
    id SERIAL PRIMARY KEY,
    content TEXT,
    label VARCHAR(300),
    forum VARCHAR(300)
);

-- CREATE INDEX idx_thread_content ON thread USING gist(content);
CREATE INDEX idx_thread_label ON thread(label);
CREATE UNIQUE INDEX idx_thread_uniq_label_forum ON thread(label,forum);

EOF

LD_LIBRARY_PATH="" /share/Public/builds/prefix/bin/psql -U postgres -d postgres < tmp/archive-schema.sql



cat << 'EOF' > tmp/import.xq

let $conn-string:="jdbc:postgresql://localhost:5432/archive?user=postgres&amp;password=postgres"
let $pgconn := sql:connect($conn-string)
let $dbs:=fn:filter(db:list(), function($x){ matches($x,"linuxquestions-shard-") })
for $db in fn:reverse(fn:sort($dbs))
for $x in db:open($db)
let $label:=$x/fn:base-uri()
let $content:=$x//*[matches(@id,"post_message_")]/text()
let $params := <sql:parameters>
                 <sql:parameter type='string'>{ $label   }</sql:parameter>
                 <sql:parameter type='string'>{ $content }</sql:parameter>
                 <sql:parameter type='string'>{ $db }</sql:parameter>
               </sql:parameters>
let $prep:=sql:prepare($pgconn, "INSERT INTO thread(label,content,forum) VALUES(?,?,?)")
return 
try {
sql:execute-prepared($prep,$params)
} catch * {
'Error [' || $err:code || ']: ' || $err:description || '--' || $params
}

EOF

/share/Public/builds/basex/bin/basexclient -U admin -P admin tmp/import.xq