--
--

SET transaction_timeout = 0;



-- Table and Sequence Creations
CREATE SEQUENCE public.area_master_area_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE TABLE public.area_master ( area_id integer DEFAULT nextval('public.area_master_area_id_seq'::regclass) NOT NULL, city_id integer, area_name character varying(120), delivery_available boolean DEFAULT true, standard_delivery boolean DEFAULT true, paid_delivery boolean DEFAULT false, delivery_charge numeric(10,2) DEFAULT 0, estimated_standard_days integer DEFAULT 1, estimated_paid_days integer DEFAULT 0, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP );

CREATE SEQUENCE public.bill_items_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE TABLE public.bill_items ( id integer DEFAULT nextval('public.bill_items_id_seq'::regclass) NOT NULL, bill_id integer, product_id integer, name character varying(255), quantity integer NOT NULL, unit_price numeric(10,2) NOT NULL, subtotal numeric(10,2) NOT NULL, discount numeric(10,2) DEFAULT 0 );

CREATE SEQUENCE public.bills_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE TABLE public.bills ( id integer DEFAULT nextval('public.bills_id_seq'::regclass) NOT NULL, bill_number character varying(20) NOT NULL, total_amount numeric(10,2) NOT NULL, discount numeric(10,2) DEFAULT 0, taxable_amount numeric(10,2) DEFAULT 0, cgst numeric(10,2) DEFAULT 0, sgst numeric(10,2) DEFAULT 0, final_amount numeric(10,2) NOT NULL, customer_name character varying(100), customer_phone character varying(15), payment_mode character varying(20) DEFAULT 'Cash'::character varying, branch_id integer, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP );

CREATE SEQUENCE public.branches_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE TABLE public.branches ( id integer DEFAULT nextval('public.branches_id_seq'::regclass) NOT NULL, name character varying(255) NOT NULL, location character varying(255), gst_number character varying(20), phone character varying(15), created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP );

CREATE SEQUENCE public.cart_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE TABLE public.cart ( id integer DEFAULT nextval('public.cart_id_seq'::regclass) NOT NULL, customer_id integer, product_id integer, quantity integer DEFAULT 1, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, prod_id integer, variant_id integer );

CREATE SEQUENCE public.categories_cat_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE TABLE public.categories ( cat_id integer DEFAULT nextval('public.categories_cat_id_seq'::regclass) NOT NULL, cat_name character varying(120) NOT NULL, domain_name character varying(120), created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, domain text );

CREATE SEQUENCE public.city_master_city_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE TABLE public.city_master ( city_id integer DEFAULT nextval('public.city_master_city_id_seq'::regclass) NOT NULL, city_name character varying(120), created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP );

CREATE SEQUENCE public.customer_customer_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE SEQUENCE public.customer_address_address_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE TABLE public.customer_address ( address_id integer DEFAULT nextval('public.customer_address_address_id_seq'::regclass) NOT NULL, customer_id integer, address text, area_id integer, city_id integer, is_default boolean DEFAULT false, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, name character varying(120), phone character varying(20), subarea character varying(150), pincode character varying(20), instruction text );

CREATE SEQUENCE public.order_items_order_item_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE TABLE public.order_items ( order_item_id integer DEFAULT nextval('public.order_items_order_item_id_seq'::regclass) NOT NULL, order_id integer, product_id integer, quantity numeric(10,2), price numeric(10,2), prod_name character varying(255), variant_display character varying(255) );

CREATE SEQUENCE public.order_status_history_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE TABLE public.order_status_history ( id integer DEFAULT nextval('public.order_status_history_id_seq'::regclass) NOT NULL, order_id integer, status character varying(50), changed_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP );

CREATE SEQUENCE public.orders_order_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE TABLE public.orders ( order_id integer DEFAULT nextval('public.orders_order_id_seq'::regclass) NOT NULL, customer_id integer, total_amount numeric(10,2), status character varying(50), created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, address_id integer, items_total numeric(10,2), total_price numeric(10,2), delivery_method character varying(20), delivery_charge numeric(10,2), estimated_delivery_date date, estimated_delivery_days integer, payment_method character varying(20), delivery_instruction text, order_status character varying(50), payment_status character varying(50), delivery_address_json jsonb, stripe_payment_intent_id character varying(255) );

CREATE SEQUENCE public.product_images_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE TABLE public.product_images ( id integer DEFAULT nextval('public.product_images_id_seq'::regclass) NOT NULL, product_id integer NOT NULL, image_url text NOT NULL, sort_order integer DEFAULT 0, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP );

CREATE SEQUENCE public.product_shop_stock_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE TABLE public.product_shop_stock ( id integer DEFAULT nextval('public.product_shop_stock_id_seq'::regclass) NOT NULL, prod_id integer, variant_id integer, shop_id integer, quantity numeric(10,2) DEFAULT 0, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP );

