package ID3FS::DB;

use strict;
use warnings;
use DBI;
use ID3FS::File;

our $SCHEMA_VERSION=1;
my $dbfile=".id3fs";

sub new
{
    my $proto=shift;
    my $class=ref($proto) || $proto;
    my $self={};
    bless($self,$class);

    my($dir, $init, $me)=@_;
    $self->{dbpath}="$dir/$dbfile";
    $self->{me}=$me;

    my $exists=-f $self->{dbpath};

    $self->{postgres}=0;

    unless($self->{postgres})
    {
	die("$me: $self->{dbpath}: not found. use --init to create.\n") if(!$exists && !$init);
	die("$me: --init used but $self->{dbpath} exists.\n")           if($exists && $init);
    }

    my $connectstr="dbi:SQLite:dbname=$self->{dbpath}";
    my ($user, $pass)=("", "");
    if($self->{postgres})
    {
	$connectstr="dbi:Pg:dbname=id3fs";
	$user="ianb";
	$pass="foo";
    }
    $self->{dbh}=DBI->connect($connectstr, $user, $pass,
			      { AutoCommit=>1 } );
    unless(defined($self->{dbh}))
    {
	die("$me: DB Error: " . $DBI::errstr . "\n");
    }

    if($init)
    {
	$self->create();
    }
    else
    {
	$self->checkschema();
    }

    return $self;
}

sub create
{
    my($self,$name)=@_;
    my @schema=split(/\n\n/,join("", <DATA>));
    close(DATA);
    for my $cmd (@schema)
    {
	$self->{dbh}->do($cmd);
    }
    if($self->{postgres})
    {
	$self->cmd("CREATE SEQUENCE seq");
    }
    $self->cmd("INSERT INTO id3fs (schema_version) VALUES (?)", $SCHEMA_VERSION);
}

sub checkschema
{
    my $self=shift;
    my ($version)=$self->cmd_onerow("SELECT schema_version from id3fs");
    if(!defined($version) || $version != $SCHEMA_VERSION)
    {
	die("$self->{me}: id3fs database version " .
	    defined($version) ? $version : '""' .
	    "not known, current version is $SCHEMA_VERSION.\n");
    }
}

sub cmd_sth
{
    my($self, $sql, @params)=@_;
    my $sth=$self->{dbh}->prepare($sql);
    my $idx=1;
    for my $param (@params)
    {
	$param="" unless(defined($param));
	$sth->bind_param($idx++, $param);
    }
    $sth->execute();
    return $sth;
}

sub tags
{
    my($self, @constraints)=@_;
    if(!@constraints) # /
    {
	my $sql="SELECT DISTINCT name FROM tags;";
	my $tags=$self->cmd_rows($sql);
	return(map { $_->[0]; } @$tags);
    }
    my @file_ids=();
    my @tag_ids=();
    my $main_sql_start=("SELECT DISTINCT tags.name FROM files\n" .
			"INNER JOIN files_x_tags ON files.id=files_x_tags.files_id\n" .
			"INNER JOIN tags ON tags.id=files_x_tags.tags_id\n" .
			"WHERE files.id in (" .
			("\tSELECT DISTINCT files.id FROM files\n" .
			 "\tINNER JOIN files_x_tags ON files.id=files_x_tags.files_id\n" .
			 "\tINNER JOIN tags ON tags.id=files_x_tags.tags_id\n" .
			 "\tWHERE tags.id in ("));
    my $main_sql_mid=")\n) AND tags.id NOT IN (";
    my $main_sql_end=")\n";
    while(my $constraint=shift @constraints)
    {
	print "CONSTRAINT: $constraint->{name}\n";
	my $cid=$constraint->{id};
	push(@tag_ids, $cid);
    }
    my $sql = ($main_sql_start . join(", ", @tag_ids) .
	       $main_sql_mid   . join(", ", @tag_ids) .
	       $main_sql_end);
    print "SQL: $sql\n";
    my $result=$self->cmd_rows($sql);
    my @tagnames=map { $_->[0]; } @$result;
    print "SUBNAMES: ", join(', ', @tagnames), "\n";
    return(@tagnames);
}

sub tag_values
{
    my($self, $tag)=@_;
    my $sql=("SELECT DISTINCT tagvals.name FROM tags\n" .
	     "INNER JOIN tags_x_tagvals ON tags.id=tags_x_tagvals.tags_id\n" .
	     "INNER JOIN tagvals ON tagvals.id=tags_x_tagvals.tagvals_id\n" .
	     "WHERE tags.name=?");
    my $tags=$self->cmd_rows($sql, $tag);
    return(map { $_->[0]; } @$tags);
}

sub tag_id
{
    my($self, $tag)=@_;
    my $sql='SELECT id FROM tags WHERE name=?';
    my ($id)=$self->cmd_onerow($sql, $tag);
    return($id);
}

