sub run_schema {
    my $dbix = shift;

    my $now = DateTime->now;

    my @schema = _schema();
    do { $dbix->dbh->do($_) || die $dbix->error } foreach @schema; # $dbix->error doesn't work here
    { # draft_report_users & users table:
        my %h = (
            username   => 'queenie',
            first_name => 'miranda',
            last_name  => 'richardson',
            password   => 'WdQRFOH9t2KmvPpjgHCjTunkDpM', # sha1 Queenie
            email      => 'queenie@mail.com',
            active     => 'yes',
        );
        $dbix->insert($_, \%h) for qw(draft_report_users users);
    }
    { # requests (5):
        my %h = (
            request_number   => 1, # will auto-increment in loop
            year             => $now->year,
            patient_case_id  => 1,
            status_option_id => 1,
            referrer_department_id => 1,
        );
        $dbix->insert('requests', \%h) && $h{request_number}++ for (1..5);
    }
    { # request_specimen (5) & specimen:
        my $i = 1;
        $dbix->insert('specimens',
            { sample_code => 'PB', description => 'blood' });
        $dbix->insert('request_specimen',
            { request_id => $i++, specimen_id => 1}) for (1..5);
    }
    { # diagnoses:
        my %h = (
            name  => 'CLL',
            icdo3 => '9823/3'
        );
        $dbix->insert('diagnoses', \%h);
    }
}

sub _schema {
    return (
        q{
            CREATE TABLE draft_report_users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                username VARCHAR,
                first_name VARCHAR,
                last_name VARCHAR,
                password VARCHAR,
                last_login DATETIME,
                email VARCHAR,
                active VARCHAR
            )
        },
        q{
            CREATE TABLE users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                username VARCHAR,
                first_name VARCHAR,
                last_name VARCHAR,
                password VARCHAR,
                email VARCHAR,
                active VARCHAR NOT NULL DEFAULT 'yes'
            )
        },
        q{
            CREATE TABLE request_draft_report (
                user_id INTEGER,
                request_id INTEGER,
                request_notes VARCHAR,
                clinical_details VARCHAR,
                biopsy_site VARCHAR,
                gross_description VARCHAR,
                morphology VARCHAR,
                comment VARCHAR,
                status VARCHAR,
                diagnosis_id INTEGER,
                secondary_diagnosis_id INTEGER,
                specimen_quality INTEGER,
                created_at DATETIME,
                updated_at DATETIME
            )
        },
        q{
            CREATE TABLE requests (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                request_number INTEGER,
                year INTEGER,
                patient_case_id INTEGER,
                referrer_department_id INTEGER,
                status_option_id INTEGER,
                created_at DATETIME,
                updated_at DATETIME
            )
        },
        q{
            CREATE TABLE request_specimen (
                request_id INTEGER,
                specimen_id INTEGER
            )
        },
        q{
            CREATE TABLE specimens (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                sample_code TEXT,
                description TEXT,
                active TEXT
            )
        },
        q{
            CREATE TABLE diagnoses (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT,
                icdo3 TEXT,
                diagnostic_category_id INTEGER,
                active TEXT
            )
        },
        q{
            CREATE TABLE user_permission_view (
                user_id INTEGER,
                function_id INTEGER,
                function_name VARCHAR
            )
        }
    );
}

1;