#!/usr/bin/perl

use lib qw( t ../lib lib );
use strict;
use warnings;
use MyDBIC::Main;
use Data::Dump;

my $schema = MyDBIC::Main->connect('dbi:SQLite:dbic_example.db');
my $dbh    = $schema->storage->dbh;

$dbh->do(
    qq{
CREATE TABLE artist (
    artistid INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL
  );}
) or die;

$dbh->do(
    qq{
CREATE TABLE cd (
    cdid INTEGER PRIMARY KEY AUTOINCREMENT,
    artist INTEGER NOT NULL REFERENCES artist(artistid),
    title TEXT NOT NULL
  );
}
) or die;

$dbh->do(
    qq{
CREATE TABLE track (
    trackid INTEGER PRIMARY KEY AUTOINCREMENT,
    title   TEXT NOT NULL
  );
}
) or die;

$dbh->do(
    qq{
CREATE TABLE cd_track_join (
    cdid        INTEGER NOT NULL REFERENCES cd(cdid),
    trackid     INTEGER NOT NULL REFERENCES track(trackid)
 );
}
) or die;

$dbh->do(
    qq{
CREATE TABLE cd_edition (
    cdid        INTEGER NOT NULL REFERENCES cd(cdid),
    lang        character(2) not null,
    PRIMARY KEY( cdid, lang )
 );
}
) or die;

$dbh->do(
    qq{
CREATE TABLE cd_collection (
    cdid        INTEGER NOT NULL REFERENCES cd_edition(cdid),
    lang        character(2) not null,
    name        varchar(128),
    primary key (cdid, lang),
    foreign key (cdid, lang) references cd_edition( cdid, lang )
 );
}
) or die;

$dbh->do(
    qq{
CREATE unique index cd_collection_name on cd_collection(name);
}
) or die;

#  here's some of the sql that is going to be generated by the schema
#  INSERT INTO artist VALUES (NULL,'Michael Jackson');
#  INSERT INTO artist VALUES (NULL,'Eminem');

my @artists = ( ['Michael Jackson'], ['Eminem'] );
$schema->populate( 'Artist', [ [qw/name/], @artists, ] );

my %albums = (
    'Thriller'                => 'Michael Jackson',
    'Bad'                     => 'Michael Jackson',
    'The Marshall Mathers LP' => 'Eminem',
);

my @cds;
foreach my $lp ( sort keys %albums ) {
    my $artist
        = $schema->resultset('Artist')->search( { name => $albums{$lp} } );
    push @cds, [ $lp, $artist->first->artistid ];
}

#warn("populate 'cd' with " . Data::Dump::dump \@cds );

$schema->populate( 'Cd', [ [qw/title artist/], @cds, ] );

my %tracks = (
    'Beat It'         => 'Thriller',
    'Billie Jean'     => 'Thriller',
    'Dirty Diana'     => 'Bad',
    'Smooth Criminal' => 'Bad',
    'Leave Me Alone'  => 'Bad',
    'Stan'            => 'The Marshall Mathers LP',
    'The Way I Am'    => 'The Marshall Mathers LP',
);

$schema->populate( 'Track', [ ['title'], map { [$_] } sort keys %tracks ] );

my @tracks;
foreach my $track_title ( sort keys %tracks ) {
    my $cdid = $schema->resultset('Cd')
        ->search( { title => $tracks{$track_title}, } )->first->cdid;
    my $trackid
        = $schema->resultset('Track')->find( { title => $track_title } )
        ->trackid;
    push @tracks, [ $cdid, $trackid ];
}

$schema->populate( 'CdTrackJoin', [ [qw/cdid trackid/], @tracks, ] );

# the long way just for clarity
$dbh->do(
    qq{
insert into cd_edition values (1, 'en');
}
) or die;

$dbh->do(
    qq{
insert into cd_edition values (1, 'fr');
}
) or die;

$dbh->do(
    qq{
insert into cd_collection values (1, 'en', 'collection 1 in english');
}
) or die;

$dbh->do(
    qq{
insert into cd_collection values (1, 'fr', 'collection 1 in french');
}
) or die;