BEGIN;

-- Create new table buttontemplate
CREATE TABLE buttontemplate (
  id serial NOT NULL, 
  company integer NOT NULL DEFAULT 1, 
  name character varying(24) NOT NULL DEFAULT 'Default'::character varying, 
  status varchar(8) NOT NULL default 'active' CHECK (status IN ('active', 'deleted')),
  created timestamp with time zone NOT NULL DEFAULT now(), 
  lastupdate timestamp with time zone NOT NULL DEFAULT now(),
  PRIMARY KEY  (id)
);
INSERT INTO buttontemplate (company, name) VALUES ('1', 'Default');

GRANT UPDATE, SELECT, INSERT, DELETE ON buttontemplate TO astium;

GRANT UPDATE, SELECT, INSERT, DELETE ON buttontemplate_id_seq TO astium;

-- Add template column to phonetypebutton table
ALTER TABLE phonetypebutton ADD COLUMN buttontemplate integer NOT NULL DEFAULT 1;

-- Add columns to contact and phone referring to buttontemplate
ALTER TABLE contact ADD COLUMN buttontemplate integer NOT NULL DEFAULT 1;
ALTER TABLE phone ADD COLUMN buttontemplate integer NOT NULL DEFAULT 1;

-- Add columns to phonetype for function keys
ALTER TABLE phonetype ADD COLUMN fkey_lcd character varying(3) DEFAULT 'no'::character varying;
ALTER TABLE phonetype ADD COLUMN fkey_fixed character varying(3) DEFAULT 'no'::character varying;

-- fill 'yes' and 'no' for various phone types
update phonetype set fkey_fixed = 'yes' where id > 1;
update phonetype set fkey_lcd = 'yes', fkey_fixed = 'no' where id in(50, 54, 51, 55, 52, 56, 57, 58, 59, 5000, 5001, 5004, 5005, 5006, 5007, 5008, 5009, 90, 94, 95, 99);
update phonetype set fkey_fixed = 'yes' where id in(94);
update phonetype set fkey_fixed = 'no' where id in (20,40,53,60,61,63,64,70,80,81,82,83,84,100,101,102,120,130,143,145,146,147,150,151,160,180,200,240,2001,2002,2003,5002,5003);

-- Set constraints
ALTER TABLE buttontemplate ADD CONSTRAINT buttontemplate_company_constraint FOREIGN KEY (company) REFERENCES company (id);

ALTER TABLE contact ADD CONSTRAINT contact_buttontemplate_constraint FOREIGN KEY (buttontemplate) REFERENCES buttontemplate (id);

ALTER TABLE phone ADD CONSTRAINT phone_buttontemplate_constraint FOREIGN KEY (buttontemplate) REFERENCES buttontemplate (id);

-- Create new index
DROP INDEX phonetypebutton_company_phonetype_button_index;
CREATE UNIQUE INDEX phonetypebutton_company_phonetype_buttontemplate_index ON phonetypebutton (company, phonetype, button, buttontemplate);

COMMIT;