#!/bin/bash



make_tz_table () {
	sqlite3 <<--

	-- Start with the official time zone rules that have applied
	-- since before MeFi started operating in 1999.

	create temp table tz_rules
		(start_year, tz, ends_before, adj);
	insert into tz_rules values
		(1987, '-08', '04-01 02:00', 'weekday 0'),
		(1967, '-07', '10-25 02:00', 'weekday 0'),
		(2007, '-08', '03-08 02:00', 'weekday 0'),
		(2007, '-07', '11-01 02:00', 'weekday 0');

	-- List all the years from 1999 to ten years from now.

	create temp table years (year);
	create trigger insert_previous_year before insert on years
	when new.year > 1999 begin
		insert into years select new.year - 1;
	end;
	pragma recursive_triggers=true;
	insert into years select strftime('%Y', 'now') + 10;

	-- Calculate all the official time zone transitions
	-- within those years.

	create temp table tz_transitions (tz, ends_before);
	insert into tz_transitions select
		tz,
		(
			select strftime('%Y-%m-%d %H:%M:%f', year||'-'||ends_before, adj)
			from (
				select * from tz_rules
				where tz = t.tz
				and   start_year <= year
				order by start_year desc limit 1
			)
		)
	from years, (select distinct tz from tz_rules) t;
			
	-- Forget those of the official transition times
	-- that the MeFi server paid no attention to.

	delete from tz_transitions where ends_before in (
		'2000-10-29 02:00:00.000',
		'2007-11-04 02:00:00.000',
		'2008-03-09 02:00:00.000',
		'2011-03-13 02:00:00.000');

	-- Add all the bogus ones it used instead/as well.

	insert into tz_transitions values
		('-07', '2000-10-29 02:07:30.000'),
		('-07', '2006-04-14 01:00:00.000'),
		('-05', '2006-04-14 03:15:00.000'),
		('-07', '2006-04-20 14:56:30.000'),
		('-05', '2006-04-20 18:07:00.000'),
		('-07', '2007-03-25 23:10:00.000'),
		('-06', '2007-03-26 04:16:31.040'),
		('-07', '2007-04-01 02:00:00.000'),
		('-06', '2007-04-01 21:48:00.000'),
		('-07', '2007-10-28 02:00:00.000'),
		('-08', '2007-10-28 10:06:53.590'),
		('-07', '2007-11-04 10:06:00.590'),
		('-08', '2008-03-09 12:33:51.983'),
		('-07', '2008-03-10 14:00:40.000'),
		('-06', '2008-03-10 15:01:00.000'),
		('-08', '2009-02-02 21:04:00.000'),
		('-06', '2009-02-03 01:00:00.000'),
		('-08', '2011-03-13 02:00:51.137');

	-- List the times when the clock went backwards.

	create temp table adjustments (at_time, jump_to);
	insert into adjustments values
		(null, ''),
		('2001-05-14 15:57:52.008', '2001-05-14 15:57:25.707'),
		('2002-12-10 12:38:09.634', '2002-12-10 12:37:55.157'),
		('2004-12-01 08:31:34.618', '2004-12-01 08:28:18.350'),
		('2004-12-15 08:27:29.171', '2004-12-15 08:27:16.860'),
		('2005-01-12 08:29:37.828', '2005-01-12 08:26:17.070'),
		('2005-02-09 08:43:19.874', '2005-02-09 08:40:22.060'),
		('2005-03-02 08:42:35.188', '2005-03-02 08:40:29.487'),
		('2005-03-30 08:43:50.321', '2005-03-30 08:41:11.777'),
		('2005-04-20 09:42:27.434', '2005-04-20 09:40:08.667'),
		('2005-07-13 09:40:01.281', '2005-07-13 09:39:55.083'),
		('2005-08-10 09:49:58.264', '2005-08-10 09:49:55.033'),
		('2006-04-22 07:45:52.831', '2006-04-22 07:43:28.797'),
		('2009-07-30 13:57:31.414', '2009-07-30 13:57:31.410'),
		('2009-12-31 09:49:11.111', '2009-12-31 09:49:11.107'),
		('2010-01-18 07:29:28.454', '2010-01-18 07:29:28.450'),
		('2012-07-09 11:26:57.438', '2012-07-09 11:26:57.430'),
		(x'ff', null);

	-- Transform transition tables to period tables.

	create temp table sorted_tz_transitions as
	select * from tz_transitions order by ends_before;

	create temp table tz_periods (tz text, starts_at, ends_before);
	insert into tz_periods select
		t2.tz,
		strftime('%Y-%m-%d %H:%M:%f', t1.ends_before, (t2.tz - t1.tz)||' hours'),
		t2.ends_before
	from sorted_tz_transitions t1 join sorted_tz_transitions t2
	on t2.rowid = t1.rowid + 1;

	create temp table adjustment_periods (starts_at, ends_before);
	insert into adjustment_periods select
		a1.jump_to,
		a2.at_time
	from adjustments a1 join adjustments a2
	on a2.rowid = a1.rowid + 1;

	-- Merge adjustment periods with timezone periods.

	create temp table periods (tz, starts_at, ends_before);
	insert into periods select
		tz,
		max(ap.starts_at, tp.starts_at),
		min(ap.ends_before, tp.ends_before)
	from tz_periods tp join adjustment_periods ap
	on ap.starts_at >= tp.starts_at and ap.starts_at < tp.ends_before
	or tp.starts_at >= ap.starts_at and tp.starts_at < ap.ends_before;
	
	-- Spit it out.

	.separator \t
	select * from periods order by ends_before;
	-
}



