package butch::sql;
use strict;
use utf8;

sub new{
	my $invocant = shift;
	my $class = ref($invocant) || $invocant;
	my $args = shift;
	my ( $obj )  = bless {
		%$args,
		@_
	}, $class;
	return $obj;
}
### insert update
sub insert_prof{
	my $self = shift;
	my $args = shift;
	my $timestamp = time;
	my $sql = qq{
insert into prof (uid,hname,lastname,firstname,intro,ctime,mtime)
  values($args->{uid}, '$args->{hname}', '$args->{lastname}','$args->{firstname}','$args->{intro}',$timestamp,$timestamp)
};
	return $sql;
}
sub update_prof{
	my $self = shift;
	my $args = shift;
	my $timestamp = time;
	my $sql = qq{
update prof set hname='$args->{hname}', lastname='$args->{lastname}', firstname='$args->{firstname}', intro='$args->{intro}', mtime=$timestamp
  where uid=$args->{uid}
};
	return $sql;
}

sub update_bookstar{
	my $self = shift;
	my $args = shift;
	my $timestamp = time;
	my $sql = qq{
update bookstar_list set starname='$args->{starname}', staricon='$args->{staricon}', starcolor='$args->{starcolor}', starstr='$args->{starstr}', mtime=$timestamp
  where uid=$args->{uid} and starid=$args->{starid}
};
	return $sql;
}
sub insert_bookstar{
	my $self = shift;
	my $args = shift;
	my $timestamp = time;
	my $sql = qq{
insert into bookstar_list (uid,starid,starname,staricon,starcolor,starstr,ctime,mtime)
  values ($args->{uid},$args->{starid},'$args->{starname}','$args->{staricon}','$args->{starcolor}','$args->{starstr}',$timestamp,$timestamp)
};
	return $sql;
}
sub insert_book{
	my $self = shift;
	my $args = shift;
	my $sql = qq{
insert into books_list (uid,bookid,isbn,title,titleyomi,readed,bookstar,ymd,ctime,mtime)
  values ($args->[0]{uid},$args->[0]{bookid},'$args->[0]{asin}','$args->[0]{title}','$args->[0]{titleyomi}', $args->[0]{readed}, $args->[0]{bookstar},'$args->[0]{ymd}',$args->[0]{timestamp},$args->[0]{timestamp})
};
	return $sql;
}
sub update_book{
	my $self = shift;
	my $args = shift;
	my $readed   = sprintf qq{%d}, $args->{readed};
	my $bookstar = sprintf qq{%d}, $args->{bookstar};
	my $timestamp = time;
	my $ymd = $self->strtime($timestamp);
	my $sql = qq{
update books_list set readed=$readed, bookstar=$bookstar, ymd=$ymd, mtime=$timestamp
  where uid=$args->{uid} and bookid=$args->{bookid}
};
	return $sql;
}
sub update_book_timestamp{
	my $self = shift;
	my $args = shift;
	my $timestamp = time;
	my $ymd = $self->strtime($timestamp);
	my $sql = qq{
update books_list set ymd=$ymd, mtime=$timestamp
  where uid=$args->{uid} and bookid=$args->{bookid}
};
	return $sql;
}
sub insert_author{
	my $self = shift;
	my $args = shift;
	my $sql = qq{
insert into authors_list (uid,bookid,authorid,author,authoryomi,role,displayorder,ctime,mtime)
  values ($args->[0]{uid},$args->[0]{bookid},$args->[0]{authorid},'$args->[0]{author}','','',100,$args->[0]{timestamp},$args->[0]{timestamp} )
};
	return $sql;
}
sub insert_bookmemo{
	my $self = shift;
	my $args = shift;
	my $sql = qq{
insert into books_memo (uid,bookid,commentid,comment,netabare,ctime,mtime)
  values ($args->{uid}, $args->{bookid}, $args->{commentid}, '$args->{comment}', $args->{netabare}, $args->{ctime}, $args->{mtime})
};
	return $sql;
}
sub update_bookmemo{
	my $self = shift;
	my $args = shift;
	my $sql = qq{
update books_memo set comment='$args->{comment}', netabare=$args->{netabare}, mtime=$args->{mtime}
  where commentid=$args->{commentid}
};
	return $sql;
}
### select
sub get_bookid_from_authors{
	my $self = shift;
	my $args = shift;

	my $where;
	if($args->{search} ne '' && $args->{col} eq 'author'){
		$args->{search} =~ s!\'!!g;
		$args->{search} =~ s!\;!!g;
		$args->{search} =~ s! +!\%!g;
		$where = sprintf qq{ where author like '%%%s%%' }, $args->{search};
	}
	elsif( $args->{author} ){
		$where = sprintf qq{ where author = '%s' }, $args->{author};
	}

	my $sql = qq{ select bookid from authors_list $where };
	return $sql;
}
sub get_bookid_from_memos{
	my $self = shift;
	my $args = shift;
	return if ! $args->{search};
	$args->{search} =~ s!\'!!g;
	$args->{search} =~ s!\;!!g;
	$args->{search} =~ s! +!\%!g;
	my $sql = qq{ select bookid from books_memo where comment like '%$args->{search}%' };
	# my $sql = qq{ select bookid from books_memo where comment like '%$args->{memo}%' };
	return $sql;
}
sub get_books_list{
	my $self = shift;
	my $args = shift;
	my $bookid = shift;

	my $limit = $self->{page_limit};
	my $maxpage = int($args->{maxcount} / $limit); ++$maxpage if ($args->{maxcount} % $limit);
	my $offset = 0;
	if( $args->{page} && $args->{page} <= $args->{maxcount} ){
		$offset = $args->{page} * $self->{page_limit};
	}
	my $order = 'desc';
	if( $args->{order} eq 'asc' ){
		$order = 'asc';
	}

	my $sql;
	my @buf;
	if( $bookid ){
		push(@buf,sprintf qq{ books_list.bookid in (%s) }, $bookid);
	}
	if( $args->{search} ne '' ){
		if( $args->{col} eq 'title' ){
			$args->{search} =~ s!\'!!g;
			$args->{search} =~ s!\;!!g;
			$args->{search} =~ s! +!\%!g;
			push(@buf, sprintf qq{books_list.title like '%%%s%%'}, $args->{search});
		}
		elsif( $args->{col} eq 'isbn' ){
			push(@buf, sprintf qq{books_list.isbn = '%s'}, $self->check_isbn($args->{search}));
		}
	}
	else{
		if( $args->{readed} ne ''){
			push(@buf, sprintf qq{books_list.readed=%d}, $args->{readed});
		}
		if( $args->{ymd}){
			push(@buf, sprintf qq{books_list.ymd like '%s%%'}, $args->{ymd} );
		}
		if( $args->{star} =~ /^\d+$/ ){
			push(@buf, sprintf qq{books_list.bookstar=%s}, $args->{star} );
		}
	}
	my $where;
	if( @buf ){
		$where = ' where ' . join(' and ', @buf);
	}

	$sql = qq{
select books_list.uid,books_list.bookid,
		books_list.isbn,books_list.title,books_list.titleyomi,
		books_list.readed,books_list.bookstar,
		books_list.ymd,books_list.ctime,books_list.mtime
  from books_list
$where
order by books_list.mtime $order
limit $limit offset $offset
};
	my $count = qq{ select count(*) from books_list $where };
	return {sql=>$sql, count=>$count};
}
sub get_authors{
	my $self = shift;
	my $args = shift;
	return unless ($args->{uid} && $args->{bookid});
	my $sql = qq{
select authorid,author,authoryomi,role,displayorder,ctime,mtime from authors_list
  where uid=$args->{uid} and bookid=$args->{bookid}
};
	return $sql;
}
sub get_memos{
	my $self = shift;
	my $args = shift;
	return unless ($args->{uid} && $args->{bookid});
	my $sql = qq{
select commentid,comment,netabare,ctime,mtime from books_memo
  where uid=$args->{uid} and bookid=$args->{bookid} and comment is not null
  order by mtime desc
};
	return $sql;
}
sub get_book{
	my $self = shift;
	my $args = shift;
	return if ! $args;
	return unless $args->{uid} && $args->{bookid};

	my $sql = qq{
select  books_list.uid,books_list.bookid,
		books_list.isbn,books_list.title,books_list.titleyomi,
		books_list.readed,books_list.bookstar,
		books_list.ymd,books_list.ctime,books_list.mtime
  from books_list
	where books_list.uid=$args->{uid} and books_list.bookid=$args->{bookid}
};
	return $sql;
}
sub get_bookstar_list{
	my $self = shift;
	my $sql = qq{ select * from bookstar_list order by starid asc};
	return $sql;
}

