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;