CREATE SEQUENCE public.product_variants_variant_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE TABLE public.product_variants ( variant_id integer DEFAULT nextval('public.product_variants_variant_id_seq'::regclass) NOT NULL, product_id integer NOT NULL, variant_label character varying(180), variant_value numeric(12,3), variant_unit_type character varying(60), variant_unit character varying(40), variant_sku character varying(120), variant_barcode character varying(120), variant_mrp numeric(12,2) DEFAULT 0, variant_discount_percent numeric(8,2) DEFAULT 0, variant_price numeric(12,2) DEFAULT 0, variant_offer_price numeric(12,2), variant_offer_start date, variant_offer_end date, variant_quantity numeric(12,2) DEFAULT 0, variant_weight numeric(12,3), variant_final_price numeric(12,2), is_default boolean DEFAULT false, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP );

CREATE SEQUENCE public.products_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE SEQUENCE public.products_prod_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE TABLE public.products ( prod_id integer DEFAULT nextval('public.products_prod_id_seq'::regclass) CONSTRAINT products_id_not_null NOT NULL, prod_name character varying(255) CONSTRAINT products_name_not_null NOT NULL, scanner_id character varying(50), category character varying(100), sub_category character varying(100), domain character varying(100), prod_sell_price numeric(10,2) CONSTRAINT products_price_not_null NOT NULL, prod_discount numeric(10,2) DEFAULT 0, prod_quantity integer DEFAULT 0, unit character varying(20) DEFAULT 'pcs'::character varying, prod_images character varying(500), hsn_code character varying(20) DEFAULT '0000'::character varying, gst_rate numeric(5,2) DEFAULT 18.00, branch_id integer, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, cat_id integer, sub_cat_id integer, prod_desc text, prod_brand character varying(150), prod_model_number character varying(150), prod_color character varying(100), prod_material character varying(150), prod_country_id_of_origin character varying(120), prod_warranty character varying(120), prod_special_features text, prod_storage_instructions text, prod_is_dangerous boolean DEFAULT false, prod_require_battery boolean DEFAULT false, prod_contains_battery boolean DEFAULT false, batch_no character varying(120), prod_mfg_date date, prod_exp_date date, prod_import_date date, prod_manufacturer character varying(180), product_type character varying(20) DEFAULT 'single'::character varying, prod_base_price numeric(10,2), prod_profit_margin numeric(10,2), prod_barcode character varying(100), prod_weight numeric(10,2), prod_shipping_weight numeric(10,2), prod_dimen_lxwxh_mm character varying(100), prod_size character varying(100), prod_packer_name character varying(255), prod_generic_name character varying(255), prod_importer_name character varying(255), offer_price numeric(10,2), offer_start date, offer_end date, prod_is_active boolean DEFAULT true );

CREATE SEQUENCE public.shop_service_area_service_area_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE TABLE public.shop_service_area ( service_area_id integer DEFAULT nextval('public.shop_service_area_service_area_id_seq'::regclass) NOT NULL, shop_id integer, city_id integer, area_id integer, is_available boolean DEFAULT true, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP );

CREATE SEQUENCE public.shops_shop_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE TABLE public.shops ( shop_id integer DEFAULT nextval('public.shops_shop_id_seq'::regclass) NOT NULL, shop_name character varying(255), shop_address text, city_id integer, area_id integer, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP );

CREATE SEQUENCE public.sub_categories_sub_cat_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE TABLE public.sub_categories ( sub_cat_id integer DEFAULT nextval('public.sub_categories_sub_cat_id_seq'::regclass) NOT NULL, cat_id integer NOT NULL, sub_cat_name character varying(120) NOT NULL, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP );

CREATE VIEW public.subcategories AS SELECT sub_cat_id, cat_id, sub_cat_name, created_at FROM public.sub_categories;

CREATE SEQUENCE public.users_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE SEQUENCE public.variant_shop_stock_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE TABLE public.variant_shop_stock ( id integer DEFAULT nextval('public.variant_shop_stock_id_seq'::regclass) NOT NULL, variant_id integer NOT NULL, branch_id integer NOT NULL, quantity numeric(12,2) DEFAULT 0, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP );

CREATE SEQUENCE public.wishlist_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE TABLE public.wishlist ( id integer DEFAULT nextval('public.wishlist_id_seq'::regclass) NOT NULL, customer_id integer, product_id integer, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, prod_id integer );

-- Copy Data Blocks

COPY public.area_master (area_id, city_id, area_name, delivery_available, standard_delivery, paid_delivery, delivery_charge, estimated_standard_days, estimated_paid_days, created_at) FROM stdin;
1	1	Deira	t	t	t	15.00	2	1	2026-04-20 15:19:15.88061
2	1	Bur Dubai	t	t	f	0.00	2	0	2026-04-20 15:19:15.88061
3	2	Mussafah	t	t	t	20.00	3	1	2026-04-20 15:19:15.88061
\.