sub add
{
    my($self,$path)=@_;
    my $file=ID3FS::File->new($path);
    return unless(defined($file));
    my $artist=$file->artist();
    my $album=$file->album();
    my $v1genre=$file->v1genre();
    my $year=$file->year();
    my $audiotype=$file->album();
    my $tags=$file->tags();
    my $haspic=$file->haspic();

    my $file_id=$self->add_to_table("files", $path);
    my $artists_id=$self->add_to_table("artists",  $artist);
    my $albums_id=$self->add_to_table("albums",  $album);
    for my $tag (keys %$tags)
    {
	$self->add_tag($file_id, $tag, $tags->{$tag});
    }

    if($self->ok($year))
    {
	$self->add_tag($file_id, "year", $year);
	if($year=~/^(\d\d\d)\d$/)
	{
	    $self->add_tag($file_id, "decade", "${1}0s");
	}
    }
    if($self->ok($v1genre))
    {
	$self->add_tag($file_id, "v1genre", $v1genre);
    }

    if($haspic)
    {
	$self->add_tag($file_id, "haspic", undef);
    }

    $self->add_relation("files_x_artists",
			{ "files_id" => $file_id,
			  "artists_id" => $artists_id });

    $self->add_relation("artists_x_albums",
		      { "artists_id" => $artists_id,
			"albums_id" => $albums_id});
}

sub add_tag
{
    my($self, $file_id, $tag, $val)=@_;
    my $tag_id=$self->add_to_table("tags",  $tag);
    $self->add_relation("files_x_tags",
			{ "files_id" => $file_id,
			  "tags_id"  => $tag_id });
    if(defined($val))
    {
	my $val_id=$self->add_to_table("tagvals", $val);
	$self->add_relation("tags_x_tagvals",
			    { "tags_id"     => $tag_id,
			      "tagvals_id"  => $val_id });
    }
}

sub add_to_table
{
    my($self, $table, $name, $extradata)=@_;
    my $id=$self->lookup_id($table, $name);
    unless(defined($id))
    {
	my $sql="INSERT INTO $table (";
	$sql .= "id, " if($self->{postgres});
	my @fields=qw(name);
	if(defined($extradata))
	{
	    push(@fields, sort keys(%$extradata));
	}
	$sql .= join(", ", @fields);
	$sql .=") VALUES (";
	$sql .=") nextval('seq'), " if($self->{postgres});
	$sql .= join(", ", map { "?"; } @fields);
	$sql .= ");";
	$id=$self->cmd_id($sql, $name, map { $extradata->{$_} || ""; } sort keys %$extradata);
    }
    return $id;
}

sub add_relation
{
    my ($self, $relname, $fields)=@_;
    return if($self->relation_exists($relname, $fields));
    my $sql="INSERT INTO $relname (";
    $sql .= join(", ", sort keys(%$fields));
    $sql .= ") VALUES (";
    $sql .= join(", ", map { "?"; } sort keys(%$fields));
    $sql .= ");";
    $self->cmd($sql, map { $fields->{$_}; } sort keys(%$fields));
}

sub lookup_id
{
    my($self, $table, $name)=@_;
    my($id)=$self->cmd_onerow("SELECT id FROM $table where name=?", $name);
    return $id;
}

sub relation_exists
{
    my ($self, $relname, $fields)=@_;
    my $sql="SELECT count(1) FROM $relname WHERE ";
    my @exprs=();
    my @vals=();
    for my $field (keys %$fields)
    {
	push(@exprs,$field);
	push(@vals,$fields->{$field});
    }
    $sql .= join(' AND ', map { "$_=?"; } @exprs);
    my ($ret)=$self->cmd_onerow($sql, @vals);
    return $ret;
}

sub ok
{
    my($self, $thing)=@_;
    return(defined($thing) && length($thing));
}

sub cmd
{
    my ($self, @args)=@_;
    # don't care about retcode
    $self->cmd_sth(@args);
}

sub cmd_onerow
{
    my ($self, @args)=@_;
    my $sth=$self->cmd_sth(@args);
    return($sth->fetchrow_array());
}

sub cmd_rows
{
    my ($self, @args)=@_;
    my $sth=$self->cmd_sth(@args);
    return $sth->fetchall_arrayref();
}

sub cmd_id
{
    my ($self, @args)=@_;
    $self->cmd_sth(@args);
    return($self->last_insert_id());
}

sub last_insert_id
{
    my $self=shift;
    if($self->{postgres})
    {
	return $self->{dbh}->last_insert_id(undef, undef, undef, undef,
					    { sequence => "seq" });
    }
    else
    {
	return $self->{dbh}->last_insert_id("","","","");
    }
}

__DATA__

CREATE TABLE id3fs (
    schema_version INTEGER
);

CREATE TABLE files (
    id INTEGER PRIMARY KEY,
    name text
);

CREATE TABLE artists (
    id INTEGER PRIMARY KEY,
    name text
);

CREATE TABLE albums (
    id INTEGER PRIMARY KEY,
    name text
);

CREATE TABLE tags (
    id INTEGER PRIMARY KEY,
    name text
);

CREATE TABLE tagvals (
    id INTEGER PRIMARY KEY,
    name text
);

CREATE TABLE files_x_tags (
    files_id INTEGER,
    tags_id INTEGER
);

CREATE TABLE tags_x_tagvals (
    tags_id INTEGER,
    tagvals_id INTEGER
);

CREATE TABLE files_x_artists (
    files_id INTEGER,
    artists_id INTEGER
);

CREATE TABLE artists_x_albums (
    artists_id INTEGER,
    albums_id INTEGER
);
