#!/usr/bin/perl

use strict;
use warnings;
use DBI;

my %domains = (
    'system' => 1,
    'queue'  => 3,
    'ticket' => 1000,
);

my $dbh = DBI->connect('dbi:mysql:database=test', 'root', '');
my @schema = (
    'DROP TABLE IF EXISTS acl',
    'DROP TABLE IF EXISTS groups',
    'CREATE TABLE acl(id INT PRIMARY KEY, type varchar(32))', # ENGINE=InnoDB',
    'CREATE TABLE groups(id INT PRIMARY KEY, domain varchar(32), type varchar(32))',# ENGINE=InnoDB',
);
$dbh->do($_) foreach @schema;
$dbh->do("INSERT INTO acl VALUES(1, 'owner')");
$dbh->do("INSERT INTO acl VALUES(2, 'admincc')");

my $id = 1;
while ( my ($domain, $total) = each %domains ) {
    while ( $total-- ) {
        $dbh->do("INSERT INTO groups VALUES($id, '$domain', 'owner')");
        $id++;
        $dbh->do("INSERT INTO groups VALUES($id, '$domain', 'requester')");
        $id++;
        $dbh->do("INSERT INTO groups VALUES($id, '$domain', 'admincc')");
        $id++;
        $dbh->do("INSERT INTO groups VALUES($id, '$domain', 'cc')");
        $id++;
    }
}

my @indexes = (
    'CREATE INDEX groups_d ON groups(domain)',
    'CREATE INDEX groups_t ON groups(type)',
    'CREATE INDEX groups_dt ON groups(domain, type)',
    'CREATE INDEX groups_td ON groups(type, domain)',
);
$dbh->do($_) foreach @indexes;