--
-- Table: basic
--
DROP TABLE basic CASCADE;
CREATE TABLE basic (
  type_id integer NOT NULL,
  pack_code character(1) NOT NULL,
  length_bits integer NOT NULL,
  PRIMARY KEY (type_id)
);



--
-- Table: encoded_string
--
DROP TABLE encoded_string CASCADE;
CREATE TABLE encoded_string (
  type_id integer NOT NULL,
  base_type_id integer NOT NULL,
  encoding character varying(20) NOT NULL,
  PRIMARY KEY (type_id)
);
CREATE INDEX encoded_string_idx_base_type_id on encoded_string (base_type_id);


--
-- Table: enum
--
DROP TABLE enum CASCADE;
CREATE TABLE enum (
  type_id integer NOT NULL,
  base_type_id integer NOT NULL,
  PRIMARY KEY (type_id)
);
CREATE INDEX enum_idx_base_type_id on enum (base_type_id);


--
-- Table: fileformats
--
DROP TABLE fileformats CASCADE;
CREATE TABLE fileformats (
  type_id integer NOT NULL,
  base_type_id integer NOT NULL,
  PRIMARY KEY (type_id)
);
CREATE INDEX fileformats_idx_base_type_id on fileformats (base_type_id);


--
-- Table: recurring_count
--
DROP TABLE recurring_count CASCADE;
CREATE TABLE recurring_count (
  type_id integer NOT NULL,
  base_type_id integer NOT NULL,
  count integer NOT NULL,
  PRIMARY KEY (type_id)
);
CREATE INDEX recurring_count_idx_base_type_id on recurring_count (base_type_id);


--
-- Table: recurring_terminated
--
DROP TABLE recurring_terminated CASCADE;
CREATE TABLE recurring_terminated (
  type_id integer NOT NULL,
  base_type_id integer NOT NULL,
  terminator character varying(1024) NOT NULL,
  PRIMARY KEY (type_id)
);
CREATE INDEX recurring_terminated_idx_base_type_id on recurring_terminated (base_type_id);


--
-- Table: structure
--
DROP TABLE structure CASCADE;
CREATE TABLE structure (
  type_id integer NOT NULL,
  PRIMARY KEY (type_id)
);



--
-- Table: structure_fields
--
DROP TABLE structure_fields CASCADE;
CREATE TABLE structure_fields (
  struct_id integer NOT NULL,
  field_id integer NOT NULL,
  name character varying(255) NOT NULL,
  position integer NOT NULL,
  type_id integer NOT NULL,
  value character varying(1024) NOT NULL,
  comments character varying(1024) NOT NULL,
  PRIMARY KEY (struct_id, field_id)
);
CREATE INDEX structure_fields_idx_struct_id on structure_fields (struct_id);;
CREATE INDEX structure_fields_idx_type_id on structure_fields (type_id);


--
-- Table: systems
--
DROP TABLE systems CASCADE;
CREATE TABLE systems (
  id serial NOT NULL,
  name character varying(255) NOT NULL,
  comments character varying(16384) NOT NULL,
  PRIMARY KEY (id)
);



--
-- Table: types
--
DROP TABLE types CASCADE;
CREATE TABLE types (
  id serial NOT NULL,
  system_id integer NOT NULL,
  name character varying(255) NOT NULL,
  comments character varying(16384),
  PRIMARY KEY (id)
);
CREATE INDEX types_idx_system_id on types (system_id);


--
-- Table: enum_values
--
DROP TABLE enum_values CASCADE;
CREATE TABLE enum_values (
  enum_id integer NOT NULL,
  value character varying(1024) NOT NULL,
  translation character varying(1024) NOT NULL,
  PRIMARY KEY (enum_id, value)
);
CREATE INDEX enum_values_idx_enum_id on enum_values (enum_id);
--
-- Foreign Key Definitions
--

ALTER TABLE encoded_string ADD FOREIGN KEY (base_type_id)
  REFERENCES types (id);

ALTER TABLE enum ADD FOREIGN KEY (base_type_id)
  REFERENCES types (id);

ALTER TABLE fileformats ADD FOREIGN KEY (base_type_id)
  REFERENCES types (id);

ALTER TABLE recurring_count ADD FOREIGN KEY (base_type_id)
  REFERENCES types (id);

ALTER TABLE recurring_terminated ADD FOREIGN KEY (base_type_id)
  REFERENCES types (id);

ALTER TABLE structure_fields ADD FOREIGN KEY (struct_id)
  REFERENCES types (id);

ALTER TABLE structure_fields ADD FOREIGN KEY (type_id)
  REFERENCES types (id);

ALTER TABLE types ADD FOREIGN KEY (system_id)
  REFERENCES systems (id) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE enum_values ADD FOREIGN KEY (enum_id)
  REFERENCES types (id);