--Row count = 103
CREATE TABLE "parties"."party" (
  "party_id" SERIAL,
  "parent_party_id" INTEGER,
  "party_type" TEXT NOT NULL,
  "party_name" TEXT NOT NULL,
  "party_path" TEXT,
  "is_active" BOOLEAN DEFAULT true NOT NULL,
  "party_uses_spid" TEXT,
  CONSTRAINT "party_pk" PRIMARY KEY("party_id"),
  CONSTRAINT "no_self_parenting_ck" CHECK (party_id <> parent_party_id),
  CONSTRAINT "party_id_ck" CHECK (party_id > 0),
  CONSTRAINT "party_type_ck" CHECK ((((party_type = 'C'::text) OR (party_type = 'R'::text)) OR (party_type = 'S'::text)) OR (party_type = 'W'::text)),
  CONSTRAINT "party_parent_party_id_fk" FOREIGN KEY ("parent_party_id")
    REFERENCES "parties"."party"("party_id")
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE
) WITH OIDS;

CREATE INDEX "party_parent_party_id_ix" ON "parties"."party"
USING btree ("parent_party_id");

CREATE INDEX "party_party_path_ix" ON "parties"."party"
USING btree ("party_path");

CREATE UNIQUE INDEX "party_partyname_uix" ON "parties"."party"
USING btree ("pg_catalog"."lower"("party_name"));

--Row count = 725250
CREATE TABLE "orders"."commercial_order" (
  "commercial_order_id" SERIAL,
  "commercial_order_type_id" INTEGER NOT NULL,
  "party_id" INTEGER NOT NULL,
  "usr_id" INTEGER NOT NULL,
  "raised_dtm" TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT ('now'::text)::timestamp with time zone NOT NULL,
  "status_id" INTEGER,
  "consumer_ref" INTEGER,
  CONSTRAINT "commercial_order_pk" PRIMARY KEY("commercial_order_id"),
  CONSTRAINT "commercial_order_commercial_order_id_ck" CHECK (commercial_order_id >= 1),
  CONSTRAINT "commercial_order_party_id_fk" FOREIGN KEY ("party_id")
    REFERENCES "parties"."party"("party_id")
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE,
  CONSTRAINT "commercial_order_status_fk" FOREIGN KEY ("status_id")
    REFERENCES "orders"."status"("status_id")
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE,
  CONSTRAINT "commercial_order_type_commercial_order" FOREIGN KEY ("commercial_order_type_id")
    REFERENCES "business_rules"."commercial_order_type"("commercial_order_type_id")
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE,
  CONSTRAINT "commercial_order_usr_id_fk" FOREIGN KEY ("usr_id")
    REFERENCES "users"."usr"("usr_id")
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE
) WITH OIDS;

CREATE INDEX "commercial_order_consumer_ref_ix" ON "orders"."commercial_order"
USING btree ("consumer_ref");

CREATE INDEX "commercial_order_party_id_ix" ON "orders"."commercial_order"
USING btree ("party_id");

CREATE INDEX "commercial_order_raised_dtm_ix" ON "orders"."commercial_order"
USING btree ("raised_dtm");

CREATE INDEX "commercial_order_status_id_ix" ON "orders"."commercial_order"
USING btree ("status_id");

CREATE INDEX "commercial_order_type_commercial_order_ix" ON "orders"."commercial_order"
USING btree ("commercial_order_type_id");

CREATE INDEX "commercial_order_usr_id_ix" ON "orders"."commercial_order"
USING btree ("usr_id");

--Row count =727726
CREATE TABLE "orders"."component" (
  "component_id" SERIAL,
  "depends_on_component_id" INTEGER,
  "commercial_order_id" INTEGER NOT NULL,
  "component_type_id" INTEGER NOT NULL,
  "raised_dtm" TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT ('now'::text)::timestamp with time zone NOT NULL,
  "current_status_id_fr" INTEGER,
  "usr_id" INTEGER NOT NULL,
  "last_supplier_status" INTEGER,
  CONSTRAINT "component_pk" PRIMARY KEY("component_id"),
  CONSTRAINT "component_component_id_ck" CHECK (component_id >= 1),
  CONSTRAINT "component_affects_component_id_fk" FOREIGN KEY ("depends_on_component_id")
    REFERENCES "orders"."component"("component_id")
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE,
  CONSTRAINT "component_commercial_order_id_fk" FOREIGN KEY ("commercial_order_id")
    REFERENCES "orders"."commercial_order"("commercial_order_id")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE,
  CONSTRAINT "component_current_status_fk" FOREIGN KEY ("current_status_id_fr")
    REFERENCES "orders"."status"("status_id")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE,
  CONSTRAINT "component_supplier_code_fk" FOREIGN KEY ("last_supplier_status")
    REFERENCES "orders"."response_code"("response_code_id")
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE,
  CONSTRAINT "component_type_fk" FOREIGN KEY ("component_type_id")
    REFERENCES "business_rules"."component_type"("component_type_id")
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE,
  CONSTRAINT "component_usr_id_fk" FOREIGN KEY ("usr_id")
    REFERENCES "users"."usr"("usr_id")
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE
) WITH OIDS;

