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};
    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);

    $self->{dbh}=DBI->connect("dbi:SQLite:dbname=$self->{dbpath}","","",
			      { 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);
    }
    $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, $path)=@_;
    my $sql="SELECT DISTINCT name FROM tags;";
    my $tags=$self->cmd_rows($sql);
    return(map { $_->[0]; } @$tags);
}

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 (";
	my @fields=qw(name);
	if(defined($extradata))
	{
	    push(@fields, sort keys(%$extradata));
	}
	$sql .= join(", ", @fields);
	$sql .=") VALUES (";
	$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;
    return $self->{dbh}->last_insert_id("","","","");
}

__DATA__

CREATE TABLE id3fs (
    schema_version
);

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

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

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

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

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

CREATE TABLE files_x_tags (
    files_id,
    tags_id
);

CREATE TABLE tags_x_tagvals (
    tags_id,
    tagvals_id
);

CREATE TABLE files_x_artists (
    files_id,
    artists_id
);

CREATE TABLE artists_x_albums (
    artists_id,
    albums_id
);