COPY public.branches (id, name, location, gst_number, phone, created_at) FROM stdin;
1	Main Branch	Chennai	33ABCDE1234F1Z5	9876543210	2026-04-10 07:49:50.013574
3	Second Branch	Coimbatore	33XYZAB5678K2Z6	9123456780	2026-04-10 07:51:29.144933
2	another one	Chennai	33ABCDE1234F1Z5	9876543210	2026-04-10 07:51:29.144933
\.

COPY public.cart (id, customer_id, product_id, quantity, created_at, prod_id, variant_id) FROM stdin;
37	1	14	1	2026-04-20 19:28:46.437491	\N	\N
\.

COPY public.categories (cat_id, cat_name, domain_name, created_at, domain) FROM stdin;
1	Dairy	Food	2026-04-10 14:01:12.124722	dairy & eggs
2	Grocery	Food	2026-04-10 14:01:12.124722	pantry
3	Vegetables	Food	2026-04-13 17:58:04.977933	produce
4	Fruits	Food	2026-04-13 17:58:04.977933	produce
\.

COPY public.city_master (city_id, city_name, created_at) FROM stdin;
1	Dubai	2026-04-20 15:18:42.17672
2	Abu Dhabi	2026-04-20 15:18:42.17672
3	Sharjah	2026-04-20 15:18:42.17672
\.

COPY public.customer_address (address_id, customer_id, address, area_id, city_id, is_default, created_at, name, phone, subarea, pincode, instruction) FROM stdin;
1	1	milk street	1	1	f	2026-04-20 15:38:18.310132	Deepankumar Selvaraj	9898989898		638301	in front of gate
\.

COPY public.order_items (order_item_id, order_id, product_id, quantity, price, prod_name, variant_display) FROM stdin;
1	2	14	1.00	315.00	apple	1.000 kg
2	2	14	2.00	350.00	apple	\N
4	12	14	2.00	350.00	apple	\N
5	13	14	1.00	315.00	apple	1.000 kg
6	14	14	6.00	350.00	apple	\N
7	15	14	1.00	350.00	apple	\N
8	16	14	1.00	350.00	apple	\N
\.

COPY public.orders (order_id, customer_id, total_amount, status, created_at, address_id, items_total, total_price, delivery_method, delivery_charge, estimated_delivery_date, estimated_delivery_days, payment_method, delivery_instruction, order_status, payment_status, delivery_address_json, stripe_payment_intent_id) FROM stdin;
2	1	1015.00	Placed	2026-04-20 15:46:02.516757	1	1015.00	1015.00	standard	0.00	2026-04-22	2	cod	in front of gate	placed	pending_cod	\N	\N
12	1	700.00	Confirmed	2026-04-20 17:05:44.189054	1	700.00	700.00	standard	0.00	2026-04-22	2	card	in front of gate	confirmed	paid	\N	pi_3TOG7AJJrNVIQVKq0yvDC4ld
16	1	350.00	Pending	2026-04-20 19:23:30.073988	1	350.00	350.00	standard	0.00	2026-04-22	2	card	in front of gate	pending	pending_payment	\N	pi_3TOIGUJJrNVIQVKq0Q3l89Cx
14	1	2115.00	Confirmed	2026-04-20 17:56:37.127445	1	2100.00	2115.00	paid	15.00	2026-04-21	1	card	in front of gate	confirmed	paid	\N	pi_3TOGuPJJrNVIQVKq1kl25TIC
13	1	315.00	Cancelled	2026-04-20 17:56:20.209643	1	315.00	315.00	standard	0.00	2026-04-22	2	cod	in front of gate	cancelled	pending_cod	\N	\N
15	1	350.00	Confirmed	2026-04-20 18:57:42.077664	1	350.00	350.00	standard	0.00	2026-04-22	2	card	in front of gate	confirmed	paid	\N	pi_3TOHrXJJrNVIQVKq1MtG98zo
\.

COPY public.product_images (id, product_id, image_url, sort_order, created_at) FROM stdin;
3	8	/uploads/1776079312996-tomato.avif	0	2026-04-13 18:38:09.078796
\.

COPY public.product_shop_stock (id, prod_id, variant_id, shop_id, quantity, created_at) FROM stdin;
1	14	10	1	12.00	2026-04-17 11:43:49.097314
\.