CREATE INDEX "component_affects_component_id_ix" ON "orders"."component"
USING btree ("depends_on_component_id");

CREATE INDEX "component_commercial_order_id_ix" ON "orders"."component"
USING btree ("commercial_order_id");

CREATE INDEX "component_cti_ix" ON "orders"."component"
USING btree ("component_type_id");

CREATE INDEX "component_current_status_ix" ON "orders"."component"
USING btree ("current_status_id_fr");

CREATE INDEX "component_last_supplier_status_ix" ON "orders"."component"
USING btree ("last_supplier_status");

CREATE INDEX "component_raised_dtm_ix" ON "orders"."component"
USING btree ("raised_dtm");

CREATE INDEX "component_usr_id_ix" ON "orders"."component"
USING btree ("usr_id");

--Row count = 677424
CREATE TABLE "orders"."ida" (
  "component_id" INTEGER NOT NULL,
  "cli" TEXT,
  "ida_barring_level_id" INTEGER NOT NULL,
  "switchgroup_id" INTEGER,
  CONSTRAINT "ida_pkey" PRIMARY KEY("component_id"),
  CONSTRAINT "ida_barring_level_id_fk" FOREIGN KEY ("ida_barring_level_id")
    REFERENCES "line_configs"."ida_barring_level"("ida_barring_level_id")
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE,
  CONSTRAINT "ida_component_id_fk" FOREIGN KEY ("component_id")
    REFERENCES "orders"."component"("component_id")
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE,
  CONSTRAINT "ida_switchgroup_id_fk" FOREIGN KEY ("switchgroup_id")
    REFERENCES "line_configs"."switch_group"("switch_group_id")
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE
) WITH OIDS;

CREATE INDEX "ida_bl_id_ix" ON "orders"."ida"
USING btree ("ida_barring_level_id");

CREATE INDEX "ida_cli_ix" ON "orders"."ida"
USING btree ("cli");

CREATE INDEX "ida_sg_id_ix" ON "orders"."ida"
USING btree ("switchgroup_id");

--Row count =937
CREATE TABLE "parties"."consumer_ref" (
  "consumer_ref_id" SERIAL,
  "retailer_id" INTEGER NOT NULL,
  "consumer_ref" TEXT,
  "is_active" BOOLEAN DEFAULT 't' NOT NULL,
  "consumer_id" INTEGER,
  CONSTRAINT "consumer_ref_pk" PRIMARY KEY("consumer_ref_id"),
  CONSTRAINT "consumer_ref_id_ck" CHECK (consumer_ref_id > 0),
  CONSTRAINT "consumer_ref_consumer_id" FOREIGN KEY ("consumer_id")
    REFERENCES "parties"."party"("party_id")
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE,
  CONSTRAINT "consumer_ref_retailer_id_fk" FOREIGN KEY ("retailer_id")
    REFERENCES "parties"."party"("party_id")
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE
) WITH OIDS;

CREATE UNIQUE INDEX "consumer_ref_id_ref_uix" ON "parties"."consumer_ref"
USING btree ("retailer_id", "consumer_ref");

--Row count = 39
CREATE TABLE "line_configs"."ida_barring_level" (
  "ida_barring_level_id" SERIAL,
  "description" TEXT,
  "is_active" BOOLEAN DEFAULT true NOT NULL,
  "ncos_value" INTEGER NOT NULL,
  CONSTRAINT "ida_barring_level_ncos_value_key" UNIQUE("ncos_value"),
  CONSTRAINT "ida_barring_level_pkey" PRIMARY KEY("ida_barring_level_id"),
  CONSTRAINT "ida_barring_level_id_ck" CHECK (ida_barring_level_id > 0)
) WITH OIDS;


--Row count = 159
CREATE TABLE "line_configs"."switch_group" (
  "switch_group_id" SERIAL,
  "servcom_name" TEXT NOT NULL,
  "party_id" INTEGER,
  "is_default" BOOLEAN DEFAULT 'F' NOT NULL,
  "switch_group_name" TEXT NOT NULL,
  "switch_group_code" INTEGER NOT NULL,
  "is_active" BOOLEAN DEFAULT 'T',
  "consumer_access" BOOLEAN DEFAULT false NOT NULL,
  CONSTRAINT "switch_group_pk" PRIMARY KEY("switch_group_id"),
  CONSTRAINT "switch_group_switch_group_code_key" UNIQUE("switch_group_code"),
  CONSTRAINT "switch_group_switch_group_id_ck" CHECK (switch_group_id >= 1),
  CONSTRAINT "switch_group_party_fk" FOREIGN KEY ("party_id")
    REFERENCES "parties"."party"("party_id")
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE
) WITH OIDS;

