#!/usr/bin/perl

use Text::CSV;
use Data::Dumper;

# cw_products
# 0 id  1 sku  2 product_name

# cw_skus
# 0 sku_id  1 sku  2 product_id

# cw_orders
# 0 order_id  1 date  2 status  3 customer_id

# cw_order_skus
# 0 id  1 order_id  2 sku_id

# cw_customers
# 0 customer_id  1 customer_type  2 date_added  3 date_modified  4 first_name  5 last_name
# 6 7 8 9 10 = address

@products     = load_csv("cw_products.csv");
@skus         = load_csv("cw_skus.csv");
@orders       = load_csv("cw_orders.csv");
@order_skus   = load_csv("cw_order_skus.csv");
@customers    = load_csv("cw_customers.csv");
@order_status = load_csv("cw_order_status.csv");

$product_name_lookup = {};
for $product (@products) {
  $sku_name = $product->[1];
  $product_name = $product->[2];
  $product_name_lookup->{ $sku_name } = $product_name;
}

$customer_name_lookup = {};
for $customer (@customers) {
  $id = $customer->[0];
  $name = $customer->[5] . " " . $customer->[4];
  $customer_name_lookup->{ $id } = $name;
}

$customer_lookup = {};
for $customer (@customers) {
  $id = $customer->[0];
  $customer_name_lookup->{ $id } = $customer;
}

$order_status_lookup = {};
for $os (@order_status) {
  $id = $os->[0];
  $name = $os->[1];
  $order_status_lookup->{ $id } = $name;
}

$sku_id_lookup = {};
for $sku (@skus) {
  $sku_id = $sku->[0];
  $sku_name = $sku->[1];
  # $product_name = $product_name_lookup->{ $sku_name };
  $sku_id_lookup->{ $sku_id } = $sku_name;
}

$order_customer_lookup = {};
for $order (@orders) {
  $order_id = $order->[0];
  $customer_id = $order->[3];
  # $customer_name = $customer_name_lookup->{ $customer_id };
  $order_customer_lookup->{ $order_id } = $customer_id;
}

$order_sku_lookup = {};
for $order_sku (@order_skus) {
  $order_id = $order_sku->[1];
  $sku_id = $order_sku->[2];
  $sku_name = $sku_id_lookup->{ $sku_id };
  $customer_id = $order_customer_lookup->{ $order_id };
  if ( not exists($order_sku_lookup->{ $customer_id })) {
    $order_sku_lookup->{ $customer_id } = {};
  }
  $order_sku_lookup->{ $customer_id }->{ $sku_name } = 1;
}

# print Dumper( $order_sku_lookup );

@report = ();

for $customer (@customers) {
  $has_v1 = 0;
  $has_v2 = 0;
  $has_v3 = 0;
  $has_v4 = 0;
  $customer_id = $customer->[0];
  if (! exists($order_sku_lookup->{ $customer_id })) {
    next;
  }
  $customer_skus = $order_sku_lookup->{ $customer_id };
  $r = [
    $customer->[0],
    $customer->[2],
    $customer->[4],
    $customer->[5],
    $customer->[6],
    $customer->[7],
    $customer->[8],
    $customer->[9],
    $customer->[10],
  ];
  if ( exists( $customer_skus->{"STBVOL1"} )
     || exists( $customer_skus->{"301MM"} )
     ) {
    $has_v1 = 1
  }
  if ( exists( $customer_skus->{"STBVOL2"} )
     || exists( $customer_skus->{"303MM"} )
     || exists( $customer_skus->{"Volumes 2 & 3"} )
     || exists( $customer_skus->{"Volumes 2 & 4"} )
     || exists( $customer_skus->{"Volumes 2, 3 & 4"} )
     ) {
    $has_v2 = 1
  }
  if ( exists( $customer_skus->{"330MM"} )
     || exists( $customer_skus->{"Volumes 2 & 3"} )
     || exists( $customer_skus->{"Volumes 3 & 4"} )
     || exists( $customer_skus->{"Volumes 2, 3 & 4"} )
     ) {
    $has_v3 = 1
  }
  if ( exists( $customer_skus->{"340MM"} )
     || exists( $customer_skus->{"Volumes 2 & 4"} )
     || exists( $customer_skus->{"Volumes 3 & 4"} )
     || exists( $customer_skus->{"Volumes 2, 3 & 4"} )
     ) {
    $has_v4 = 1
  }
  push(@$r, $has_v1 ? "VOLUME 1" : "");
  push(@$r, $has_v2 ? "VOLUME 2" : "");
  push(@$r, $has_v3 ? "VOLUME 3" : "");
  push(@$r, $has_v4 ? "VOLUME 4" : "");
  if ($has_v1 || $has_v2 || $has_v3 || $has_v4) {
    print Dumper($r);
    push(@report, $r);
  }
}

my $csv = Text::CSV->new ( { binary => 1 } )  # should set binary attribute.
               or die "Cannot use CSV: ".Text::CSV->error_diag ();

$fn = "cv_stb_report_reduced.csv";
open $fh, ">:encoding(utf8)", $fn or die "$fn: $!";
for $row (@report) {
  $csv->print ($fh, $row);
  print $fh "\n";
}
close $fh or die "$fn: $!";













sub load_csv () {
  my $filename = shift;
  my @rows;
  my $csv = Text::CSV->new ( { binary => 1 } )  # should set binary attribute.
                 or die "Cannot use CSV: ".Text::CSV->error_diag ();
 
  open my $fh, "<:encoding(utf8)", $filename or die "$filename: $!";
  while ( my $row = $csv->getline( $fh ) ) {
    push @rows, $row;
  }
  $csv->eof or $csv->error_diag();
  close $fh;
  return @rows;
}