BEGIN;

ALTER TABLE ap ADD PRIMARY KEY (id);

ALTER TABLE ar ADD PRIMARY KEY (id);

ALTER TABLE assembly ADD PRIMARY KEY (id, parts_id);

ALTER TABLE business ADD PRIMARY KEY (id);

ALTER TABLE customer ADD PRIMARY KEY (id);

ALTER TABLE customertax ADD PRIMARY KEY (customer_id, chart_id);

ALTER TABLE department ADD PRIMARY KEY (id);

ALTER TABLE dpt_trans ADD PRIMARY KEY (trans_id);

ALTER TABLE employee ADD PRIMARY KEY (id);

ALTER TABLE exchangerate ADD PRIMARY KEY (curr, transdate);

ALTER TABLE gifi ADD PRIMARY KEY (accno);

ALTER TABLE gl ADD PRIMARY KEY (id);

ALTER TABLE invoice ADD PRIMARY KEY (id);

ALTER TABLE jcitems ADD PRIMARY KEY (id);

ALTER TABLE language ADD PRIMARY KEY (code);

ALTER TABLE makemodel ADD PRIMARY KEY (parts_id);

ALTER TABLE oe ADD PRIMARY KEY (id);

ALTER TABLE orderitems ADD PRIMARY KEY (id);

ALTER TABLE parts ADD PRIMARY KEY (id);

ALTER TABLE partsgroup ADD PRIMARY KEY (id);

ALTER TABLE partstax ADD PRIMARY KEY (parts_id, chart_id);
ALTER TABLE partstax ADD FOREIGN KEY (chart_id) REFERENCES chart (id);
ALTER TABLE partstax ADD FOREIGN KEY (parts_id) REFERENCES parts (id);

ALTER TABLE pricegroup ADD PRIMARY KEY (id);

ALTER TABLE project ADD PRIMARY KEY (id);

ALTER TABLE recurringemail ADD PRIMARY KEY (id);

ALTER TABLE recurring ADD PRIMARY KEY (id);

ALTER TABLE recurringprint ADD PRIMARY KEY (id);

ALTER TABLE sic ADD PRIMARY KEY (code);

ALTER TABLE status ADD PRIMARY KEY (trans_id);