CREATE INDEX "switch_group_party_ix" ON "line_configs"."switch_group"
USING btree ("party_id");

--Row count = 41
CREATE TABLE "business_rules"."component_type" (
  "component_type_id" SERIAL,
  "product_id" INTEGER NOT NULL,
  "order_type_id" INTEGER NOT NULL,
  "is_active" BOOLEAN DEFAULT 't' NOT NULL,
  "affects_quota" BOOLEAN DEFAULT true NOT NULL,
  "uses_order_table" TEXT,
  CONSTRAINT "product_order_type_pkey" PRIMARY KEY("component_type_id"),
  CONSTRAINT "component_type_order_type_fk" FOREIGN KEY ("order_type_id")
    REFERENCES "business_rules"."order_type"("order_type_id")
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE,
  CONSTRAINT "component_type_product_fk" FOREIGN KEY ("product_id")
    REFERENCES "business_rules"."product"("product_id")
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE
) WITH OIDS;

CREATE UNIQUE INDEX "component_type_uix" ON "business_rules"."component_type"
USING btree ("product_id", "order_type_id");

--Row count = 15
CREATE TABLE "business_rules"."order_type" (
  "order_type_id" SERIAL,
  "description" TEXT NOT NULL,
  "is_active" BOOLEAN DEFAULT 't' NOT NULL,
  CONSTRAINT "order_type_pk" PRIMARY KEY("order_type_id"),
  CONSTRAINT "order_type_id_ck" CHECK (order_type_id > 0)
) WITH OIDS;

CREATE UNIQUE INDEX "order_type_description_uix" ON "business_rules"."order_type"
USING btree ("description");

--Row count = 9
CREATE TABLE "orders"."status" (
  "status_id" SERIAL,
  "description" TEXT NOT NULL,
  "further_detail" TEXT,
  "is_final" BOOLEAN DEFAULT true NOT NULL,
  "make_post" BOOLEAN DEFAULT false,
  CONSTRAINT "status_pk" PRIMARY KEY("status_id"),
  CONSTRAINT "status_status_id_ck" CHECK (status_id >= 1)
) WITH OIDS;

--Row count = 299
CREATE TABLE "orders"."response_code" (
  "response_code_id" SERIAL,
  "response_code" TEXT NOT NULL,
  "description" TEXT NOT NULL,
  "status_id" INTEGER,
  "supplier_id" INTEGER NOT NULL,
  "is_active" BOOLEAN DEFAULT 't' NOT NULL,
  "changes_supplier_status" BOOLEAN DEFAULT true NOT NULL,
  CONSTRAINT "response_code_pk" PRIMARY KEY("response_code_id"),
  CONSTRAINT "response_code_response_id_ck" CHECK (response_code_id >= 1),
  CONSTRAINT "response_code_status_id_fk" FOREIGN KEY ("status_id")
    REFERENCES "orders"."status"("status_id")
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE,
  CONSTRAINT "response_code_supplier_id_fk" FOREIGN KEY ("supplier_id")
    REFERENCES "parties"."party"("party_id")
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE
) WITH OIDS;

CREATE UNIQUE INDEX "response_code_response_code_id_supplier_id_uix" ON "orders"."response_code"
USING btree ("response_code", "supplier_id");

CREATE INDEX "response_code_status_id_ix" ON "orders"."response_code"
USING btree ("status_id");

CREATE INDEX "response_code_supplier_id_ix" ON "orders"."response_code"
USING btree ("supplier_id");

--Row count = 903283

CREATE TABLE "orders"."communication" (
  "communication_id" SERIAL,
  "component_id" INTEGER NOT NULL,
  "dtm" TIMESTAMP WITHOUT TIME ZONE DEFAULT ('now'::text)::timestamp(6) with time zone NOT NULL,
  "detail" TEXT,
  "response_code_id" INTEGER NOT NULL,
  "usr_id" INTEGER NOT NULL,
  CONSTRAINT "communication_pk" PRIMARY KEY("communication_id"),
  CONSTRAINT "communication_communication_id_ck" CHECK (communication_id >= 1),
  FOREIGN KEY ("usr_id")
    REFERENCES "users"."usr"("usr_id")
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE,
  CONSTRAINT "communication_component_id_fk" FOREIGN KEY ("component_id")
    REFERENCES "orders"."component"("component_id")
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE,
  CONSTRAINT "communication_response_code_fk" FOREIGN KEY ("response_code_id")
    REFERENCES "orders"."response_code"("response_code_id")
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE
) WITH OIDS;

CREATE INDEX "communication_component_id_ix" ON "orders"."communication"
USING btree ("component_id");

CREATE INDEX "communication_dtm_ix" ON "orders"."communication"
USING btree ("dtm");

CREATE INDEX "communication_response_code_id_ix" ON "orders"."communication"
USING btree ("response_code_id");

