--- Put the timezone into contact for the user
--- And, while we are at, change all times and timestamps to be with time zone!
CREATE UNIQUE INDEX zonetab_timezone_index ON zonetab (timezone);
BEGIN;
alter table activate alter column created type timestamp with time zone;
alter table activate alter column process type timestamp with time zone;
alter table activate alter column processed type timestamp with time zone;
alter table asterisk_voicemail_users alter column stamp type timestamp with time zone;
alter table conference alter column lastused type timestamp with time zone;
alter table conference_log alter column started type timestamp with time zone;
alter table conference_log alter column ended type timestamp with time zone;
alter table history alter column created type timestamp with time zone;
alter table host alter column starts type timestamp with time zone;
alter table lease alter column starts type timestamp with time zone;
alter table lease alter column ends type timestamp with time zone;
alter table phone alter column starts type timestamp with time zone;
alter table schedule alter column starttime type time with time zone;
alter table schedule alter column endtime type time with time zone;
alter table sessions alter column last_at type timestamp with time zone;
alter table voicemail alter column accessed type timestamp with time zone;
alter table contact add column timezone character varying(40);
update contact set timezone = 'UTC' where timezone is NULL;
alter table contact alter column timezone set default 'UTC';
alter table contact alter column timezone set NOT NULL;
alter table contact add constraint zonetab_timezone_constraint FOREIGN KEY(timezone) REFERENCES zonetab(timezone);
drop view v_my_account;
alter table contact alter column lastfailedlogin type timestamp with time zone;
alter table contact alter column startdate type timestamp with time zone;
alter table contact alter column enddate type timestamp with time zone;
CREATE VIEW v_my_account AS SELECT * FROM contact WHERE contact.username::name = "current_user"();
GRANT SELECT, UPDATE ON v_my_account TO GROUP astium_users;
CREATE or REPLACE FUNCTION system_get_timezone() RETURNS varchar AS '
  DECLARE
    _systz  varchar;
  BEGIN
   SELECT INTO _systz current_setting(''Timezone'');
   RETURN _systz;
  END;
' LANGUAGE plpgsql;
CREATE or REPLACE FUNCTION contact_get_timezone(integer) RETURNS varchar AS '
  DECLARE
    _usertz  varchar;
  BEGIN
   SELECT INTO _usertz timezone from contact where id=$1;
   RETURN _usertz;
  END;
' LANGUAGE plpgsql;
COMMIT;

