#!perl

use strict;
use warnings;

use Rose::DB::Object::Loader;
use Test::More  tests => 3;
use File::Temp ();


# Grab a temporary filename for the database
my $fh = File::Temp->new();
$fh->unlink_on_destroy( 1 );
my $dbname = $fh->filename;

{
    package TestApp::DB;

    use base qw(Rose::DB);

    TestApp::DB->register_db(
        driver           => 'SQLite',
        database         => $dbname,
    );
}

# Grab a database handle
my $db = TestApp::DB->new;
my $dbh = $db->retain_dbh;

# Create tables
local $/ = "\n\n";
for (<DATA>) {
    $dbh->do( $_ ) or die $dbh->errstr;
}

# Create RDBO classes
my $loader = Rose::DB::Object::Loader->new(
    db           => $db,
    class_prefix => 'TestApp::DB',
);
$loader->make_classes;

# Create some data
my $route_a = TestApp::DB::Route->new(
    name => 'A',
    locations => [
        { type => 'start', 'name' => 'London' },
        { type => 'end',   'name' => 'Brighton' },
    ],
);
$route_a->save;
my $route_b = TestApp::DB::Route->new(
    name => 'B',
    locations => [
        { type => 'start', 'name' => 'London' },
        { type => 'end',   'name' => 'Brighton' },
    ],
);
$route_b->save;
my $route_c = TestApp::DB::Route->new(
    name => 'C',
    locations => [
        { type => 'start', 'name' => 'London' },
        { type => 'end',   'name' => 'Manchester' },
    ],
);
$route_c->save;

# Test double join
$Rose::DB::Object::Manager::Debug = 1;
my $routes = TestApp::DB::Route::Manager->get_routes(
    query => [
        't2.type' => 'start',
        't2.name' => 'London',
        't3.type' => 'end',
        't3.name' => 'Brighton',
    ],
    with_objects => ['locations', 'locations'],
    multi_many_ok => 1,
);

my @route_names = sort map { $_->name } @$routes;
is($route_names[0], 'A', "Correctly matched route A");
is($route_names[1], 'B', "Correctly matched route B");
ok(! defined $route_names[2], "Correctly skipped route C");


__DATA__
CREATE TABLE routes (
    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE locations (
    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    route_id integer NOT NULL REFERENCES routes(id),
    type VARCHAR(10) NOT NULL,
    name VARCHAR(50) NOT NULL
);