package nabble.model;

import fschmidt.db.DbDatabase;
import jdbcpgbackup.ZipBackup;
import nabble.utils.Log4j;

import java.io.File;
import java.sql.SQLException;
import java.util.Collections;


public final class DbGlobalUpdater {

	private static final int version = 15;

	private static final boolean skipSlowSteps = Init.get("skipSlowSteps",false);
	private static final File globalSchemaFile;
	static {
		String homeDir = (String)Init.get("home_dir");
		globalSchemaFile = new File(homeDir+"src/nabble/data/global.schema");
	}

	private static final String GLOBAL = "global";

	private final DbUpdater up;

	private DbGlobalUpdater(DbDatabase dbGlobal) {
		this.up = new DbUpdater(dbGlobal,GLOBAL);
	}

	private void go() throws SQLException {
		try {
			update();
			if( up.databaseVersion() != version )
				throw new RuntimeException();
		} finally {
			up.close();
		}
	}

	public static boolean isValid() {
		ZipBackup backup = new ZipBackup( globalSchemaFile, Db.completeUrl );
		return backup.compareTo(GLOBAL);
	}

	public static void backupSchema(String filename) {
		ZipBackup backup = new ZipBackup( new File(filename), Db.completeUrl );
		backup.dump( Collections.singleton(GLOBAL), SiteImpl.SCHEMA_DATA );
	}
//	in beanshell, I do:
//	DbGlobalUpdater.backupSchema("/Users/Franklin/hg/nabble/src/nabble/data/global.schema")

	public static void updateGlobal() throws SQLException {
		DbDatabase dbGlobal;
		try {
			dbGlobal = Db.dbGlobal();
		} catch(Db.NoSchema e) {
			if( !e.getMessage().equals(GLOBAL) )
				throw e;
			DbDatabase db = Db.dbPostgres();
			db.beginTransaction();
			try {
				DbSiteCreator.restoreSchema( globalSchemaFile, GLOBAL );
				db.commitTransaction();
			} finally {
				db.endTransaction();
			}
			dbGlobal = Db.dbGlobal();
		}
		new DbGlobalUpdater(dbGlobal).go();
	}

	public static void main(String[] args) throws Exception {
		Log4j.initForConsole();
		updateGlobal();
	}

	void execStepAsPostgres(String sql)
		throws SQLException
	{
		up.begin();
		up.exec( 
		"\r\n			set role "
		+(Db.user)
		+"\r\n		"
 );
		up.exec(sql);
		up.exec( 
		"\r\n			set role global\r\n		"
 );
		up.commit();
	}

	void update() throws SQLException {
		switch( up.dbVersion() ) {
		case 0:
			up.execStep( 
		"\r\n				insert into site_global (site_id)\r\n					select site_id from site\r\n						where not exists (select * from site_global where site_global.site_id=site.site_id)\r\n			"
 );
		case 1:  // again
			up.execStep( 
		"\r\n				insert into site_global (site_id)\r\n					select site_id from site\r\n						where not exists (select * from site_global where site_global.site_id=site.site_id)\r\n			"
 );
		case 2:
			up.execStep( 
		"\r\n				alter table mailing_list_lookup\r\n					drop constraint mailing_list_lookup_site_id_fkey\r\n			"
 );
		case 3:
			up.execStep( 
		"\r\n				alter table mailing_list_lookup\r\n					ADD FOREIGN KEY (site_id) REFERENCES site_global(site_id) ON DELETE CASCADE\r\n			"
 );
		case 4:
			up.execStep( 
		"\r\n				alter table task\r\n					drop constraint task_site_id_fkey\r\n			"
 );
		case 5:
			up.execStep( 
		"\r\n				alter table task\r\n					ADD FOREIGN KEY (site_id) REFERENCES site_global(site_id) ON DELETE CASCADE\r\n			"
 );
		case 6:
			up.execStep(null);
		case 7:
			up.execStep(null);
		case 8:
			execStepAsPostgres( 
		"\r\n				alter schema public rename to pulic_old\r\n			"
 );
		case 9:
			up.execStep( 
		"\r\n				alter table global.site_global\r\n					add column remote_addr character varying;\r\n			"
 );
		case 10:
			up.execStep( 
		"\r\n				create table safety (\r\n					url character varying primary key,\r\n					is_safe boolean not null,\r\n					content text not null\r\n				)\r\n			"
 );
		case 11:
			up.execStep( 
		"\r\n				alter table site_global drop column owner_email\r\n			"
 );
		case 12:
			up.execStep( 
		"\r\n				delete from safety\r\n			"
 );
		case 13:
			up.execStep( 
		"\r\n				alter table site_global\r\n					add column user_views integer NOT NULL DEFAULT 0\r\n			"
 );
		case 14:
			up.execStep( 
		"\r\n				drop table safety\r\n			"
 );
		}
	}

}