#!/bin/perl5

use strict;
use warnings;
use Data::Dumper;
use DBI;
use HTML::Template;

$|++;

my $dbh = DBI->connect(
  "DBI:mysql:database=map;host=localhost", 
  "", 
  "", 
  {RaiseError => 1}
);

my $t = HTML::Template->new(
  filename => 'subjects_tmpl.html',
);

# my $category_loop = get_category_loop();
# die Dumper $category_loop;

$t->param(
  category_loop => get_category_loop()
);

open my $fh, '>', 'subjects.html'
  or die "can't open to write: $!\n";
print $fh $t->output;
close $fh;

sub get_category_loop{
  
  my $sql_category = q{
    SELECT   category_id,
             category_name
    FROM     category 
    ORDER BY category_id
  };
  
  my $sql_subject = q{
    SELECT   subject_id, 
             subject_name
    FROM     subject 
    WHERE    category_id = ? 
    ORDER BY subject_name
  };
  
  my $sth_subject = $dbh->prepare($sql_subject);
  
  return [
    map{add_subject_loop($sth_subject, $_)} 
    @{$dbh->selectall_arrayref($sql_category, {Slice => {}})}
  ];
    
}

sub add_subject_loop{
  
  my ($sth, $category_loop) = @_;
  
  $sth->execute($category_loop->{category_id});
  $category_loop->{subject_loop} = $sth->fetchall_arrayref({});
  
  delete $category_loop->{category_id}; # not used by the tmpl
  
  return $category_loop;
  
}