#!/usr/local/bin/perl

use strict;
use warnings;
use DBI;
use CGI qw(-nosticky);
use Graphics::Magick;

my $q = CGI->new();

my $me = $ENV{SCRIPT_NAME};

my $HOME = '/home/hostedsites/www.jeremykister.com';
my $file = "${HOME}/data/sql/smspy.db";
my $dsn = "DBI:SQLite:dbname=$file";

my $uid = $q->param('uid') || die "no uid param.\n";
unless( $uid =~ /^[a-zA-Z0-9_\.]{32}$/ ){
    die "uid fmt error.\n";
}

my $dbh = DBI->connect($dsn);
$dbh->do( 'PRAGMA foreign_keys = ON' );

our %global;
my $sql =  q{ SELECT key, value, devices.id
                FROM global,devices
               WHERE global.d_id = devices.id
                 AND ( devices.id = 0
                       OR devices.uid = } . $dbh->quote($uid) . ')';
my $sth = $dbh->prepare($sql);
$sth->execute;
while( my $row = $sth->fetchrow_arrayref ){
    $global{$row->[2]}{$row->[0]} = $row->[1];
}

our $dev_label = $uid; # changes to name later


if( (my $addr=$q->param('sender')) && (my $wifi=$q->param('wifi4')) ){
    die "addr err: $addr\n" unless( $addr =~ /^(?:\d{10}|\S+\@\S+\.\S+)$/ );
    die "wifi err: $wifi\n" unless( $wifi =~ /^[A-Z0-9]{4}$/i );

    $dbh->do( 'INSERT INTO devices (uid,addr,wifi) VALUES (?,?,?)', undef, ($uid,$addr,$wifi) );
    
    verbose( "saved device $uid -> $addr ($wifi)" );
    print $q->header(),
          "OK\n";
    exit;
}

if( $q->param('query') ){

    my $sql = 'SELECT addr FROM devices WHERE devices.uid = ' . $dbh->quote($uid);
    my $sth = $dbh->prepare($sql);
    $sth->execute;
    my $row = $sth->fetchrow_arrayref;
    unless( $row->[0] ){
        print $q->header(),
              "want: sender,wifi\n";
        verbose( "query for uid: $uid - need phone." );
        exit;
    }

    $sql = q{ SELECT messages.rowid
                FROM devices, messages
               WHERE devices.id = messages.d_id
                 AND devices.uid = ?
               ORDER BY rowid DESC LIMIT 1 };
    $sth = $dbh->prepare($sql);
    $sth->execute($uid);
    $row = $sth->fetchrow_arrayref;
    my $mid = $row->[0] || 0;
    
    print $q->header(),
          "mid: $mid\n";
    verbose( "query for uid: $uid - mid: $mid" );

    exit;
}

my $xaref = $dbh->selectrow_arrayref(
 'SELECT id,name FROM devices WHERE uid = ' . $dbh->quote($uid)
);

my $id = $xaref->[0] || die "cant find id for uid $uid\n";
$dev_label = $xaref->[1];


if( (my $contact = $q->param('sender')) && (my $name = $q->param('name')) ){
    die "invalid contact: $contact\n" unless( $contact =~ /^(?:\d{10}|\S+\@\S+\.\S{2,4})$/ );
    my $imguri = $q->param('imguri');

    # client is sending contact info
    # is this an update or a new contact ?
    my $aref = $dbh->selectrow_arrayref(
        q{ SELECT id
             FROM contacts
            WHERE d_id = } . $dbh->quote($id) .
        q{   AND  contact = } . $dbh->quote($contact)
     );
    my $sql;
    if( $aref->[0] ){
        verbose( "updating contact $contact -> $name" );

        $sql = 'UPDATE contacts ' .
               '   SET name =  ' . $dbh->quote($name) . ',' .
               '       date =  ' . time();
        if( $imguri ){
            $sql .= ', imguri = ' . $dbh->quote($imguri);
        }
        $sql .= ' WHERE id = '. $dbh->quote( $aref->[0] );
    }else{
        verbose( "saving contact $contact -> $name" );

        $sql = 'INSERT INTO contacts (d_id,contact,name,imguri,date) VALUES (' .
               $dbh->quote($id)      . ',' .
               $dbh->quote($contact) . ',' .
               $dbh->quote($name)    . ',' .
               $dbh->quote($imguri)  . ',' .
               time() . ')';
    }
    $dbh->do( $sql );
   

    print $q->header(),
          "got contact.\n";

    exit;
}

if( (my $filename = $q->param('blob')) && (my $rowid = $q->param('rowid')) ){
    verbose( "received: $filename" );
    # this takes 3 types of files: movie, image, preview of movie in jpg form.

    die "bad filename: $filename\n" if( $filename =~ /\.\./ );

    my $preview = $filename =~ s/-(?:right|left)//;

    unless( $preview ){
        my ($file) = $filename =~ m#([^/]+)$#;

        my $aref = $dbh->selectrow_arrayref(
            q{ SELECT id
                 FROM attachments
                WHERE file = } . $dbh->quote($file) .
            q{    AND d_id = } . $dbh->quote($id) .
            q{    AND m_rowid = } . $dbh->quote($rowid)
         );

        if( $aref->[0] ){
            warn "not inserting attachment; already have it.\n";
        }else{
            $dbh->do( 'INSERT INTO attachments (m_rowid,d_id,file) VALUES (?,?,?)', undef, ($rowid,$id,$file) );
        }
    }

    defined(my $lfh = $q->upload('blob')) || die "upload wtf\n";
    my $ioh = $lfh->handle;

    my $dir = "${HOME}/www/sms/${uid}";
    unless( -d $dir ){
        mkdir($dir, 0755) || die "cannot mkdir $dir: $!\n";
    }

    die "bad parameters.\n" unless( int($rowid) eq $rowid );
    $dir .= "/${rowid}";
    unless( -d $dir ){
        mkdir($dir, 0755) || die "cannot mkdir $dir: $!\n";
    }
    
    open(my $outfile, '>', "${dir}/${filename}") || die "cannot write to $dir/$filename: $!\n";
    my $buf;
    while( $ioh->read($buf, 4096) ){
        print $outfile $buf;
    }
    close $outfile;

    if( $filename =~ /\.(?:jpe?g|gif|png)$/i && (! $preview) ){
        verbose( "starting thumbnail conversion" );

        my $image = Graphics::Magick->new;
        my $rc = $image->Read("${dir}/${filename}");
        verbose( "$filename read error: $rc" ) if $rc;
        
        unless( $rc ){
            my $rc = $image->Scale( width => 60, height => 80 );
            verbose( "$filename scale error: $rc" ) if $rc;

            unless( $rc ){
                my $rc = $image->Write("${dir}/thumb-${filename}");
                verbose( "$filename write error: $rc" ) if $rc;
            }
        }
    }

    verbose( "$filename processed." );

    print $q->header(),
          "got filename: $filename\n";

    exit;
}

my $contact = $q->param('sender') || die "no contact?\n";

$sql = 'SELECT id FROM messages WHERE rowid = ' . $dbh->quote($q->param('rowid')) .
       ' AND d_id = ' . $dbh->quote($id) .
       ' AND contact = ' . $dbh->quote($contact);
$sth = $dbh->prepare($sql);
$sth->execute;
my $row = $sth->fetchrow_arrayref;
if( $row->[0] ){
    warn "already have message: $row->[0]\n";
    print $q->header(),
          "got rowid: [$q->param('rowid')]\n";
    exit;
}

verbose( "saving message: ", $q->param('rowid') );
my $datea = $q->param('datea'); # if null, dbh->quote gets upset using param direct
my $text = $q->param('text');
$sql = 'INSERT INTO messages (d_id,rowid,date,contact,type,datea,text) VALUES (' .
        $dbh->quote($id)                 . ',' .
        $dbh->quote($q->param('rowid'))  . ',' .
        $dbh->quote($q->param('date'))   . ',' .
        $dbh->quote($contact)            . ',' .
        $dbh->quote($q->param('type'))   . ',' .
        $dbh->quote($datea)  . ',' .
        $dbh->quote($text)   . ')';

$sth = $dbh->prepare($sql);
$sth->execute || die $sth->errstr;

$sql = q{ SELECT c.name, c.date
            FROM devices d, contacts c
           WHERE d.id = c.d_id
             AND d.uid = ?
             AND c.contact = ? };
$sth = $dbh->prepare($sql);
$sth->execute($uid,$contact);
$row = $sth->fetchrow_arrayref;

print $q->header(),
      "got rowid: [$q->param('rowid')]\n";

my $time = time();
if( ! $row->[0] || ($time - $row->[1] > 604800) ){
    # dont overwhelm device with requests for contacts it doesnt have
    my $cstring = 'contact:' . $contact;
    $global{$id}{$cstring} ||= 0;
    if( ($time - $global{$id}{$cstring}) > 86400 ){
        verbose( "requesting contact info for $contact" );

        my $r = $dbh->do( 'UPDATE global SET value = ? WHERE key = ? AND d_id = ?', undef, ($time,$cstring,$id) );
        $dbh->do( 'INSERT INTO global (d_id,key,value) VALUES (?,?,?)', undef, ($id,$cstring,$time) ) if($r eq '0E0');
        $global{$id}{$cstring} = $time; # really just for mod_perl

        print "contact: $contact\n";
    }
}

sub verbose {
    my $msg = join('', @_);

    my $x = localtime();
    warn "[$x] smspy-rcvr ${dev_label} $ENV{REMOTE_ADDR} - $msg\n";
}
