# ko_coffeeshop.sh 20221030_0500
# ko_coffeeshop.sh 20221020_0100

progid="ko_coffeeshp"
dttm=m=`date +"%Y%m%d_%H%M%S"`

db3fil="ko.db3"
tbl="ko_coffeeshop"

sqlite3 $db3fil < $tbl.sql


# gs -dBATCH -dNOPAUSE -q -sDEVICE=pdfwrite -sOutputFile=koiperms.pdf /pdf/konet.pdf /pdf/iperms.pdf
# convert -density XXX 1.pdf 1.jpg 1.pdf



ids="konet iperms royal1 ko0 ko1 ko2 ka1 jkwii skwii jk sk jak1 jak2 jak3 jtfdc jtfinc med law gov ka kids koed go kt it jhf webb hamamoto housatonic gigaohm polly bookmarkusb mars myco miniraptors"
for id in $ids;do
	printf "id=$id\n"
	sqlite3 $db3fil "insert into $tbl (id ) values ('$id');"
	fb="http://ko2.us/fb/$id"
	pdf="http:///pdf/$id.pdf"
	sqlite3 $db3fil "update $tbl set fb='$fb', pdf='$pdf'"
	done
sqlite3 $db3fil "select * from $tbl"


sqlite3 $db3fil "update $tbl set dttm='$dttm';"
sqlite3 $db3fil "update $tbl set logo='/ko.png';"
sqlite3 $db3fil "update $tbl set name='konet';"
sqlite3 $db3fil "update $tbl set home='https://ko2.us';"
sqlite3 $db3fil "update $tbl set email='jkwii@ko2.us';"
sqlite3 $db3fil "update $tbl set fab='https://facebook.com';"
sqlite3 $db3fil "update $tbl set gab='https://gab.com';"
sqlite3 $db3fil "update $tbl set ss='https://substack.com';"
sqlite3 $db3fil "update $tbl set ts='https://truthsocial.com';"
sqlite3 $db3fil "update $tbl set tw='https://twitter.com';"
sqlite3 $db3fil "update $tbl set pl='https://parler.com';"
sqlite3 $db3fil "update $tbl set coffee_url='https://buymeacoffee.com/RoyalOne';"

# sqlite3 $db3fil "select * from $tbl"
# sqlite3 $db3fil  -header "select * from $tbl" > $tbl.psv
# sqlite3 $db3fil  -header "select * from $tbl" | tr '|' ',' > $tbl.csv

# sqlite3 $db3fil ".import coffeec_list.psv coffeec_list"
# sqlite3 $db3fil ".import media_list.psv media_list"
# sqlite3 $db3fil ".import host_list.psv host_list"
sqlite3 $db3fil "update $tbl set coffee_url='https://buyemacoffee.com#' where coffee_url == ''"

sqlite3 $db3fil "update $tbl set fbtxt='fbtxt' where fbtxt == ''"
sqlite3 $db3fil "update $tbl set pdftxt='pdftxt' where pdftxt == ''"
sqlite3 $db3fil "update $tbl set giftxt='giftxt' where pdftxt == ''"
sqlite3 $db3fil "update $tbl set mp3txt='mp3txt' where mp3txt == ''"
sqlite3 $db3fil "update $tbl set mp4txt='mp4txt' where mp4txt == ''"
sqlite3 $db3fil "update $tbl set bctxt='bitchute' where bctxt == ''"
sqlite3 $db3fil "update $tbl set yttxt='youtube' where yttxt == ''"

sqlite3 $db3fil "update $tbl set pn='https://ko2.us/pn' where pn == ''"
sqlite3 $db3fil "update $tbl set pntxt='pano' where pntxt == ''"
sqlite3 $db3fil "update $tbl set tl='https://ko2.us/tl' where tl == ''"
sqlite3 $db3fil "update $tbl set tltxt='timeline' where tltxt == ''"
sqlite3 $db3fil "update $tbl set sl='https://ko2.us/sl' where sl == ''"
sqlite3 $db3fil "update $tbl set sltxt='slideshow' where sltxt == ''"
sqlite3 $db3fil "update $tbl set zm='https://ko2.us/zm' where zm == ''"
sqlite3 $db3fil "update $tbl set zmtxt='zoom' where zmtxt == ''"

sqlite3 $db3fil "update $tbl set fb='/fb/' || id where fb == ''"
sqlite3 $db3fil "update $tbl set pdf='/pdf/' || id || '.pdf' where pdf == ''"
sqlite3 $db3fil "update $tbl set bc='https://bitchute.com' where bc ==''"
sqlite3 $db3fil "update $tbl set fab='https://facebook.com' where fab == ''"
sqlite3 $db3fil "update $tbl set gab='https://facebook.com' where gab == ''"
sqlite3 $db3fil "update $tbl set li='https://linkedin.com' where li == ''"
sqlite3 $db3fil "update $tbl set pl='https://parler.com' where pl == ''"
sqlite3 $db3fil "update $tbl set rm='https://rumtble.com' where rm ==''"
sqlite3 $db3fil "update $tbl set ss='https://substack.com' where ss == ''"
sqlite3 $db3fil "update $tbl set ts='https://truthsocial.com' where ts ==''"
sqlite3 $db3fil "update $tbl set tw='https://twitter.com' where tw ==''"
sqlite3 $db3fil "update $tbl set vm='https://vimeo.com' where vm ==''"
sqlite3 $db3fil "update $tbl set yt='https://youtube.com' where yt ==''"