# Scan the specified comments file and build tables relating
# postid and commentid ranges to timezone.

scan () {
	awk -v site=$1 -v tzfile="$2" -f- \
		<(extract postdata_$1.txt.zip) \
		<(extract commentdata_$1.txt.zip) \
		<<-'-'

	BEGIN {
		OFS = FS = "\t"
		while (getline <tzfile) {
			tz[++periods] = $1
			starts_at[periods] = $2
			ends_before[periods] = $3
		}
	}

	FILENAME != prev_name {
		++file
		prev_name = FILENAME
		prev_tz = prev_time = ""
		prev_id = 0
		period = 1
	}

	file == 1 {
		while ($3 >= ends_before[period] || $3 >= starts_at[period+1] && $3 < prev_time) {
			prev_time = starts_at[++period]
		}

		while ($3 < starts_at[period] && $3 < ends_before[period+1]) {
			++period
		}

		while ($3 < starts_at[period] && $3 < ends_before[period-1]) {
			--period
		}

		post_user[$1] = $2
		post_time[$1] = $3
		post_tz[$1] = tz[period]

		if (tz[period] != prev_tz) {
			print $1, tz[period] >"post-tz-" site ".txt"
			prev_tz = tz[period]
		}

		# Anonymous AskMe posts are out of time sequence
		known_anomaly = \
			(site == "askme" && $2 == 17564)

		if (!known_anomaly) {
			flags = ""
			if ($1 < prev_id) {
				flags = flags "<"
			}
			if ($3 < prev_time) {
				flags = flags "P"
			}
			if ($3 < starts_at[period]) {
				flags = flags "!"
			}
			if (flags > "") {
				$3 = $3 tz[period]
				print $0, flags >"post-anomalies-" site ".txt"
			}
			prev_id = $1
			prev_time = $3
		}
	}

	file == 2 {
		while ( \
			$4 >= ends_before[period] || \
			$4 >= starts_at[period+1] && ($4 < prev_time || $4 < post_time[$2]) \
		) {
			prev_time = starts_at[++period]
		}

		while ($4 < starts_at[period] && $4 < ends_before[period+1]) {
			++period
		}

		while ($4 < starts_at[period] && $4 < ends_before[period-1]) {
			--period
		}

		if (tz[period] != prev_tz) {
			print $1, tz[period] >"comment-tz-" site ".txt"
			prev_tz = tz[period]
		}

		# AskMe suffered a data loss in 2010 and some comments got reconstructed
		# from a Google cache; anonymous posts are just generally screwed; a few
		# MeFi comments had timestamps mis-set by hand.
		known_anomaly =	\
			(site == "askme" && $4 ~ /^2010-01-08 ..:..:0[01]\.000$/) || \
			(site == "mefi" && $1 ~ /^(29816|8917[579]|143222)$/)

		if (!known_anomaly) {
			flags = ""
			if ($1 < prev_id) {
				flags = flags "<"
			}
			if ( \
				!(site == "askme" && post_user[$2] == 17564) && \
				tz[period] == post_tz[$2] && \
				$4 < post_time[$2] \
			) {
				flags = flags "P"
			}
			if ($4 < prev_time) {
				flags = flags "C"
			}
			if ($4 < starts_at[period]) {
				flags = flags "!"
			}
			if (flags > "") {
				$4 = $4 tz[period]
				print $0, flags >"comment-anomalies-" site ".txt"
			}
			prev_id = $1
			prev_time = $4
		}
	}
	-
}



# Unzip the specified infodump file, remove the header lines and clean it up
# for import or processing: convert DOS \r\n end-of-line markers to Unix \n
# markers and \r\r\n embedded linebreak markers to HTML <br> tags, then fix
# any quoting in the last field so that .import doesn't choke on unbalanced
# quotes.

extract () {
	unzip -p "$1" |
	tail -n +3 |
	sed ':1;/\r$/s///;/\r$/{N;s/\r\n/<br>/;b1;};/"/{s//""/g;s/[^\t]*$/"&"/;}'
}



wget -nv -N http://mefi.us/infodump/{post,comment}data_{askme,mefi,meta,music}.txt.zip

tz=tz-table.txt
make_tz_table >$tz
rm *-anomalies-*

for site in askme mefi meta music
do
	scan $site $tz
done

ftp -p ftp.nearlyfreespeech.net <<--
cd mefi-timestamps
prompt off
mdel *-anomalies-*.txt
binary
put $0
ascii
mput *.txt
bye
-