#!/usr/bin/perl -w

use MyDB::Main;
use strict;
use Data::Dump qw( dump );

my $schema = MyDB::Main->connect('dbi:SQLite: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 (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    cdid        INTEGER NOT NULL REFERENCES cd(cdid),
    trackid     INTEGER NOT NULL REFERENCES track(trackid)
 );
}
) 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 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, ] );