#!/usr/bin/perl -w

package cell;
use Time::HiRes qw( usleep ualarm gettimeofday tv_interval );
my $start=[gettimeofday];

use strict;
use Data::Dumper;
#use Server;
use Kwark::Server2;
use CGI;

my $debug=0;
my $msg='';

my $q=new CGI;

print $q->header(
#-cookie=> [$cookie1,$cookie2],
                     -charset =>'UTF-8',
                    );




my $server  = Kwark::Server2->instance();
my $tmpl    = $server->gettemplate('cell');

# login
use Apache::Session::Postgres;
my $username;
my $users_id;
my $sessionID = $q->cookie('sessionID') || undef;
if ($sessionID){
  my %session;
  eval{
    tie %session,
      'Apache::Session::Postgres',
        $sessionID , {
                      Handle => $server->{'dbh'},
                      Commit => 0,
                     } || die "cannot tie $!";
  };
  if ($session{username}){
    $username = $session{username};
    $users_id = $session{users_id};
    $tmpl->param(
                 username => $session{username},
		 users_id => $session{users_id},
                 loggedin => 1,
                );
  }
}
# /login





my $qself   = $q->self_url;
my $qselfs  = $q->url;


# rest
my $qrest   = $qself;
$qrest =~ s!$qselfs!!;
$qrest =~ s!^/!!;
my (@arest) = split( /\// , $qrest);


#$qrest =~ s!/$!!;
$qrest = $arest[0];

if (defined($arest[1]) && $arest[1] eq 'edit'){

  if ($username){
    $tmpl->param( edit => '1' );
  }else{
    $msg .= "not logged in";
  }
 #print Dumper(@arest);
}



  






#
# parse all cells from messages
#

# c.id as cellid
my $query = "
 SELECT
   m.created as mcreated,u.id as userid,m.id as messageid,
   c.id as cellid , m.cell as mcell,
  lpad(extract(day FROM m.created),2,'0')   || '-' ||
  lpad(extract(month FROM m.created),2,'0') || '-' ||
  extract(year FROM m.created)  || ' ' ||
  lpad(extract(hour FROM m.created), 2, '0')  || ':' ||
  lpad(extract(minute FROM m.created), 2, '0')
  as mcreatedd,

 lpad(extract(day FROM m.created),2,'0')   || '-' ||
  lpad(extract(month FROM m.created),2,'0') || '-' ||
  extract(year FROM m.created) 
  as mcreateddate,

  lpad(extract(hour FROM m.created), 2, '0')  || ':' ||
  lpad(extract(minute FROM m.created), 2, '0')
  as mcreatedtime,



  *
  FROM messages as m

  LEFT JOIN users   as u on u.phn = m.orig

  LEFT JOIN cell    as c on c.cellstring = m.cell


  ORDER BY m.created desc
 ";

#  || ' +' ||
#  lpad(extract(timezone_hour FROM m.created),2, '0') || ':00' 


#  RIGHT JOIN cell   as c on m.cell = c.cellstring

print $query if $debug;
my ($sth,$err) = $server->doq($query);
if ($err){
  $msg .= $err;
}else{
  my @cells;
  my $lastdate = '';
  while (my $row = $sth->fetchrow_hashref){
    if ($row->{'msg'} =~ m!cell!i ){

      # parse cellnumber
      my   ($mcc,$mnc,$lac,$cid) = $row->{'msg'}
	=~  m!(\d+),\s(\d+),\s(\d+),\s(\d+)!;
      my $pcell="$mcc-$mnc-$lac-$cid";
    
      # update messages.cell
      unless ($row->{'mcell'} eq $pcell){
	warn "$row->{'id'} [$row->{'cell'}] vs. [$pcell]";
	my $qcell = $server->quote($pcell);
	my $iquery = "
         UPDATE messages
         SET cell = $qcell
         WHERE id = $row->{'messageid'}
        ";
	my ($isth,$ierr)=$server->doq($iquery);
	if ($err){
	  $msg .= $err;
	}else{
	  warn $iquery;
	}
	$row->{'cell'} = $pcell;

       # create new cell
       $iquery="INSERT INTO cell(cellstring) values($qcell)";
       ($isth,$ierr)=$server->doq($iquery);
       warn $ierr if $ierr;
      }


      # extra extract
      my $msgextra = $row->{'msg'};
      $msgextra =~ s!cell\s\(\d+,\s\d+,\s\d+,\s\d+\)(.*)!$1!;
      $msgextra =~ s!^\s!!;
      $msgextra =~ s!\s$!!;
      if ($msgextra){
	$row->{'msgextra'} = $msgextra;
      }

      # extra insert
      if ($msgextra){
	my $qtag = $server->quote($msgextra);
	my $qusers_id = $server->quote($row->{'userid'});
	my $qcell_id  = $server->quote($row->{'cellid'});
	my $qmsgid    = $server->quote($row->{'messageid'});
	my $eiquery="
         INSERT INTO cell_tag(cell_id,tag,users_id,message_id)
         VALUES ($qcell_id,$qtag,$qusers_id,$qmsgid)
        ";
	print $eiquery if $debug;
	my ($eisth,$eierr) = $server->doq($eiquery);
	if ($eierr){
	  warn $eierr;
	}
      }


      # try
      use Kwark::Cell;
      my $kcell = new Kwark::Cell;
      my $cc   = $kcell->get( cellstring => $pcell );
      #print "cc: " . Dumper($cc);
      
      my $tags = $cc->{'tags'};
      
      # /try
      if ($tags){
	$row->{'tags'} = $tags;
	#print "<pre>" . Dumper($tags);
      }

      $row->{'qselfs'} = $qselfs;


      if ($lastdate eq $row->{'mcreateddate'}){
      }else{
	$row->{'newdate'} = $row->{'mcreateddate'};
	$lastdate = $row->{'mcreateddate'};
      }

      push @cells,$row;
    }
  }
  $tmpl->param( cells => \@cells,);
}
#
# /all
#




#
# if rest
#
if ($qrest && $q->param('tag') ){
 $tmpl->param( edit => '1' );

 #fout
 my $cell_id = $q->param('cell_id');
 
 my $tag = $q->param('tag');
 my $qtag = $server->quote($tag);
 my $qqrest = $server->quote($qrest);
 

 unless ($users_id){
  $msg .= "not logged in";
 }else{
   my $qusers_id = $server->quote($users_id);
  # my $query = "
  #  INSERT INTO rel_locationname_cell(tag,cell_id,locationname_id) 
  #  VALUES
  #  ($qtag , $cell_id, '1')
  # ";

   my $query = "
    INSERT INTO cell_tag(tag,cell_id,users_id)
    VALUES
    ($qtag , $cell_id, $qusers_id)
   ";
   my ($sth,$err)= $server->doq($query);
   if ($err){	
     $msg = $err;
   }else{
     $msg="inserted";
   }
 } # end if login
 
}

if ($qrest){
  my $cell_id;
  my $qqrest = $server->quote($qrest);
  my $query = "
   SELECT c.id as cell_id, m.* 
   FROM messages as m, cell as c 
   WHERE m.cell =  $qqrest
   AND c.cellstring = $qqrest
   ORDER BY m.created desc
  ";
  my ($sth,$err) = $server->doq($query);
  if ($err){
    $msg .= $err;
  }else{
    my @qcells;
    while (my $row = $sth->fetchrow_hashref){
      if ($row->{'msg'} =~ m!cell!i ){
	$row->{'qselfs'} = $qselfs;
	push @qcells,$row;
        $cell_id = $row->{'cell_id'};
      }
    }
    $tmpl->param( qcells => \@qcells,
		  cell_id => $cell_id
	         );
  }

  #,cell_tag as ct
  #   SELECT cell.id as cellid, ct.id as ctid, *
  # extra
  $query = "
   SELECT *
   FROM cell as c,cell_tag as ct
   LEFT JOIN locationname as ln on ln.id = ct.tag
   RIGHT JOIN users as u on ct.users_id  = u.id
   WHERE 
   c.cellstring = $qqrest
   AND c.id = ct.cell_id
  ";

  print $query if $debug;
 ($sth,$err)=$server->doq($query);
 if ($err){
  $msg .= $err; 
 }

   my @tags;
   while (my $row = $sth->fetchrow_hashref){
     #print "<pre>" . Dumper($row) . "</pre>";
     push @tags,$row;
   } 
   $tmpl->param( tags => \@tags, );



}
#
# /rest
#


# access
if (1){
  use Lok;
  my $la        = Lok->new( '/gfx/Icons/ico.cell.png' );
  my @hits      = @{$la->updateaccess( {onlywithrefer => 1,} ) };
  my $totalhits = $la->totalhits;
  $tmpl->param( hitsa => \@hits );
}

# timing
my $end=[gettimeofday];
my $elapsed = tv_interval ( $start, $end );

$tmpl->param( qrest   => $qrest,
	      elapsed => $elapsed,
	      qselfs  => $qselfs,
	      qself   => $qself,
              msg     => $msg,
	    );

print $tmpl->output;




=head

CREATE TABLE cell_tag(
 id serial    UNIQUE,
 cell_id int  NOT NULL,
 users_id int NOT NULL,
 tag varchar  NOT NULL,
 created      timestamp with time zone not null default now(),
 foreign key (cell_id) references cell(id),
 foreign key (users_id) references users(id)
)

CREATE TABLE rel_locationname_cell(
 id serial UNIQUE,
 locationname_id int NOT NULL,
 cell_id         int NOT NULL,
 created         timestamp with time zone not null default now(),

 foreign key (cell_id) references cell(id)

);

CREATE TABLE cell(
 id serial UNIQUE ,
 cellstring varchar NOT NULL UNIQUE,
 created         timestamp with time zone not null default now()
);



=cut