--
-- PostgreSQL database dump
--

SET client_encoding = 'UTF8';
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- Name: equipment; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA equipment;


SET search_path = equipment, pg_catalog;

SET default_tablespace = 'equipment';

SET default_with_oids = false;

--
-- Name: audit_trail; Type: TABLE; Schema: equipment; Owner: postgres; Tablespace: 
--

CREATE TABLE audit_trail (
    audit_id serial NOT NULL,
    ts timestamp with time zone DEFAULT now() NOT NULL,
    event_type smallint DEFAULT 1 NOT NULL,
    user_id integer NOT NULL,
    item_id integer,
    new_state_id integer,
    owner_id integer,
    notes character varying(80)
);

SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('audit_trail', 'audit_id'), 33, true);

ALTER TABLE ONLY audit_trail
    ADD CONSTRAINT audit_trail_pkey PRIMARY KEY (audit_id);

CREATE TABLE items (
    item_id serial NOT NULL,
    type_id integer NOT NULL,
    size_id integer NOT NULL,
    visual_tag character varying(10) NOT NULL,
    state_id integer NOT NULL
);

SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('items', 'item_id'), 45, true);

ALTER TABLE ONLY items
    ADD CONSTRAINT items_pkey PRIMARY KEY (item_id);

ALTER TABLE ONLY items
    ADD CONSTRAINT items_type_id_key UNIQUE (type_id, size_id, visual_tag);


--
-- Name: states; Type: TABLE; Schema: equipment; Owner: postgres; Tablespace: 
--

CREATE TABLE states (
    state_id serial NOT NULL,
    short_desc character varying(5),
    long_desc character varying(25) NOT NULL,
    terminating boolean DEFAULT false NOT NULL,
    "valid" boolean DEFAULT true NOT NULL,
    report boolean DEFAULT false
);

SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('states', 'state_id'), 1, false);

ALTER TABLE ONLY states
    ADD CONSTRAINT new_states_pkey PRIMARY KEY (state_id);


INSERT INTO audit_trail (audit_id, ts, event_type, user_id, item_id, new_state_id, owner_id, notes) VALUES (23, '2006-04-23 22:33:10.443+02', 1, 1, 37, 5, 19, NULL);
INSERT INTO audit_trail (audit_id, ts, event_type, user_id, item_id, new_state_id, owner_id, notes) VALUES (24, '2006-04-23 22:33:10.443+02', 1, 1, 41, 5, 19, NULL);
INSERT INTO audit_trail (audit_id, ts, event_type, user_id, item_id, new_state_id, owner_id, notes) VALUES (25, '2006-04-23 22:33:10.443+02', 1, 1, 22, 5, 19, NULL);
INSERT INTO audit_trail (audit_id, ts, event_type, user_id, item_id, new_state_id, owner_id, notes) VALUES (26, '2006-04-23 22:33:10.443+02', 1, 1, 25, 5, 19, NULL);
INSERT INTO audit_trail (audit_id, ts, event_type, user_id, item_id, new_state_id, owner_id, notes) VALUES (27, '2006-04-25 20:17:14.224+02', 1, 1, 32, 5, 19, NULL);
INSERT INTO audit_trail (audit_id, ts, event_type, user_id, item_id, new_state_id, owner_id, notes) VALUES (28, '2006-04-25 21:38:58.055+02', 1, 1, 15, 5, 19, NULL);
INSERT INTO audit_trail (audit_id, ts, event_type, user_id, item_id, new_state_id, owner_id, notes) VALUES (29, '2006-04-25 21:56:01.327+02', 1, 1, 15, 4, 19, NULL);
INSERT INTO audit_trail (audit_id, ts, event_type, user_id, item_id, new_state_id, owner_id, notes) VALUES (30, '2006-04-26 13:18:07.141+02', 1, 1, 37, 4, 19, NULL);
INSERT INTO audit_trail (audit_id, ts, event_type, user_id, item_id, new_state_id, owner_id, notes) VALUES (31, '2006-04-26 15:00:37.776+02', 1, 1, 15, 4, 19, NULL);
INSERT INTO audit_trail (audit_id, ts, event_type, user_id, item_id, new_state_id, owner_id, notes) VALUES (32, '2006-04-26 15:01:32.985+02', 1, 1, 15, 4, 19, NULL);
INSERT INTO audit_trail (audit_id, ts, event_type, user_id, item_id, new_state_id, owner_id, notes) VALUES (33, '2006-04-26 15:05:55.763+02', 1, 1, 15, 4, 19, NULL);

--
-- Data for Name: items; Type: TABLE DATA; Schema: equipment; Owner: postgres
--

INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (18, 4, 13, 'MK8', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (19, 4, 13, 'MK9', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (14, 4, 13, 'MK4', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (10, 4, 11, 'lkkkk', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (20, 4, 13, 'MK10', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (16, 4, 13, 'MK6', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (11, 4, 13, 'MK1', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (12, 4, 13, 'MK2', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (17, 4, 13, 'MK7', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (13, 4, 13, 'MK3', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (23, 4, 12, 'KM22', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (24, 4, 12, 'KM23', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (27, 4, 12, 'KM26', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (28, 4, 12, 'KM27', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (29, 4, 12, 'KM28', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (30, 4, 12, 'KM29', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (31, 6, 25, 'DPV5', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (33, 6, 25, 'DPV7', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (34, 6, 25, 'DPV8', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (35, 6, 25, 'DPV9', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (38, 6, 25, 'DPV12', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (39, 6, 25, 'DPV13', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (40, 6, 25, 'DPV14', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (42, 6, 25, 'DPV124', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (43, 6, 25, 'DPV125', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (44, 6, 25, 'DPV126', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (45, 6, 25, 'DPV127', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (8, 4, 13, 'ffff', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (26, 4, 12, 'KM25', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (9, 4, 12, 'dddd', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (36, 6, 25, 'DPV10', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (21, 4, 12, 'KM20', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (41, 6, 25, 'DPV123', 5);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (25, 4, 12, 'KM24', 5);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (22, 4, 12, 'KM21', 5);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (32, 6, 25, 'DPV6', 5);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (15, 4, 13, 'MK5', 4);
INSERT INTO items (item_id, type_id, size_id, visual_tag, state_id) VALUES (37, 6, 25, 'DPV11', 5);

--
-- Data for Name: states; Type: TABLE DATA; Schema: equipment; Owner: postgres
--

INSERT INTO states (state_id, short_desc, long_desc, terminating, "valid", report) VALUES (4, 'AVAIL', 'Available', false, true, true);
INSERT INTO states (state_id, short_desc, long_desc, terminating, "valid", report) VALUES (5, 'OUT', 'Checked Out', false, true, true);
INSERT INTO states (state_id, short_desc, long_desc, terminating, "valid", report) VALUES (6, 'LOST', 'Lost', true, true, true);



ALTER TABLE ONLY audit_trail
    ADD CONSTRAINT audit_trail_item_id_fkey FOREIGN KEY (item_id) REFERENCES items(item_id);


ALTER TABLE ONLY audit_trail
    ADD CONSTRAINT audit_trail_new_state_id_fkey FOREIGN KEY (new_state_id) REFERENCES states(state_id) ON DELETE CASCADE;


ALTER TABLE ONLY items
    ADD CONSTRAINT items_state_id_fkey FOREIGN KEY (state_id) REFERENCES states(state_id) ON DELETE CASCADE;

