require 'sqlite3'

dbname = ARGV.shift
db = SQLite3::Database.new dbname
db.execute_batch <<EOSQL
drop table if exists releases;
create table releases (
 id integer,
 groupid integer,
 artid integer
);
create index kiki on releases (groupid, artid);
create index kaka on releases (groupid, id);
EOSQL

def normalize(subj)
	subj.gsub(/\d+\/\d+/, '').gsub(/\.(part\d+.rar|(vol\d+\+\d+\.)?par2|nfo|sfv|nzb|\d\d\d|rar|r\d\d)[ "]/i, '')
end

db.execute('select id, name from groups order by name') { |groupid, groupname|
	next if ARGV.first and groupname !~ /#{ARGV.first}/
	puts " [+] searching releases in #{groupname}"
	cursubj = {}
	ns = nil
	cs = nil
	first = false
	stmt = db.prepare('insert into releases (id, groupid, artid) values (?, ?, ?)')
	db.execute('select id, subject from articles where groupid=? order by subject', groupid) { |id, subj|
		ns = normalize(subj)
		if cs = cursubj[ns.length] and cs.first == ns
			if first
				puts 'new   ' + subj
				stmt.execute cs.last, groupid, cs.last
				first = false
			end
			stmt.execute cs.last, groupid, id
		else
			cursubj[ns.length] = [ns, id]
			first = true
		end
	}
	stmt.close
#	db.execute('delete from releases where groupid=? and id in (select id from releases where groupid=? and count(*) < 3 group by id)', groupid, groupid)
	puts " [+] #{db.get_first_value 'select count(distinct id) from releases where groupid=?', groupid} releases found for #{groupname}"

}