-- Removing the primary key below since this is not quite the best way
-- do this.  The tax table could have multiple rows per chart_id.
-- We need a better fix for 1.3 (perhaps changing date to timestamp and
-- and defaulting to infinity.
-- ALTER TABLE tax ADD PRIMARY KEY (chart_id);
-- ALTER TABLE tax ADD FOREIGN KEY (chart_id) REFERENCES chart (id);

ALTER TABLE translation ADD PRIMARY KEY (trans_id, language_code);

ALTER TABLE vendor ADD PRIMARY KEY (id);

ALTER TABLE vendortax ADD PRIMARY KEY (vendor_id, chart_id);

ALTER TABLE warehouse ADD PRIMARY KEY (id);

ALTER TABLE yearend ADD PRIMARY KEY (trans_id);

LOCK inventory in EXCLUSIVE mode;
ALTER TABLE inventory ADD COLUMN entry_id bigint;
CREATE SEQUENCE inventory_entry_id_seq;

ALTER TABLE inventory ALTER COLUMN entry_id 
SET DEFAULT nextval('inventory_entry_id_seq');

UPDATE inventory SET entry_id = nextval('inventory_entry_id_seq');
ALTER TABLE inventory ADD PRIMARY key (entry_id);

LOCK partscustomer IN EXCLUSIVE MODE;
ALTER TABLE partscustomer ADD COLUMN entry_id int;
CREATE SEQUENCE partscustomer_entry_id_seq;

ALTER TABLE partscustomer ALTER COLUMN entry_id 
SET DEFAULT nextval('partscustomer_entry_id_seq');

UPDATE partscustomer SET entry_id = nextval('partscustomer_entry_id_seq');
ALTER TABLE partscustomer ADD PRIMARY KEY (entry_id);

LOCK partsvendor IN EXCLUSIVE MODE;
ALTER TABLE partsvendor ADD COLUMN entry_id int;
CREATE SEQUENCE partsvendor_entry_id_seq;

ALTER TABLE partsvendor ALTER COLUMN entry_id 
SET DEFAULT nextval('partsvendor_entry_id_seq');

UPDATE partsvendor SET entry_id = nextval('partsvendor_entry_id_seq');
ALTER TABLE partsvendor ADD PRIMARY KEY (entry_id);

LOCK audittrail IN EXCLUSIVE MODE;
ALTER TABLE audittrail ADD COLUMN entry_id int;
CREATE SEQUENCE audittrail_entry_id_seq ;

ALTER TABLE audittrail ALTER COLUMN entry_id 
SET DEFAULT nextval('audittrail_entry_id_seq');

UPDATE audittrail SET entry_id = nextval('audittrail_entry_id_seq');
ALTER TABLE audittrail ADD PRIMARY KEY (entry_id);

LOCK shipto IN EXCLUSIVE MODE;
ALTER TABLE shipto ADD COLUMN entry_id int;
CREATE SEQUENCE shipto_entry_id_seq ;

ALTER TABLE shipto ALTER COLUMN entry_id 
SET DEFAULT nextval('shipto_entry_id_seq');

UPDATE shipto SET entry_id = nextval('shipto_entry_id_seq');
ALTER TABLE shipto ADD PRIMARY KEY (entry_id);

CREATE TABLE taxmodule (
  taxmodule_id serial PRIMARY KEY,
  taxmodulename text NOT NULL
);

INSERT INTO taxmodule (
  taxmodule_id, taxmodulename
  ) VALUES (
  1, 'Simple'
);

CREATE TABLE taxcategory (
  taxcategory_id serial PRIMARY KEY,
  taxcategoryname text NOT NULL,
  taxmodule_id int NOT NULL REFERENCES taxmodule (taxmodule_id)
);

ALTER TABLE partstax ADD COLUMN taxcategory_id int REFERENCES taxcategory (taxcategory_id);

LOCK tax IN EXCLUSIVE MODE;
ALTER TABLE tax ADD COLUMN pass int DEFAULT 0;
UPDATE tax SET pass = 0;
ALTER TABLE tax ALTER COLUMN pass SET NOT NULL;

ALTER TABLE tax ADD COLUMN taxmodule_id int REFERENCES taxmodule DEFAULT 1;
UPDATE tax SET taxmodule_id = 1;
ALTER TABLE tax ALTER COLUMN taxmodule_id SET NOT NULL;

-- Fixed session table and add users table --
--CREATE TABLE users (id serial UNIQUE, username varchar(30) PRIMARY KEY);
COMMENT ON TABLE users IS 'username is the primary key because we don\'t want duplicate users';
--CREATE TABLE users_conf(id integer primary key references users(id) deferrable initially deferred,
--                        acs text,
--                        address text,
--                        businessnumber text,
--                        company text,
--                        countrycode text,
--                        currency text,
--                        dateformat text,
--                        dbconnect text,
--                        dbdriver text default 'Pg',
--                        dbhost text default 'localhost',
--                        dbname text,
--                        dboptions text,
--                        dbpasswd text,
--                        dbport text,
--                        dbuser text,
--                        email text,
--                        fax text,
--                        menuwidth text,
--                        name text,
--                        numberformat text,
--                        password varchar(32) check(length(password) = 32),
--                        print text,
--                        printer text,
--                        role text,
--                        sid text,
--                        signature text,
--                        stylesheet text,
--                        tel text,
--                        templates text,
--          crypted_password text,
--                        timeout numeric,
--                        vclimit numeric);
COMMENT ON TABLE users_conf IS 'This is a completely dumb table that is a place holder to get usersconf into the database. Next major release will have a much more sane implementation';
COMMENT ON COLUMN users_conf.id IS 'Yes primary key with a FOREIGN KEY to users(id) is correct';
COMMENT ON COLUMN users_conf.password IS 'This means we have to get rid of the current password stuff and move to presumably md5()';

--LOCK session in EXCLUSIVE MODE;
--DELETE FROM session;
--ALTER TABLE session ADD CONSTRAINT session_token_check check (length(token::text) = 32);
--ALTER TABLE session ADD column user_id integer not null references users(id);

-- comment this out when user db is working:
--ALTER TABLE session ALTER COLUMN user_id DROP NOT NULL;

-- Admin user --
--INSERT INTO users(username) VALUES ('admin');
--INSERT INTO users_conf(id,password) VALUES (currval('users_id_seq'),NULL);

-- Functions

--CREATE FUNCTION create_user(text) RETURNS bigint AS $$
--   INSERT INTO users(username) VALUES ('$1');
--   SELECT currval('users_id_seq');
--   $$ LANGUAGE 'SQL';

COMMENT ON FUNCTION create_user(text) IS $$ Function to create user Returns users.id if successful, else it is an error. $$;

--CREATE FUNCTION update_user(int4,text) RETURNS int4 AS $$
--   UPDATE users SET username = '$2' WHERE id = $1;
--   SELECT 1;
--   $$ LANGUAGE 'SQL';

COMMENT ON FUNCTION update_user(int4,text) IS $$ Takes int4 which is users.id and text which is username. Will update username based on id. Username is unique $$;

ALTER TABLE defaults RENAME TO old_defaults;

CREATE TABLE defaults (
    setting_key TEXT PRIMARY KEY,
    value TEXT
);