### make table
sub make_table_session{
	my $self = shift;
	my $sql = qq{
create table if not exists session(
sid text,
ctime integer,
mtime integer
);
	};
	return $sql;
}
sub make_books_list{
	my $self = shift;
	my $sql = qq{
create table if not exists books_list (
uid integer,
bookid integer,
isbn text,
title text,
titleyomi text,
readed integer,
bookstar integer,
ymd text,
ctime integer,
mtime integer
);
	};
	return $sql;
}
sub make_authors_list{
	my $self = shift;
	my $sql = qq{
create table if not exists authors_list(
uid integer,
bookid integer,
authorid integer,
author text,
authoryomi text,
role text,
displayorder integer,
ctime integer,
mtime integer
);
	};
	return $sql;
}
sub make_books_memo{
	my $self = shift;
	my $sql = qq{
create table if not exists books_memo(
uid integer,
bookid integer,
commentid integer,
comment text,
netabare integer,
ctime integer,
mtime integer
);
	};
	return $sql;
}
sub make_bookstar_list{
	my $self = shift;
	my $sql = qq{
create table if not exists bookstar_list(
uid integer,
starid integer,
starname text,
staricon text,
starcolor text,
starstr text,
ctime integer,
mtime integer
);
	};
	return $sql;
}
sub make_prof{
	my $self = shift;
	my $sql = qq{
create table if not exists prof(
uid integer,
hname text,
lastname text,
firstname text,
intro text,
ctime integer,
mtime integer
);
	};
	return $sql;
}
### core
sub make_table_members{
	my $self = shift;
	my $sql = qq{
create table if not exists members(
uid integer primary key autoincrement,
email text,
pass text,
mtime integer,
enabled integer
)
	};
	return $sql;
}
sub DESTROY {
	my $self = shift;
}
1;