-- SuperMart Migration Script
-- Drops old tables and recreates with the correct schema


-- Drop old tables (order matters for FK)
DROP VIEW IF EXISTS public.subcategories CASCADE;

-- Drop old sequences
DROP SEQUENCE IF EXISTS public.area_master_area_id_seq CASCADE;
DROP SEQUENCE IF EXISTS public.bill_items_id_seq CASCADE;
DROP SEQUENCE IF EXISTS public.bills_id_seq CASCADE;
DROP SEQUENCE IF EXISTS public.branches_id_seq CASCADE;
DROP SEQUENCE IF EXISTS public.cart_id_seq CASCADE;
DROP SEQUENCE IF EXISTS public.categories_cat_id_seq CASCADE;
DROP SEQUENCE IF EXISTS public.city_master_city_id_seq CASCADE;
DROP SEQUENCE IF EXISTS public.customer_customer_id_seq CASCADE;
DROP SEQUENCE IF EXISTS public.customer_address_address_id_seq CASCADE;
DROP SEQUENCE IF EXISTS public.order_items_order_item_id_seq CASCADE;
DROP SEQUENCE IF EXISTS public.order_status_history_id_seq CASCADE;
DROP SEQUENCE IF EXISTS public.orders_order_id_seq CASCADE;
DROP SEQUENCE IF EXISTS public.product_images_id_seq CASCADE;
DROP SEQUENCE IF EXISTS public.product_shop_stock_id_seq CASCADE;
DROP SEQUENCE IF EXISTS public.product_variants_variant_id_seq CASCADE;
DROP SEQUENCE IF EXISTS public.products_id_seq CASCADE;
DROP SEQUENCE IF EXISTS public.products_prod_id_seq CASCADE;
DROP SEQUENCE IF EXISTS public.shop_service_area_service_area_id_seq CASCADE;
DROP SEQUENCE IF EXISTS public.shops_shop_id_seq CASCADE;
DROP SEQUENCE IF EXISTS public.sub_categories_sub_cat_id_seq CASCADE;
DROP SEQUENCE IF EXISTS public.users_id_seq CASCADE;
DROP SEQUENCE IF EXISTS public.variant_shop_stock_id_seq CASCADE;
DROP SEQUENCE IF EXISTS public.wishlist_id_seq CASCADE;

-- ========================
-- CREATE SEQUENCES
-- ========================
CREATE SEQUENCE public.area_master_area_id_seq START WITH 10 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE SEQUENCE public.bill_items_id_seq START WITH 10 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE SEQUENCE public.bills_id_seq START WITH 10 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE SEQUENCE public.branches_id_seq START WITH 10 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE SEQUENCE public.cart_id_seq START WITH 10 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE SEQUENCE public.categories_cat_id_seq START WITH 10 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE SEQUENCE public.city_master_city_id_seq START WITH 10 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE SEQUENCE public.customer_customer_id_seq START WITH 10 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE SEQUENCE public.customer_address_address_id_seq START WITH 10 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE SEQUENCE public.order_items_order_item_id_seq START WITH 10 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE SEQUENCE public.order_status_history_id_seq START WITH 10 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE SEQUENCE public.orders_order_id_seq START WITH 10 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE SEQUENCE public.product_images_id_seq START WITH 10 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE SEQUENCE public.product_shop_stock_id_seq START WITH 10 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE SEQUENCE public.product_variants_variant_id_seq START WITH 10 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE SEQUENCE public.products_prod_id_seq START WITH 20 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE SEQUENCE public.shop_service_area_service_area_id_seq START WITH 10 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE SEQUENCE public.shops_shop_id_seq START WITH 10 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE SEQUENCE public.sub_categories_sub_cat_id_seq START WITH 20 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE SEQUENCE public.users_id_seq START WITH 20 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE SEQUENCE public.variant_shop_stock_id_seq START WITH 10 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE SEQUENCE public.wishlist_id_seq START WITH 10 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

