<?php
require_once( dirname( __FILE__ ) . '/../_init.php' );

$filename = sys_get_temp_dir() . DIRECTORY_SEPARATOR . "thehome_subs.csv";
$domain_id = VerticalSites::THE_HOME_ID;
$reader = new SplFileObject( $filename );
$new_subscribers = $existed_subscribers = $subscriptions = $geotargets = [];
while ( $row = $reader->fgetcsv() ){
    $email = filter_var( Member::StrEmail( $row[ 2 ] ), FILTER_VALIDATE_EMAIL );
    if( empty( $email ) ){
        continue;
    }

    $member = $conn->fetchRow(
        "SELECT m.id, ms.id AS subscription_id
        FROM member m LEFT JOIN member_subscribe ms ON m.id=ms.member_id AND ms.domain_id=? AND ms.mailing_list_id IN (?)
        WHERE m.email=?",
        array($domain_id, 7, $email )
    );

    if( empty( $member ) ){
        print "$email - new" . PHP_EOL;
        $first_name = $row[ 0 ];
        $last_name = $row[ 1 ];
        $postal_code = $row [ 3 ];
        $new_subscribers[ $email ] = [ 'email' => $email, 'name' => $first_name." ".$last_name, 'postal_code' => $postal_code ];
        $result = Doctrine_Manager::connection()->fetchRow(
            "SELECT g.id, g.city_id, g.zip, c.slug FROM geotarget g INNER JOIN city c ON g.city_id=c.id WHERE zip=? AND country='AU'",
            [ ( int ) $postal_code ]
        );
        if (empty($result)) {
            $result = Doctrine_Manager::connection()->fetchRow(
                "SELECT g.id, g.city_id, g.zip, c.slug FROM geotarget g INNER JOIN city c ON g.city_id=c.id WHERE zip=? AND country='AU'",
                [ 2000 ]
            );
        }

        $geotargets[ $email ] = $result;
    }

    if( !empty( $member ) && !empty( $member[ "id" ] ) && empty( $member[ "subscription_id" ] ) ){
        print "$email - existed" . PHP_EOL;
        $existed_subscribers[ $email ] = ( int ) $member[ "id" ];
        $result = Doctrine_Manager::connection()->fetchRow(
            "SELECT g.id, g.city_id, g.zip, c.slug FROM geotarget g INNER JOIN city c ON g.city_id=c.id WHERE zip=? AND country='AU'",
            [ ( int ) $postal_code ]
        );
        if (empty($result)) {
            $result = Doctrine_Manager::connection()->fetchRow(
                "SELECT g.id, g.city_id, g.zip, c.slug FROM geotarget g INNER JOIN city c ON g.city_id=c.id WHERE zip=? AND country='AU'",
                [ 2000 ]
            );
        }

        $geotargets[ $email ] = $result;
    }
}

$members = [];
$conn->beginTransaction();
foreach ( $new_subscribers as $new_sub ){
    $conn->exec(
        "INSERT INTO member (email, name, sub_postal_code, role, created_at, updated_at, activated, imported_at, country)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
        array( $new_sub['email'], $new_sub['name'], $geotargets[ $new_sub['email'] ][ 'zip' ], Member::ROLE_SUBSCRIBER, date( "Y-m-d H:i:s" ),
            date( "Y-m-d H:i:s" ), 1, date( "Y-m-d H:i:s" ), "AU" )
    );
    $member_id = ( int ) $conn->lastInsertId();
    $members[ $member_id ] = array(
        "id" => $member_id,
        "is_new" => true
    );
    $conn->exec(
        "INSERT INTO member_subscribe (member_id, mailing_list_id, geotarget_id, city_id, city_slug,
        city_zip, domain_id, created_at, updated_at, frequency, country)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
        array( $member_id, 7, $geotargets[ $new_sub['email'] ][ 'id' ], $geotargets[ $new_sub['email'] ][ 'city_id' ],
            $geotargets[ $new_sub['email'] ][ 'slug' ], $geotargets[ $new_sub['email'] ][ 'zip' ], $domain_id,
            date( "Y-m-d H:i:s" ), date( "Y-m-d H:i:s" ), MemberSubscribe::SUBSCRIBE_DAILY, "AU")
    );
}
$conn->commit();

$conn->beginTransaction();
foreach ( $existed_subscribers as $existed_sub_email => $sub_id ){
    $conn->exec(
        "INSERT INTO member_subscribe (member_id, mailing_list_id, geotarget_id, city_id, city_slug,
        city_zip, domain_id, created_at, updated_at, frequency, country)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
        array( $sub_id, 7, $geotargets[ $existed_sub_email ][ 'id' ], $geotargets[ $existed_sub_email ][ 'city_id' ],
            $geotargets[ $existed_sub_email ][ 'slug' ], $geotargets[ $existed_sub_email ][ 'zip' ], $domain_id,
            date( "Y-m-d H:i:s" ), date( "Y-m-d H:i:s" ), MemberSubscribe::SUBSCRIBE_DAILY, "AU")
    );
    $members[ $sub_id ] = array(
        "id" => $sub_id,
        "is_new" => false
    );
}
$conn->commit();

$tm = new TransactionalMessages();
$domain_vertical = Doctrine::getTable('DomainVerticalsTable')->findOneByVerticalIdAndDomainId( VerticalSites::VERTICAL_SITE_THEHOME_ID, VerticalSites::THE_HOME_ID );
VerticalSites::$_now_site = VerticalSites::NowSite( $domain_vertical );
VerticalSites::$_now_brand = Domains::findById( $domain_id );
VerticalSites::$_now_template = Doctrine::getTable('Templates')->find( VerticalSites::$_now_brand->template_id );
VerticalSites::$_now_sender_profile = $domain_vertical->SenderProfile;
foreach ( $members as $mid => $member ){
    if( $member[ "is_new" ] ){
        MembersQueueTable::add( $member[ "id" ], MembersQueueTable::ACTION_ADD_MEMBER );
        $user = Doctrine::getTable('Member')->findOneById($member[ "id" ]);
        $city = Doctrine::getTable('City')->findOneById($geotargets[$user->email]['city_id']);
        $tm->sendWelcome($user, $city);
    }
    $subscriptions = Doctrine_Query::create()->from( "MemberSubscribe" )->where( "member_id=?", $member[ "id" ] )
        ->addWhere( "domain_id=?", $domain_id)
        ->execute();
    MembersQueueTable::add( $member[ "id" ], MembersQueueTable::ACTION_ADD_SUBSCRIPTIONS,
        array( "subscriptions" => $subscriptions ) );
}

//$databaseManager->shutdown();