dummydb.md

Database Schema:

CREATE TABLE IF NOT EXISTS public.erp_customers

(

customer_id integer NOT NULL DEFAULT nextval('erp_customers_customer_id_seq'::regclass),

name character varying(100) COLLATE pg_catalog."default" NOT NULL,

email character varying(100) COLLATE pg_catalog."default" NOT NULL,

phone character varying(20) COLLATE pg_catalog."default",

address text COLLATE pg_catalog."default",

created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,

CONSTRAINT erp_customers_pkey PRIMARY KEY (customer_id),

CONSTRAINT erp_customers_email_key UNIQUE (email)

)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.erp_customers

OWNER to postgres;

CREATE TABLE IF NOT EXISTS public.erp_invoices

(

invoice_id integer NOT NULL DEFAULT nextval('erp_invoices_invoice_id_seq'::regclass),

order_id integer,

invoice_date date DEFAULT CURRENT_DATE,

amount numeric(12,2) NOT NULL,

payment_terms character varying(50) COLLATE pg_catalog."default",

due_date date,

is_paid boolean DEFAULT false,

invoice_number character varying(50) COLLATE pg_catalog."default",

CONSTRAINT erp_invoices_pkey PRIMARY KEY (invoice_id),

CONSTRAINT erp_invoices_invoice_number_key UNIQUE (invoice_number),

CONSTRAINT erp_invoices_order_id_fkey FOREIGN KEY (order_id)

    REFERENCES public.erp_orders (order_id) MATCH SIMPLE

    ON UPDATE NO ACTION

    ON DELETE NO ACTION

)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.erp_invoices

OWNER to postgres;

CREATE INDEX IF NOT EXISTS idx_invoices_order

ON public.erp_invoices USING btree

(order_id ASC NULLS LAST)

TABLESPACE pg_default;

CREATE TABLE IF NOT EXISTS public.erp_order_history

(

history_id integer NOT NULL DEFAULT nextval('erp_order_history_history_id_seq'::regclass),

order_id integer,

"timestamp" timestamp without time zone DEFAULT CURRENT_TIMESTAMP,

status_change character varying(50) COLLATE pg_catalog."default",

notes text COLLATE pg_catalog."default",

updated_by character varying(100) COLLATE pg_catalog."default",

CONSTRAINT erp_order_history_pkey PRIMARY KEY (history_id),

CONSTRAINT erp_order_history_order_id_fkey FOREIGN KEY (order_id)

    REFERENCES public.erp_orders (order_id) MATCH SIMPLE

    ON UPDATE NO ACTION

    ON DELETE NO ACTION

)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.erp_order_history

OWNER to postgres;

CREATE INDEX IF NOT EXISTS idx_order_history_order

ON public.erp_order_history USING btree

(order_id ASC NULLS LAST)

TABLESPACE pg_default;

CREATE TABLE IF NOT EXISTS public.erp_order_items

(

order_item_id integer NOT NULL DEFAULT nextval('erp_order_items_order_item_id_seq'::regclass),

order_id integer,

product_id integer,

quantity integer NOT NULL,

unit_price numeric(10,2) NOT NULL,

subtotal numeric(12,2) NOT NULL,

CONSTRAINT erp_order_items_pkey PRIMARY KEY (order_item_id),

CONSTRAINT erp_order_items_order_id_fkey FOREIGN KEY (order_id)

    REFERENCES public.erp_orders (order_id) MATCH SIMPLE

    ON UPDATE NO ACTION

    ON DELETE NO ACTION,

CONSTRAINT erp_order_items_product_id_fkey FOREIGN KEY (product_id)

    REFERENCES public.erp_products (product_id) MATCH SIMPLE

    ON UPDATE NO ACTION

    ON DELETE NO ACTION

)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.erp_order_items

OWNER to postgres;

CREATE INDEX IF NOT EXISTS idx_order_items_order

ON public.erp_order_items USING btree

(order_id ASC NULLS LAST)

TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS idx_order_items_product

ON public.erp_order_items USING btree

(product_id ASC NULLS LAST)

TABLESPACE pg_default;

CREATE TABLE IF NOT EXISTS public.erp_orders

(

order_id integer NOT NULL DEFAULT nextval('erp_orders_order_id_seq'::regclass),

customer_id integer,

order_date date DEFAULT CURRENT_DATE,

total_amount numeric(12,2) NOT NULL,

status character varying(20) COLLATE pg_catalog."default" NOT NULL DEFAULT 'Processing'::character varying,

previous_order_id integer,

estimated_delivery date,

actual_delivery date,

payment_status character varying(20) COLLATE pg_catalog."default" DEFAULT 'Pending'::character varying,

shipping_address text COLLATE pg_catalog."default" NOT NULL,

shipping_country character varying(100) COLLATE pg_catalog."default",

destination_country character varying(100) COLLATE pg_catalog."default",

CONSTRAINT erp_orders_pkey PRIMARY KEY (order_id),

CONSTRAINT erp_orders_customer_id_fkey FOREIGN KEY (customer_id)

    REFERENCES public.erp_customers (customer_id) MATCH SIMPLE

    ON UPDATE NO ACTION

    ON DELETE NO ACTION,

CONSTRAINT erp_orders_previous_order_id_fkey FOREIGN KEY (previous_order_id)

    REFERENCES public.erp_orders (order_id) MATCH SIMPLE

    ON UPDATE NO ACTION

    ON DELETE NO ACTION

)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.erp_orders

OWNER to postgres;

CREATE INDEX IF NOT EXISTS idx_orders_customer

ON public.erp_orders USING btree

(customer_id ASC NULLS LAST)

TABLESPACE pg_default;

CREATE TABLE IF NOT EXISTS public.erp_products

(

product_id integer NOT NULL DEFAULT nextval('erp_products_product_id_seq'::regclass),

product_name character varying(100) COLLATE pg_catalog."default" NOT NULL,

description text COLLATE pg_catalog."default",

category character varying(50) COLLATE pg_catalog."default",

price numeric(10,2) NOT NULL,

stock_quantity integer NOT NULL DEFAULT 0,

sku character varying(50) COLLATE pg_catalog."default",

CONSTRAINT erp_products_pkey PRIMARY KEY (product_id),

CONSTRAINT erp_products_sku_key UNIQUE (sku)

)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.erp_products

OWNER to postgres;