-- ========================
-- CREATE TABLES
-- ========================
CREATE TABLE public.area_master (
    area_id integer DEFAULT nextval('public.area_master_area_id_seq') 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 TABLE public.branches (
    id integer DEFAULT nextval('public.branches_id_seq') 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 TABLE public.city_master (
    city_id integer DEFAULT nextval('public.city_master_city_id_seq') NOT NULL,
    city_name character varying(120),
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE public.categories (
    cat_id integer DEFAULT nextval('public.categories_cat_id_seq') 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 TABLE public.sub_categories (
    sub_cat_id integer DEFAULT nextval('public.sub_categories_sub_cat_id_seq') 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 TABLE public.products (
    prod_id integer DEFAULT nextval('public.products_prod_id_seq') NOT NULL,
    prod_name character varying(255) 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) NOT NULL,
    prod_discount numeric(10,2) DEFAULT 0,
    prod_quantity integer DEFAULT 0,
    unit character varying(20) DEFAULT 'pcs',
    prod_images character varying(500),
    hsn_code character varying(20) DEFAULT '0000',
    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',
    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 TABLE public.product_variants (
    variant_id integer DEFAULT nextval('public.product_variants_variant_id_seq') 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 TABLE public.product_images (
    id integer DEFAULT nextval('public.product_images_id_seq') 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 TABLE public.variant_shop_stock (
    id integer DEFAULT nextval('public.variant_shop_stock_id_seq') 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 TABLE public.shops (
    shop_id integer DEFAULT nextval('public.shops_shop_id_seq') 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 TABLE public.product_shop_stock (
    id integer DEFAULT nextval('public.product_shop_stock_id_seq') 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 TABLE public.shop_service_area (
    service_area_id integer DEFAULT nextval('public.shop_service_area_service_area_id_seq') 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 TABLE public.customer_address (
    address_id integer DEFAULT nextval('public.customer_address_address_id_seq') 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 TABLE public.bills (
    id integer DEFAULT nextval('public.bills_id_seq') 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',
    branch_id integer,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE public.bill_items (
    id integer DEFAULT nextval('public.bill_items_id_seq') 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 TABLE public.orders (
    order_id integer DEFAULT nextval('public.orders_order_id_seq') 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 TABLE public.order_items (
    order_item_id integer DEFAULT nextval('public.order_items_order_item_id_seq') 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 TABLE public.order_status_history (
    id integer DEFAULT nextval('public.order_status_history_id_seq') NOT NULL,
    order_id integer,
    status character varying(50),
    changed_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE public.cart (
    id integer DEFAULT nextval('public.cart_id_seq') 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 TABLE public.wishlist (
    id integer DEFAULT nextval('public.wishlist_id_seq') NOT NULL,
    customer_id integer,
    product_id integer,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    prod_id integer
);

-- ========================
-- SEED DATA
-- ========================

-- Branches
INSERT INTO public.branches (id, name, location, gst_number, phone) VALUES
(1, 'Main Branch', 'Chennai', '33ABCDE1234F1Z5', '9876543210'),
(2, 'another one', 'Chennai', '33ABCDE1234F1Z5', '9876543210'),
(3, 'Second Branch', 'Coimbatore', '33XYZAB5678K2Z6', '9123456780');

-- Categories
INSERT INTO public.categories (cat_id, cat_name, domain_name, domain) VALUES
(1, 'Dairy', 'Food', 'dairy & eggs'),
(2, 'Grocery', 'Food', 'pantry'),
(3, 'Vegetables', 'Food', 'produce'),
(4, 'Fruits', 'Food', 'produce');

-- Sub-categories
INSERT INTO public.sub_categories (sub_cat_id, cat_id, sub_cat_name) VALUES
(1, 1, 'Milk'),
(2, 2, 'Grains'),
(3, 2, 'Sweet'),
(6, 3, 'Leafy'),
(7, 3, 'Root'),
(8, 3, 'General'),
(9, 4, 'Citrus'),
(10, 4, 'Tropical'),
(11, 4, 'General');

-- Sample Products
INSERT INTO public.products (prod_id, prod_name, scanner_id, category, sub_category, domain, prod_sell_price, prod_discount, prod_quantity, unit, hsn_code, gst_rate, branch_id, cat_id, sub_cat_id, product_type, prod_base_price) VALUES
(4,  'Rice 1kg',   'SCN001', 'Grocery',    'Grains',  'pantry',    60.00, 5.00,  100, 'kg',  '0000', 18.00, 1, 2, 2, 'single', 60.00),
(5,  'Sugar 1kg',  'SCN002', 'Grocery',    'Sweet',   'pantry',    45.00, 2.00,  80,  'kg',  '0000', 18.00, 1, 2, 3, 'single', 45.00),
(6,  'Milk 500ml', 'SCN003', 'Dairy',      'Milk',    'dairy & eggs', 30.00, 0.00, 50, 'pcs', '0000', 18.00, 1, 1, 1, 'single', 30.00),
(7,  'Butter',     'SCN004', 'Dairy',      'Milk',    'dairy & eggs', 55.00, 5.00, 60, 'pcs', '0000', 18.00, 1, 1, 1, 'single', 55.00),
(8,  'Tomato',     'SCN005', 'Vegetables', 'General', 'produce',   39.20, 0.00,  12,  'kg',  '0000', 18.00, 1, 3, 8, 'single', 39.20),
(9,  'Banana',     'SCN006', 'Fruits',     'Tropical','produce',   25.00, 0.00,  40,  'pcs', '0000', 18.00, 1, 4, 10, 'single', 25.00),
(10, 'Orange',     'SCN007', 'Fruits',     'Citrus',  'produce',   80.00, 10.00, 30,  'kg',  '0000', 18.00, 1, 4, 9, 'single', 80.00),
(14, 'Apple',      'APPLE',  'Fruits',     'General', 'produce',  315.00, 10.00, 12,  'kg',  '0000', 18.00, 1, 4, 11, 'single', 350.00);

-- Product variants
INSERT INTO public.product_variants (variant_id, product_id, variant_label, variant_value, variant_unit_type, variant_unit, variant_mrp, variant_discount_percent, variant_price, variant_quantity, variant_final_price, is_default) VALUES
(1,  4,  '1 kg',   1.000, 'Weight', 'kg',  65.00,  5.00, 60.00,  100, 60.00,  true),
(2,  5,  '1 kg',   1.000, 'Weight', 'kg',  47.00,  2.00, 45.00,  80,  45.00,  true),
(3,  6,  '500 ml', 500.000, 'Volume','ml', 30.00,  0.00, 30.00,  50,  30.00,  true),
(4,  7,  '200 g',  200.000, 'Weight','g',  58.00,  5.00, 55.00,  60,  55.00,  true),
(5,  8,  '1 kg',   1.000, 'Weight', 'kg',  40.00,  0.00, 39.20,  12,  39.20,  true),
(6,  9,  'Dozen',  12.000, 'Count', 'dozen',28.00, 0.00, 25.00,  40,  25.00,  true),
(7,  10, '1 kg',   1.000, 'Weight', 'kg',  90.00, 10.00, 80.00,  30,  80.00,  true),
(10, 14, '1 kg',   1.000, 'Weight', 'kg', 350.00, 10.00,315.00,  12, 315.00,  true);

-- Shops (mirrors branches)
INSERT INTO public.shops (shop_id, shop_name, shop_address) VALUES
(1, 'Main Branch', 'Chennai'),
(2, 'another one', 'Chennai'),
(3, 'Second Branch', 'Coimbatore');

-- City & Area master
INSERT INTO public.city_master (city_id, city_name) VALUES
(1, 'Dubai'), (2, 'Abu Dhabi'), (3, 'Sharjah');

INSERT INTO public.area_master (area_id, city_id, area_name, delivery_available, standard_delivery, paid_delivery, delivery_charge, estimated_standard_days, estimated_paid_days) VALUES
(1, 1, 'Deira', true, true, true, 15.00, 2, 1),
(2, 1, 'Bur Dubai', true, true, false, 0.00, 2, 0),
(3, 2, 'Mussafah', true, true, true, 20.00, 3, 1);
