#!/usr/bin/perl

use DBI;
use Carp;
use Data::Dumper;
use Storable;

my $db_sid = "veradb";
my $db_user = "DEV_DM_BUILD";
my $db_pass = "asc#Helper9asc#Helper9";

  print("Connecting to the DM_BUILD\n");

  my $dbh = DBI->connect(sprintf("dbi:%s:%s", 'Oracle', $db_sid),
                         $db_user,
                         $db_pass,
                         { PrintError => 0, RaiseError => 1, AutoCommit => 0}) or confess "could not connect" . $DBI::errstr;


  my $sth = $dbh->prepare("drop TABLE CONTACT_HISTORY_CHG_pfay");
     $sth->execute();
     print ("dropped table\n");

  $sth->finish();

my $sql = qq{CREATE TABLE CONTACT_HISTORY_CHG_pfay
TABLESPACE TS_PROC
PCTUSED 99
PCTFREE 0
STORAGE ( INITIAL 10M NEXT 1M PCTINCREASE 0)
PARALLEL ( DEGREE 4)
NOLOGGING
NOCOMPRESS
 as
select * FROM (
    SELECT CAST(CONTACT_HISTORY.CONTACT_URN as NUMBER(12)) as CONTACT_URN
          ,CAST(MDM_CHG_PERM_ID.INDIV_ID as NUMBER(13)) as INDIV_ID
          ,CAST(CONTACT_HISTORY.CONTACT_POINT_TYP_CD as VARCHAR2(3)) as CONTACT_POINT_TYP_CD
          ,CAST(CONTACT_HISTORY.CONTACT_POINT_ID as NUMBER) as CONTACT_POINT_ID
          ,CAST(CONTACT_HISTORY.CELL_KEY as VARCHAR2(63)) as CELL_KEY
          ,CAST(CONTACT_HISTORY.CAMPAIGN_KEY as VARCHAR2(63)) as CAMPAIGN_KEY
          ,CAST(CONTACT_HISTORY.SEGMENT_KEY as VARCHAR2(63)) as SEGMENT_KEY
          ,CAST(CONTACT_HISTORY.LIST_SELECT_ID as NUMBER(18)) as LIST_SELECT_ID
          ,CAST(CONTACT_HISTORY.MESSAGE_ID as NUMBER(10)) as MESSAGE_ID
          ,CAST(CONTACT_HISTORY.OUTPUT_DT as DATE) as OUTPUT_DT
          ,CAST(CELL.CELL_DEPLOYMENT_DT as DATE) as MAILED_DT
          ,CAST(RESPONSE_AGG_CHG.OPENED_DT as DATE) as OPENED_DT
          ,CAST(RESPONSE_AGG_CHG.DELIVERED_FLG as VARCHAR2(1)) as DELIVERED_FLG
          ,CAST(RESPONSE_AGG_CHG.DELIVERY_CD as VARCHAR2(2)) as DELIVERY_CD
          ,CAST(CONTACT_HISTORY.VENDOR_SUPPRESS_RSN as VARCHAR2(3)) as VENDOR_SUPR_RSN_CD
          ,CAST(RESPONSE_AGG_CHG.FST_CLICK_DT as DATE) as FST_CLICK_DT
          ,CAST(RESPONSE_AGG_CHG.LST_CLICK_DT as DATE) as LST_CLICK_DT
          ,CAST(RESPONSE_AGG_CHG.CLICK_CNT as NUMBER) as CLICK_CNT
          ,CAST(RESPONSE_AGG_CHG.OPTED_OUT_DT as DATE) as OPTED_OUT_DT
          ,CAST(MDM_CHG_PERM_ID.PERM_ID as NUMBER(13)) as PERM_ID
          ,CAST(null as VARCHAR2(15)) as BRAND_CD
          ,CAST('GENR' as VARCHAR2(15)) as MSG_PURPOSE_TYP_CD
          ,CAST(CONTACT_HISTORY.SELECTION_LEVEL_URN as NUMBER(13)) as SELECTION_LEVEL_URN
          ,CAST(CONTACT_HISTORY.SELECTION_LEVEL_CD as VARCHAR2(5)) as SELECTION_LEVEL_CD
          ,CAST(CONTACT_HISTORY.FUSE_FILE_ID as NUMBER) as FUSE_FILE_ID
          ,CAST(CONTACT_HISTORY.CONTROL_FLG as VARCHAR2(1)) as CONTROL_FLG
      FROM DEV_WAREHOUSE.CONTACT_HISTORY CONTACT_HISTORY
           Inner Join MDM_CHG_PERM_ID_11266 MDM_CHG_PERM_ID on CONTACT_HISTORY.PERM_ID=MDM_CHG_PERM_ID.PERM_ID
           Left Outer Join CELL CELL on CONTACT_HISTORY.CELL_KEY = CELL.CELL_KEY
           Left Outer Join RESPONSE_AGG_CHG_11266 RESPONSE_AGG_CHG on CONTACT_HISTORY.CONTACT_URN=RESPONSE_AGG_CHG.CONTACT_URN
) foo
};


    print("prepearing sql\n");
    # Prepare the SQL query
    $sth = $dbh->prepare($sql)
        or confess "ERROR:$0: Error in SQL prepare:\n" .  $sth->errstr;

   $sth->trace( 30, 'trace30.txt' );


    print("executing sql\n");
    # Execute the SQL Query
    $sth->execute();

# or do {
#       print("Return Value $rv\n");
#      print("-------------------------------------\n\n");
#      print Dumper tied %$dbh;
       #print(Dumper($dbh->errstr));
       #print(Dumper($DBI::errstr));
       #$store \%$dbh, 'STH_VALUE.txt';
       #store \$dbh, 'DBH_VALUE.txt';
       #store \$DBI, 'DBI_VALUE.txt';
#    };
#or do {
#        $msg = "Can't execute SQL statement:" . $DBI::errstr;
#        confess "$msg";
#    };

    print("finshed sql\n");
    $sth->finish();
    print("disconnect\n");
    $dbh->disconnect();