-- Downgrades from version 0.4

-- pek: upgrades to 0.4 adds the 'changed' column, and adds the 'del' column to
--   INDEX ix_identities_user_id; the following chunk removes the 'changed' column,
--   and restores the INDEX ix_identities_user_id

-- pek: note 'del' column is missing here, because it is implied in the WHERE clause
CREATE TABLE temp_identities (
  identity_id integer NOT NULL PRIMARY KEY,
  user_id integer NOT NULL default '0',
  standard tinyint NOT NULL default '0',
  name varchar(128) NOT NULL default '',
  organization varchar(128) default '',
  email varchar(128) NOT NULL default '',
  "reply-to" varchar(128) NOT NULL default '',
  bcc varchar(128) NOT NULL default '',
  signature text NOT NULL default '',
  html_signature tinyint NOT NULL default '0'
);

INSERT INTO temp_identities (identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature)
  SELECT identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature
  FROM identities WHERE del=0;

DROP INDEX ix_identities_user_id;
DROP TABLE identities;

CREATE TABLE identities (
  identity_id integer NOT NULL PRIMARY KEY,
  user_id integer NOT NULL default '0',
  del tinyint NOT NULL default '0',
  standard tinyint NOT NULL default '0',
  name varchar(128) NOT NULL default '',
  organization varchar(128) default '',
  email varchar(128) NOT NULL default '',
  "reply-to" varchar(128) NOT NULL default '',
  bcc varchar(128) NOT NULL default '',
  signature text NOT NULL default '',
  html_signature tinyint NOT NULL default '0'
);

CREATE INDEX ix_identities_user_id ON identities(user_id);

INSERT INTO identities (identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature)
  SELECT identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature
  FROM temp_identities;

DROP TABLE temp_identities;


DROP TABLE contactgroups;


-------------------

-- pek: upgrades to 0.4 removes the default for 'last_login'; this restores the timestamp default
CREATE TABLE tmp_users (
  user_id integer NOT NULL PRIMARY KEY,
  username varchar(128) NOT NULL default '',
  mail_host varchar(128) NOT NULL default '',
  alias varchar(128) NOT NULL default '',
  created datetime NOT NULL default '0000-00-00 00:00:00',
  last_login datetime NOT NULL default '0000-00-00 00:00:00',
  language varchar(5),
  preferences text NOT NULL default ''
);

-- pek: last_login = NULL did not work, but IS does
UPDATE users SET last_login='0000-00-00 00:00:00' WHERE last_login IS NULL;

INSERT INTO tmp_users (user_id, username, mail_host, alias, created, last_login, language, preferences)
    SELECT user_id, username, mail_host, alias, created, last_login, language, preferences FROM users;

DROP TABLE users;

CREATE TABLE users (
  user_id integer NOT NULL PRIMARY KEY,
  username varchar(128) NOT NULL default '',
  mail_host varchar(128) NOT NULL default '',
  alias varchar(128) NOT NULL default '',
  created datetime NOT NULL default '0000-00-00 00:00:00',
  last_login datetime NOT NULL default '0000-00-00 00:00:00',
  language varchar(5),
  preferences text NOT NULL default ''
);

INSERT INTO users (user_id, username, mail_host, alias, created, last_login, language, preferences)
    SELECT user_id, username, mail_host, alias, created, last_login, language, preferences FROM tmp_users;

CREATE INDEX ix_users_username ON users(username);
CREATE INDEX ix_users_alias ON users(alias);
DROP TABLE tmp_users;
