import sys

MINORS = 1

DB_SERIES = [
  (1300, 1310),
  (1310, 1311),
  (1311, 1312),
  (1312, 1313),
 ]

DB_CHANGES = {
  (1300, 1310): [ """ALTER TABLE efolders ADD eaccount_id integer""",
                  """ALTER TABLE emails ADD eaccount_id integer""",
                  """CREATE INDEX efolders_account_ndx ON efolders ( eaccount_id )""",
                  """CREATE TABLE imap_emails ( folder_id int4 NOT NULL, imap_uid int4 NOT NULL, conflux_id int4 not null )""",
                  """ALTER TABLE ONLY imap_emails ADD CONSTRAINT imap_emails_pkey PRIMARY KEY (folder_id, imap_uid, conflux_id)""",
                  """CREATE INDEX imap_emails_ndx ON imap_emails ( conflux_id )""",
                  """CREATE INDEX todos_workflow_ndx ON todos ( workflow_id )""",
                  """ALTER TABLE events add master_id integer""",
                  """CREATE INDEX event_master_ndx ON events ( master_id )""",
                  """ALTER TABLE emails ADD email_to_addr text""",
                  """CREATE SEQUENCE custom_field_seq""",
                  """CREATE TABLE custom_fields (field_id integer, toporg_id integer,item_type_id integer,field_name text,priority integer, type integer,size integer)""",
                  """ALTER TABLE custom_fields ALTER field_id SET NOT NULL""",
                  """ALTER TABLE custom_fields ALTER field_id SET DEFAULT nextval('custom_field_seq'::text)""",
                  """CREATE TABLE custom_rows (item_id integer not null,field_id integer not null,contents text)""",
                  """ALTER TABLE custom_fields ADD CONSTRAINT custom_fields_pkey PRIMARY KEY (field_id)""",
                  """ALTER TABLE custom_rows ADD CONSTRAINT custom_rows_pkey PRIMARY KEY (item_id, field_id)""",
                  """CREATE INDEX custom_fields_ndx ON custom_fields ( toporg_id, item_type_id )""",
                  """ALTER TABLE ONLY custom_rows ADD CONSTRAINT "$1" FOREIGN KEY (item_id) REFERENCES items(item_id) ON UPDATE RESTRICT ON DELETE CASCADE""",
                  """ALTER TABLE ONLY custom_rows ADD CONSTRAINT "$2" FOREIGN KEY (field_id) REFERENCES custom_fields(field_id) ON UPDATE RESTRICT ON DELETE CASCADE""",
                  """ALTER TABLE ONLY custom_fields ADD CONSTRAINT "$1" FOREIGN KEY (toporg_id) REFERENCES items(item_id) ON UPDATE RESTRICT ON DELETE CASCADE""",
                  """ALTER TABLE persons ADD email_notify2 integer""",
                  """ALTER TABLE persons ALTER email_notify2 SET DEFAULT 0""",
                  """UPDATE persons SET email_notify2=0""",
                  """ALTER table persons ALTER email_notify2 SET NOT NULL""",
                  """ALTER TABLE eaccounts ADD eaccount_sendername text""",
                  """ALTER TABLE eaccounts ADD eaccount_sendermail text""",
                  """UPDATE items SET is_private=true WHERE item_type_id=9""",
                  """UPDATE events SET master_id=1 WHERE repeat_type IS NOT NULL""",
                  """INSERT INTO settings values ('conflux_version', '1.3.1', 0, null)""",
                ],
  (1310, 1311): [ """LOCK TABLE emails""",
                  """SELECT email_id, email_contents AS email_base64 INTO TABLE email_contents FROM emails""",
                  """ALTER TABLE email_contents ALTER email_id SET NOT NULL""",
                  """ALTER TABLE email_contents ADD CONSTRAINT email_contents_pkey PRIMARY KEY (email_id)""",
                  """ALTER TABLE emails DROP email_contents""",
                  """UPDATE settings SET value='1.3.1.1' WHERE setting_key='conflux_version'""",
                ],
  (1311, 1312): [ """ALTER TABLE ONLY email_contents ADD CONSTRAINT "$1" FOREIGN KEY (email_id) REFERENCES items(item_id) ON UPDATE RESTRICT ON DELETE CASCADE """,
                  """UPDATE settings SET value='1.3.1.2' WHERE setting_key='conflux_version'""",
                ],
  (1312, 1313): [ """CREATE INDEX emails_account_ndx ON emails ( eaccount_id, email_message_id )""",
                  """UPDATE settings SET value='1.3.1.3' WHERE setting_key='conflux_version'""",
                ],
 }

import _Baselib, _Utils
REQUEST = _Baselib.Request(None)

REQUEST.db.execute("""SELECT value FROM settings WHERE setting_key='conflux_version' AND treepos is null""")
res = REQUEST.db.fetchone()
if not res: # Conflux older than 1.3.1
    # This depends on pg version, 7.3 didnt have schema
    if REQUEST.db.version == 730:
        REQUEST.db.execute("""SELECT count(*) FROM pg_attribute, pg_class WHERE pg_attribute.attrelid=pg_class.relfilenode AND pg_attribute.attname='relation_flags' AND pg_class.relname='item_rels'""")
    else:
        REQUEST.db.execute("""SELECT count(*) FROM information_schema.columns WHERE table_name='item_rels' AND column_name='relation_flags'""")
    res = REQUEST.db.fetchone()[0]
    if res == 0:
        print "Automatic atabase schema upgrading cannot be performed. Current Conflux database version is lower than 1.3.0"
        sys.exit(1)
    version = 1300
else:
    if res[0].count('.') == 3:
        stable, major, minor, build = map(int, res[0].split('.'))
    else:
        stable, major, minor = map(int, res[0].split('.'))
        build = 0
    version = stable*1000+major*100+minor*10+build
REQUEST.db.rollback()

last_successful = version
updated = 0
for version_change in DB_SERIES:
    SQLS = DB_CHANGES[version_change]
    if version_change[0] < version:
        continue
    if not MINORS and (version_change[0] % 10) <> 0:
        continue
    v_old = ".".join(map(lambda x: x, str(version_change[0])))
    v_new = ".".join(map(lambda x: x, str(version_change[1])))
    print "Updating from version %s to %s..." % (v_old, v_new),
    sys.stdout.flush()
    try:
        REQUEST.db.execute("""BEGIN;""")
        for SQL in SQLS:
            REQUEST.db.execute(SQL)
        REQUEST.db.commit()
        print
    except:
        print
        TB = _Utils.get_tb()
        REQUEST.db.rollback()
        REQUEST.db.close()
        print TB
        print "Error occured, exiting."
        if version == last_successful:
            print "Database schema remained at version %s." % ".".join(map(lambda x: x, str(last_successful)))
        else:
            print "Database schema was upgraded to version %s." % ".".join(map(lambda x: x, str(last_successful)))
        sys.exit(1)
    last_successful = version_change[1]
    updated = 1

REQUEST.db.close()
if updated:
    print "Database schema was upgraded to version %s." % ".".join(map(lambda x: x, str(last_successful)))
else:
    print "Database schema remained at version %s." % ".".join(map(lambda x: x, str(last_successful)))
sys.exit(0)