use strict;
use warnings;

#
# todo:
#   several types of fields
#       - primary  ( included in main record )
#       - typed    ( records can easily be filtered for a typefield==value )
#       - freeform
#
#   option to convert several freeform fields of a group of records to a typed field.
#
#
#
use CGI::Pretty qw(:standard :cgi-lib);
use DBI;

my $dsn = "DBI:SQLite:dbname=db.dat";
my $dbh = DBI->connect($dsn, "", "") or die "error connecting to db: $!\n";
$dbh->{RaiseError}= 1;

my %pages= (
    main=> { handler=>\&mainpage, description=>"main" },
    list=> { handler=>\&listpage, description=>"list" },
    detail=> { handler=>\&detailpage, description=>"detail" },
);
my %actions= (
    create=> { handler=>\&create_tables, description=>"create tables" },
    drop=> { handler=>\&drop_tables, description=>"drop tables" },
    add_device=> { handler=>\&adddevice, description=>"add device" },
);
if (param('page') && exists $pages{param('page')}) {
    $pages{param('page')}{handler}->();
}
elsif (param('action') && exists $actions{param('action')}) {
    $actions{param('action')}{handler}->();
    print redirect(referer());
}
else {
    printf("action=%s  page=%s\n", param('action')||'-', param('page')||'-');
    mainpage();
}

# todo: close db connection

##############################################
sub paramdump {
    my %params= Vars;
    return map { sprintf("%s=>%s", $_, $params{$_}) } keys %params;
}
sub listpage {
    print header, start_html('wince file info repository');

    my $tabid= getid('record', 'device');
    my %propnames= getproperties($tabid);
    print table(
        Tr([
            map { my $recid= $_; td( map { getpropvalue($tabid, $propnames{$_}, $recid) } keys %propnames ) } (0..getmaxdevid())
        ])
    );



}
sub mainpage {
print header,
    start_html('wince file info repository'),
    li( [ map { a({-href=>"index.pl?page=$_"}, $pages{$_}{description}) }sort keys %pages ] ),
    start_form(-method=>'GET'),
    table({-border=>1 }, 
        Tr([
            td( [ map { submit(action=>$_) } sort keys %actions] ),
            td('add device', textfield(device=>'')),
            td({-colspan=>2}, 'device',
                Select('device', 
                    option(
                        [qw(wallaby himalaya magician typhoon tornado)]
                    ),
                )
            ),
            td({-colspan=>2}, 'rom',
                Select('romversion', 
                    option(
                        [qw(1.2.3 4.5.6 6.7.8)]
                    ),
                )
            ),

        ])
    ),
    paramdump(),
    end_form,
    end_html;
}
sub getmaxdevid {
    my $sth= $dbh->prepare("SELECT max(id) FROM device");
    $sth->execute;
    my $max= $sth->fetchrow_array;
    $sth->finish;

    return $max;
}
sub getproperties {
    my ($tabid)= @_;
    my $sth= $dbh->prepare("SELECT id, name FROM propertyname WHERE table_id=?");
    $sth->execute($tabid);

    my %props;
    while (my @row= $sth->fetchrow_array) {
        $props{$row[1]}= $row[0];
    }

    return %props;
}
sub getpropvalue {
    my ($tabid, $propid, $recid)= @_;
    # todo: create link from 'device' to 'device_property'
    my $sth= $dbh->prepare("SELECT value FROM device_property WHERE device_id=? AND property_id=?");
    $sth->execute($recid, $propid);
    my $val= $sth->fetchrow_array;
    $sth->finish;
    return $val;
}
sub getid {
    my ($table, $name)= @_;
    my $sth= $dbh->prepare("SELECT id FROM $table WHERE name=?");
    $sth->execute($name);
    my $id= $sth->fetchrow_array;
    $sth->finish;

    if (!defined $id) {
        my $ins= $dbh->prepare("INSERT INTO $table (name) VALUES (?)");
        $ins->execute($name);
        $id=$dbh->last_insert_id( "", "filedb", $table, "id" );
    }

    return $id;
}
sub getpropid {
    my ($table, $field)= @_;
    my $tabid= getid('record', $table);

    my $sth= $dbh->prepare("SELECT id FROM propertyname WHERE table_id=? AND name=?");
    $sth->execute($tabid, $field);
    my $id= $sth->fetchrow_array;
    $sth->finish;

    if (!defined $id) {
        my $ins= $dbh->prepare("INSERT INTO propertyname (table_id, name) VALUES (?,?)");
        $ins->execute($tabid, $field);
        $id=$dbh->last_insert_id( "", "filedb", "propertyname", "id" );
    }
    return $id;
}
sub adddevice {
    if (!param('device')) {
        die "invalid devicename\n";
    }
    my $devins= $dbh->prepare("INSERT INTO device () VALUES ()");
    $devins->execute;
    my $devid= $dbh->last_insert_id( "", "filedb", "device", "id" );

    my $propid= getpropid('device', 'htcname');
    my $dpins= $dbh->prepare("INSERT INTO device_property (device_id, property_id, value) VALUES (?, ?, ?)");
    $dpins->execute($devid, $propid, param('device'));
}

sub create_tables {
$dbh->do('create table propertyname (id int not null auto_increment primary key, 
                table_id int, name varchar(255));');
$dbh->do('create table record (id int not null auto_increment primary key, 
                name varchar(255));');
$dbh->do('create table file (id int not null auto_increment primary key, 
                name varchar(255), rom_id int, bytesize int, md5 varchar(32));');
$dbh->do('create table file_property (id int not null auto_increment primary key, 
                file_id int, property_id int, value varchar(255));');
$dbh->do('create table rom (id int not null auto_increment primary key, 
                device_id int, operator_id int, version varchar(64));');
$dbh->do('create table device (id int not null auto_increment primary key, 
                touchscreen tinyint, screenwidth int, screenheight int);');
$dbh->do('create table device_property (id int not null auto_increment primary key, 
                device_id int, property_id int, value varchar(255));');
$dbh->do('create table device_name (id int not null auto_increment primary key, 
                device_id int, oem_id int, name varchar(255));');
$dbh->do('create table oem (id int not null auto_increment primary key, 
                name varchar(255));');
#
#   file:[name,<rom>,size,md5] <- {prop1:val, prop2:val, ... }
#   rom:[<device>,<oem>,version]
#   device:[touch,width,height] <- {prop1:val, prop2:val, ... }
#           <- {<oem>:name, ...}
#   oem:[name]
}
sub drop_tables {
    my $sth= $dbh->prepare('show tables');
    $sth->execute;
    while (my $tablename=$sth->fetchrow_array) {
        my $drop= $dbh->do("drop table $tablename");
    }
}