sqlite3 $db3fil "update $tbl set bitcoin='https://bitcoin.com' where bitcoin ==''"
sqlite3 $db3fil "update $tbl set shopify='https://shopify.com' where shopify ==''"
sqlite3 $db3fil "update $tbl set squareup='https://squareup.com' where squareup ==''"
sqlite3 $db3fil "update $tbl set stripe='https://stripe.com' where stripe ==''"

sqlite3 $db3fil "update $tbl set email='jkwii@ko2.us' where email == ''"

sqlite3 $db3fil "update $tbl set phn='' where phn == ''"
sqlite3 $db3fil "update $tbl set logo='ko.png' where logo == ''"
sqlite3 $db3fil "update $tbl set note='note.' where note == ''"

sqlite3 $db3fil "select * from $tbl"

gifs=`head -18 ../pj/find_giftn.txt`
	j=0;
	for gif in $gifs; do
		let j=j+1
		printf "sqlite3 $db3fil \"update $tbl set gif='$gif' where rowid=$j\"\n"
		sqlite3 $db3fil "update $tbl set gif='$gif' where rowid=$j"
		done
mp3s=`tail -18 ../pj/find_giftn.txt`
	for gif in $gifs; do
		let j=j+1
		sqlite3 $db3fil "update $tbl set gif='$gif',hostid='ka1'  where rowid=$j"
		done
	sqlite3 $db3fil "select rowid,coffee_id,gif from coffeeshop where hostid != ''"

mp3s=`head -18 ../pj/find_mp3.txt`
	j=0;
	for mp3 in $mp3s; do
		let j=j+1
		printf "sqlite3 $db3fil \"update $tbl set mp3='$mp3' where rowid=$j\"\n"
		sqlite3 $db3fil "update $tbl set mp3='$mp3' where rowid=$j"
		done
mp3s=`tail -18 ../pj/find_mp3.txt`
	for mp3 in $mp3s; do
		let j=j+1
		printf "sqlite3 $db3fil \"update $tbl set mp3='$mp3' where rowid=$j\"\n"
		sqlite3 $db3fil "update $tbl set mp3='$mp3',hostid='ka1' where rowid=$j"
		done
	sqlite3 $db3fil "select rowid,hostid,coffee_id,mp3,mp3txt from coffeeshop"


mp3s=`head -18 ../pj/find_mp4.txt`
	j=0;
	for mp4 in $mp4s; do
		let j=j+1
		printf "sqlite3 $db3fil \"update $tbl set mp4='$mp4' where rowid=$j\"\n"
		sqlite3 $db3fil "update $tbl set mp4='$mp4' where rowid=$j"
		done
mp4s=`tail -18 ../pj/find_mp4.txt`
	for mp4 in $mp4s; do
		let j=j+1
		printf "sqlite3 $db3fil \"update $tbl set mp4='$mp4' where rowid=$j\"\n"
		sqlite3 $db3fil "update $tbl set mp4='$mp4', hostid='ka1' where rowid=$j"
		done

	sqlite3 $db3fil "select rowid,hostid, id,mp4,mp4txt from coffeeshop"

yts=`head -18 ../pj/find_yts.txt`
	j=0;
	for yt0 in $yts; do
		yt=`echo $yt0 | awk -F"|" '{ print $1 }'`
		yttxt=`echo $yt0 | awk -F"|" '{ print $2 }'`
		let j=j+1
		printf "sqlite3 $db3fil \"update $tbl set yt='$yt' where rowid=$j\"\n"
		sqlite3 $db3fil "update $tbl set yt='$yt', yttxt='$yttxt' where rowid=$j"
		done
mp3s=`tail -18 ../pj/find_yts.txt`
	for yt0 in $yts; do
		yt=`echo $yt0 | awk -F"|" '{ print $1 }'`
		yttxt=`echo $yt0 | awk -F"|" '{ print $2 }'`
		let j=j+1
		printf "sqlite3 $db3fil \"update $tbl set yt='$yt' where rowid=$j\"\n"
		sqlite3 $db3fil "update $tbl set yt='$yt', yttxt='$yttxt', hostid='ka12' where rowid=$j"
		done
	sqlite3 $db3fil "select rowid,hostid,coffee_id,yt,yttxt from coffeeshop"


mp3s=`head -18 ../pj/find_bcs.txt`
	j=0;
	for bc0 in $bcs; do
		bc=`echo $bc0 | awk -F"|" '{ print $1 }'`
		bctxt=`echo $bc0 | awk -F"|"  '{ print $1 }'`
		let j=j+1
		printf "sqlite3 $db3fil \"update $tbl set bc='$bc' where rowid=$j\"\n"
		sqlite3 $db3fil "update $tbl set bc='$bc', bctxt='$bctxt' where rowid=$j"
		done
bcs=`tail -18 ../pj/find_bcs.txt`
	for bc0 in $bcs; do
		bc=`echo $bc0 | awk -F"|" '{ print $1 }'`
		bctxt=`echo $bc0 | awk -F"|"  '{ print $1 }'`
		let j=j+1
		printf "sqlite3 $db3fil \"update $tbl set bc='$bc' where rowid=$j\"\n"
		sqlite3 $db3fil "update $tbl set bc='$bc', bctxt='$bctxt', hostid='ka12' where rowid=$j"
		done
	sqlite3 $db3fil "select rowid,coffee_id,bc,bctxt from coffeeshop"


sqlite3 $db3fil  -header "select * from $tbl" > $tbl.psv
sqlite3 $db3fil  -header "select * from $tbl" | tr '|' ',' > $tbl.csv


exit;


printf "mp3s=$mp3s"
printf "mp4s=$mp4s"
printf "gifs=$gifs"
printf "bcs=$bcs"
printf "yts=$yts"