#!/usr/bin/perl -w
use strict;
use DBI;
use Time::Local qw/timegm/;

my $dbpath = $ENV{"HOME"} . "/.config";

die ("no .config in HOME?") if (!-d $dbpath);

$dbpath .= '/slucto';

if (!-d $dbpath) {
	mkdir($dbpath) || die "cannot create slucto dir in .config";
}

my $out = $dbpath . '/slucto.db';

if (-e $out) {
	my $reply = "N";
	do {
		print "'$out' already exists, overwrite? [y/N] ";
		$reply = uc(<STDIN>);
		chomp($reply);
	} while ($reply ne "Y" && $reply ne "N" && $reply ne "");
	exit 1 if ($reply ne "Y");
	unlink $out;
}

my $dbh = DBI->connect("dbi:SQLite:dbname=$out","","", {AutoCommit => 0}) ||
	die "connect to db error: " . DBI::errstr;

$dbh->do("PRAGMA foreign_keys = ON") || die "cannot set foreign keys";

$dbh->do("CREATE TABLE receiver(ic INTEGER PRIMARY KEY, " .
	"name STRING NOT NULL, street STRING NOT NULL, " .
	"city STRING NOT NULL, " .
	"zip STRING NOT NULL)");

$dbh->do("CREATE INDEX receiver_name ON receiver(name ASC)");

$dbh->do("CREATE TABLE invoice(id INTEGER PRIMARY KEY, " .
	"receiver INTEGER NOT NULL, " .
	"issuance INTEGER NOT NULL, due INTEGER NOT NULL, " .
	"FOREIGN KEY(receiver) REFERENCES receiver(ic))");

$dbh->do("CREATE TABLE item(id INTEGER PRIMARY KEY, " .
	"name STRING NOT NULL UNIQUE, price REAL)");

$dbh->do("CREATE INDEX item_name ON item(name ASC)");

$dbh->do("CREATE TABLE invoice_item(id_invoice INTEGER NOT NULL, " .
		"id_item INTEGER NOT NULL, count INTEGER NOT NULL, " .
		"price REAL NOT NULL, note STRING, " .
		"UNIQUE(id_invoice, id_item, note), " .
		"FOREIGN KEY(id_invoice) REFERENCES invoice(id), " .
		"FOREIGN KEY(id_item) REFERENCES item(id))");

$dbh->do("CREATE VIEW invoice_view AS " .
		"SELECT invoice.id, " .
		"strftime('%d. %m. %Y', invoice.issuance, 'unixepoch', 'localtime') AS issuance, " .
		"strftime('%d. %m. %Y', invoice.due, 'unixepoch', 'localtime') AS due, " .
		"receiver.* FROM invoice " .
		"LEFT JOIN receiver ON invoice.receiver = receiver.ic");
$dbh->do("CREATE VIEW invoice_item_view AS " .
		"SELECT invoice.id, " .
		"strftime('%d. %m. %Y', invoice.issuance, 'unixepoch', 'localtime') AS issuance, " .
		"strftime('%d. %m. %Y', invoice.due, 'unixepoch', 'localtime') AS due, " .
		"receiver.ic, receiver.name, item.name, " .
		"invoice_item.count, invoice_item.price, " .
		"invoice_item.count * invoice_item.price AS total " .
		"FROM invoice " .
		"LEFT JOIN receiver ON invoice.receiver = receiver.ic " .
		"LEFT JOIN invoice_item ON invoice.id = invoice_item.id_invoice " .
		"LEFT JOIN item ON invoice_item.id_item = item.id");

my $data = $dbh->prepare("INSERT INTO receiver(name, street, city, zip, ic) " .
		"VALUES (?, ?, ?, ?, ?)");
$data->execute("PC-DIR Real, s.r.o.", "Mlýnská 425/70", "Brno", "602 00",
	25533525);
$data->finish;

$data = $dbh->prepare("INSERT INTO invoice(id, receiver, issuance, " .
	"due) VALUES (?, ?, ?, ?)");
my $t = timegm(0, 0, 0, 14, 4 - 1, 2017);
$data->execute(201703001, 25533525, $t, $t + 3600*24*14);
$data->finish;

$data = $dbh->prepare("INSERT INTO item(name, price) VALUES (?, ?)");
$data->execute("Školení pro 2 osoby/den", 3200);
$data->execute("Školení pro 3 osoby/den", 3600);
$data->execute("Školení pro 4 a více osob/den", 4200);
$data->finish;

$data = $dbh->prepare("INSERT INTO invoice_item(id_invoice, id_item, " .
	"count, note, price) SELECT ?, id, ?, ?, price FROM item " .
	"WHERE id = ?");
$data->execute(201703001, 3, 'v termínu 8-10. 3. 2017', 3);
$data->finish;

$dbh->commit;

$dbh->disconnect;

1;