COPY public.product_variants (variant_id, product_id, variant_label, variant_value, variant_unit_type, variant_unit, variant_sku, variant_barcode, variant_mrp, variant_discount_percent, variant_price, variant_offer_price, variant_offer_start, variant_offer_end, variant_quantity, variant_weight, variant_final_price, is_default, created_at) FROM stdin;
5	8	1 kg	1.000	Weight	kg	\N	\N	\N	0.00	39.20	\N	\N	\N	12.00	0.000	39.20	t	2026-04-13 18:38:09.078796
10	14	1 kg	1.000	Weight	kg	\N	\N	350.00	10.00	315.00	\N	\N	\N	12.00	0.000	315.00	t	2026-04-17 11:43:49.097314
\.

COPY public.products (prod_id, prod_name, scanner_id, category, sub_category, domain, prod_sell_price, prod_discount, prod_quantity, unit, prod_images, hsn_code, gst_rate, branch_id, created_at, cat_id, sub_cat_id, prod_desc, prod_brand, prod_model_number, prod_color, prod_material, prod_country_id_of_origin, prod_warranty, prod_special_features, prod_storage_instructions, prod_is_dangerous, prod_require_battery, prod_contains_battery, batch_no, prod_mfg_date, prod_exp_date, prod_import_date, prod_manufacturer, product_type, prod_base_price, prod_profit_margin, prod_barcode, prod_weight, prod_shipping_weight, prod_dimen_lxwxh_mm, prod_size, prod_packer_name, prod_generic_name, prod_importer_name, offer_price, offer_start, offer_end, prod_is_active) FROM stdin;
4	Rice 1kg	SCN001	Grocery	Grains	Food	60.00	5.00	100	kg	\N	0000	18.00	1	2026-04-10 07:51:07.672278	2	2	\N	\N	\N	\N	\N	\N	\N	\N	\N	f	f	f	\N	\N	\N	\N	\N	single	60.00	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	t
5	Sugar 1kg	SCN002	Grocery	Sweet	Food	45.00	2.00	80	kg	\N	0000	18.00	1	2026-04-10 07:51:07.672278	2	3	\N	\N	\N	\N	\N	\N	\N	\N	\N	f	f	f	\N	\N	\N	\N	\N	single	45.00	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	t
6	Milk 500ml	SCN003	Dairy	Milk	Food	30.00	0.00	50	pcs	\N	0000	18.00	1	2026-04-10 07:51:07.672278	1	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	f	f	f	\N	\N	\N	\N	\N	single	30.00	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	t
8	tomato	\N	Vegetables	General		39.20	0.00	12	kg		0000	18.00	1	2026-04-10 16:40:11.34826	1	1										f	f	f		\N	\N	\N		single	39.20	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	t
10	tomato	\N	fruits	General		300.00	5.00	12	kg		0000	18.00	1	2026-04-10 16:40:11.34826	1	1										f	f	f		\N	\N	\N		single	39.20	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	t
14	apple	APPLE	Fruits	General	pantry	315.00	10.00	12	kg	{"1776406429054-566951647.avif","1776406429055-225034966.avif"}	0000	18.00	1	2026-04-17 11:43:49.097314	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	f	f	f	\N	\N	\N	\N	\N	single	350.00	\N	APPLE	0.00	\N	\N	\N	\N	\N	\N	0.00	\N	\N	t
\.

COPY public.shops (shop_id, shop_name, shop_address, city_id, area_id, created_at) FROM stdin;
1	Main Branch	Chennai	\N	\N	2026-04-16 16:45:24.873638
3	Second Branch	Coimbatore	\N	\N	2026-04-16 16:45:24.873638
2	another one	Chennai	\N	\N	2026-04-16 16:45:24.873638
\.

COPY public.sub_categories (sub_cat_id, cat_id, sub_cat_name, created_at) FROM stdin;
1	1	Milk	2026-04-10 14:01:12.124722
2	2	Grains	2026-04-10 14:01:12.124722
3	2	Sweet	2026-04-10 14:01:12.124722
6	3	Leafy	2026-04-13 17:58:21.004804
7	3	Root	2026-04-13 17:58:21.004804
8	3	General	2026-04-13 17:58:21.004804
9	4	Citrus	2026-04-13 17:58:21.004804
10	4	Tropical	2026-04-13 17:58:21.004804
11	4	General	2026-04-13 17:58:21.004804
\.

COPY public.variant_shop_stock (id, variant_id, branch_id, quantity, created_at) FROM stdin;
3	5	2	12.00	2026-04-13 18:38:09.078796
\.

COPY public.wishlist (id, customer_id, product_id, created_at, prod_id) FROM stdin;
3	\N	\N	2026-04-17 12:34:02.022719	\N
4	\N	\N	2026-04-17 12:34:02.906639	\N
5	\N	\N	2026-04-17 12:34:03.137723	\N
6	\N	\N	2026-04-17 12:49:15.603298	\N
8	\N	\N	2026-04-17 18:49:57.556955	14
\.

-- Completed
