isocroft/mid_budget_ecommerce_storefront_plus_backoffice_app_design_database.sql

## mid_budget_ecommerce_storefront_plus_backoffice_app_design_database.sql
-- MySQL v8.0.16
-- PostgresSQL v16.9.2

CREATE DATABASE IF NOT EXISTS `test`
DEFAULT CHARACTER SET utf8 -- utf8mb4
DEFAULT COLLATE utf8_general_ci;  -- utf8mb4_unicode_ci

SET default_storage_engine = INNODB;

CREATE TABLE organizations (
	id bigint NOT NULL,
	company_name varchar(150) NOT NULL,
	company_logo_url varchar(130), -- start with value of NULL and use background job to upload image to `cloudinary` OR `aws-s3` an then later write url value back
	industry enum('education', 'financial-services', 'legal', 'manufacturing', 'health', 'food-processing', 'agro-materials', 'fashion-design + tailoring') NOT NULL,
	company_type enum('sole-proprietor_SME', 'corporation_STARTUP', 'sole-proprietor_SB', 'corporation_BIG_FIRM'),
	registration_number char(11) NOT NULL,
	registration_country_code enum('NG', 'US', 'KE', 'GB') NOT NULL,
	verified_by enum('premble', 'ondato', 'idenfy', 'verifyme') NOT NULL,
	verified_at datetime NOT NULL,
	PRIMARY KEY (id)
);

CREATE TABLE organization_teams (
	id bigint NOT NULL,
	tenant_id bigint NOT NULL,
	email varchar(50) NOT NULL UNIQUE,
	role enum('manager', 'assistant', 'support') NOT NULL,
  	PRIMARY KEY (id),
  	FOREIGN KEY (tenant_id) REFERENCES organizations (id) ON DELETE CASCADE
);

CREATE TABLE organization_team_accounts (
	id bigint NOT NULL,
	email varchar(50) NOT NULL UNIQUE, -- data redundancy for easier access
	password varchar(36) NOT NULL,
	profile_avatar_url varchar(130), -- start with value of NULL and use background job to upload image to `cloudinary` OR `aws-s3` an then later write url value back
	tenant_id bigint NOT NULL,
	PRIMARY KEY (id),
	FOREIGN KEY (id) REFERENCES organization_teams (id) ON DELETE CASCADE,
  	FOREIGN KEY (tenant_id) REFERENCES organizations (id) ON DELETE CASCADE
);

CREATE TABLE organization_team_roles (
	id bigint NOT NULL,
	role_name varchar(50) UNIQUE NOT NULL,
	PRIMARY KEY (id)
);

CREATE TABLE organization_team_permissions (
	id bigint NOT NULL,
	permission_name varchar(100) UNIQUE NOT NULL,
	description text,
	PRIMARY KEY (id)
);

CREATE TABLE organization_team_account_roles (
	account_id bigint NOT NULL,
	role_id bigint NOT NULL,
	PRIMARY KEY (account_id, role_id),
	FOREIGN KEY (account_id) REFERENCES organization_team_accounts (id) ON DELETE CASCADE,
	FOREIGN KEY (role_id) REFERENCES organization_team_roles (id) ON DELETE CASCADE
);

CREATE TABLE organization_team_role_permissions (
	role_id bigint NOT NULL,
	permission_id bigint NOT NULL,
	PRIMARY KEY (role_id, permission_id),
	FOREIGN KEY (role_id) REFERENCES organization_team_roles (id) ON DELETE CASCADE,
	FOREIGN KEY (permission_id) REFERENCES organization_team_permissions (id) ON DELETE CASCADE
);

CREATE TABLE shoppers (
	id bigint NOT NULL,
    first_name varchar(100) NOT NULL,
    last_name varchar(100) NOT NULL,
    profile_avatar_url varchar(130), -- start with value of NULL and use background job to upload image to `cloudinary` OR `aws-s3` an then later write url value back
    gender enum('male', 'female') NOT NULL,
    date_of_birth date NOT NULL,
    registered_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT riderunq UNIQUE (id, first_name, last_name, date_of_birth),
    PRIMARY KEY (id)
);

CREATE TABLE shopper_accounts (
	id bigint NOT NULL,
	primary_email varchar(50) NOT NULL, -- It is possible to have the same shopper shopping at 2 or more diferent tenants
	full_name varchar(150) NOT NULL, -- data redundancy for easier access
	password varchar(36) NOT NULL,
	last_login_at datetime,
  	is_active tinyint(1) NOT NULL DEFAULT 1,
	email_verified tinyint(1) NOT NULL DEFAULT 0,
	tenant_id bigint NOT NULL,
	phone_number varchar(16) UNIQUE NULL,
	referral_shareable_code char(9) NOT NULL,
  	referrals_count int NOT NULL,
	PRIMARY KEY (id),
	FOREIGN KEY (id) REFERENCES shoppers (id) ON DELETE CASCADE,
  	FOREIGN KEY (tenant_id) REFERENCES organizations (id) ON DELETE CASCADE
);

CREATE TABLE customer_emails (
	id bignint NOT NULL,
	email varchar(50) NOT NULL UNIQUE,
	is_primary tinyint(1) NOT NULL DEFAULT 0,
	PRIMARY KEY (id)
);

-- Pivot table
CREATE TABLE shopper_emails (
	email_id bignint NOT NULL,
	tenant_id bigint NOT NULL,
	shopper_id bignint NOT NULL,
	PRIMARY KEY (shopper_id, email_id),
	FOREIGN KEY (shopper_id) REFERENCES shoppers (id) ON DELETE CASCADE,
	FOREIGN KEY (tenant_id) REFERENCES organizations (id) ON DELETE CASCADE,
	FOREIGN KEY (email_id) REFERENCES customer_emails (id) ON DELETE CASCADE
);

-- System table (doesn't have a `tenant_id` column because this table is populated by the SaaS provider and use by all tenants)
CREATE TABLE world_address_countries (
	id bigint NOT NULL,
	name char('2') NOT NULL,
	PRIMARY KEY (id)
);

-- System table (doesn't have a `tenant_id` column because this table is populated by the SaaS provider and use by all tenants)
CREATE TABLE world_address_states (
	id bigint NOT NULL,
	name char('2') NOT NULL,
	country_id bigint NOT NULL,
	country_name char('2') NOT NULL,
	PRIMARY KEY (id),
	FOREIGN KEY (country_id) REFERENCES world_address_countries (id) ON DELETE CASCADE
);

-- System table (doesn't have a `tenant_id` column because this table is populated by the SaaS provider and use by all tenants)
CREATE TABLE world_location_addresses (
	id bigint NOT NULL,
	street_name varchar(150) NOT NULL,
	house_number char(6),
	landmarks json NOT NULL DEFAULT (JSON_ARRAY()),
	location_latlng point,
	city_town varchar(60) NOT NULL,
	state_id bigint NOT NULL,
	zipcode char(9),
	country_id bigint NOT NULL,
	device_id varchar(110) UNIQUE NULL, -- either a moblie device id or a browser fingerprint or NULL
	PRIMARY KEY (id),
	FOREIGN KEY (state_id) REFERENCES world_address_states (id) ON DELETE CASCADE,
	FOREIGN KEY (country_id) REFERENCES world_address_countries (id) ON DELETE CASCADE
);

-- Pivot table
CREATE TABLE shopper_shipping_addresses (
	shopper_id bigint NOT NULL,
	tenant_id bigint NOT NULL,
	world_location_address_id NOT NULL,
	PRIMARY KEY (shopper_id, world_location_address_id),
	FOREIGN KEY (shopper_id) REFERENCES shoppers (id) ON DELETE CASCADE,
	FOREIGN KEY (tenant_id) REFERENCES organizations (id) ON DELETE CASCADE,
	FOREIGN KEY (world_location_address_id) REFERENCES world_location_addresses (id) ON DELETE CASCADE
);

CREATE TABLE tenant_office_addresses (
	tenant_id bigint NOT NULL,
	world_location_address_id NOT NULL,
	PRIMARY KEY (tenant, world_location_address_id),
	FOREIGN KEY (tenant_id) REFERENCES organizations (id) ON DELETE CASCADE,
	FOREIGN KEY (world_location_address_id) REFERENCES world_location_addresses (id) ON DELETE CASCADE
);

CREATE TABLE referral_rules (
  id bigint NOT NULL,
  rule_valid_from_time datetime NOT NULL,
  rule_valid_to_time datetime NOT NULL,
  credit_for_referrer_user int,
  credit_for_referred_user int,
  credit_kickback_to_referrer number,
  credit_kickback_referred_spend_minimum number,
  PRIMARY KEY (id),
  FOREIGN KEY (tenant_id) REFERENCES organizations (id) ON DELETE CASCADE
);

-- Pivot table
CREATE TABLE referral_manifest (
  referrer_user_id bigint NOT NULL,
  referred_user_id bigint NOT NULL,
  used_at datetime NOT NULL,
  tenant_id bigint NOT NULL,
  rule_id bigint NOT NULL,
  PRIMARY KEY (referrer_user_id, referred_user_id),
  FOREIGN KEY (referrer_user_id) REFERENCES shopper_accounts (id) ON DELETE CASCADE,
  FOREIGN KEY (referred_user_id) REFERENCES shopper_accounts (id) ON DELETE CASCADE,
  FOREIGN KEY (rule_id) REFERENCES referral_rules (id) ON DELETE CASCADE,
  FOREIGN KEY (tenant_id) REFERENCES organizations (id) ON DELETE CASCADE
);

CREATE TABLE shopping_carts (
	id bigint NOT NULL,
	cart_reference varchar(80) NOT NULL UNIQUE, -- cuid v2 ids
    shopper_id bigint, -- NULL if guest user else logged-in user id
	status enum('picked', 'in-use', 'abandoned', 'returned') NOT NULL DEFAULT 'picked', -- all shopping carts will be deleted after 48hrs from `created_at` if they still have an 'picked' status. The shopping cart status is set to 'returned' after you pay for the order associated with the cart
	tenant_id bigint NOT NULL,
	device_id varchar(110) NOT NULL UNIQUE, -- either a mobile device id or a browser fingerprint (this goes into a HTTP cookie that lasts 48hrs from `created_at`)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (id),
	FOREIGN KEY (shopper_id) REFERENCES shoppers (id) ON DELETE CASCADE,
	FOREIGN KEY (tenant_id) REFERENCES organizations (id) ON DELETE CASCADE
);

CREATE TABLE orders (
	id bigint NOT NULL,
	customer_id bigint NOT NULL,
	tenant_id bigint NOT NULL,
	checkout_status_started tinyint(1) NOT NULL DEFAULT 1,
	checkout_status_completed tinyint(1) NOT NULL DEFAULT 0,
	checkout_status_abandoned tinyint(1) NOT NULL DEFAULT 0,
	PRIMARY KEY (id),
	FOREIGN KEY (customer_id) REFERENCES shoppers (id) ON DELETE CASCADE,
	FOREIGN KEY (tenant_id) REFERENCES organizations (id) ON DELETE CASCADE
);

CREATE TABLE order_invoices (
	id bigint NOT NULL,
	payer_id bigint NOT NULL,
	tenant_id bigint NOT NULL,
	total_fee_amount_paid decimal(10, 2) NOT NULL DEFAULT 0.00, -- will only be populated when the shopper actually pays
	total_tax_amount_paid decimal(10, 2) NOT NULL DEFAULT 0.00, -- will only be populated when the shopper actually pays
	due_date date NOT NULL, -- `due_date` is set 4 days from `created_at`
	note text,
	-- example `order_items`: { "item_id_[`product_stock_id`]": { "quantity": 1, "item_total": 62000.00, "product_variant_id": 2222 } }
	order_items json NOT NULL DEFAULT (JSON_OBJECT()), -- `order_items` is an empty JSON object intially until after payment for the order has been made then we populate it with cart items with their `quantity` and `item_total`
	payment_status_paid tinyint(1) NOT NULL DEFAULT 0,
	payment_status_unpaid tinyint(1) NOT NULL DEFAULT 1,
	payment_status_retried tinyint(1) NOT NULL DEFAULT 0,
	payment_status_failed tintyint(1) NOT NULL DEFAULT 0,
	created_at date NOT NULL,
	PRIMARY KEY (id),
	FOREIGN KEY (payer_id) REFERENCES shoppers (id) ON DELETE CASCADE,
	FOREIGN KEY (tenant_id) REFERENCES organizations (id) ON DELETE CASCADE
);

-- Invoice Statuses:
-- =================
-- 1. It can be 'unpaid' & 'failed'; meaning the shopper triedd to pay before but failed while not past the due date
-- 2. It can be 'overdue' & 'failed'; meaning the shopper tried to pay before but failed and its past the due date
-- 3. It can be 'paid' & 'retried'; meaning the shopper tried more than once to pay before succeeding while not past the due date

ALTER TABLE order_invoices ADD CONSTRAINT chk_order_invoices_pay_stats_paid CHECK (payment_status_unpaid <> 1 AND (payment_status_paid = 0 OR payment_status_paid = 1));

ALTER TABLE order_invoices ADD CONSTRAINT chk_order_invoices_pay_stats_retried CHECK (payment_status_failed <> 1 AND (payment_status_retried = 0 OR payment_status_retried = 1));

ALTER TABLE order_invoices ADD CONSTRAINT chk_order_invoices_pay_stats_paid_retried CHECK (payment_status_paid = 1 AND (payment_status_retried = 0 OR payment_status_retried = 1));

ALTER TABLE order_invoices ADD CONSTRAINT chk_order_invoices_pay_stats_unpaid_failed CHECK (payment_status_unpaid = 1 AND payment_status_paid = 0 AND payment_status_failed = 1);

ALTER TABLE order_invoices ADD CONSTRAINT chk_order_invoices_pay_stats_overdue_paid CHECK (due_date < CURRENT_DATE() AND payment_status_paid = 1 AND payment_status_unpaid = 0);

-- System table (doesn't have a `tenant_id` column because this table is populated by the SaaS provider and use by all tenants)
CREATE TABLE order_shipment_transit_delivery_methods (
	id bigint NOT NULL,
	transit_option enum('bike', 'truck', 'van', 'bicycle', 'shipping_vessel') NOT NULL,
	transit_type enum('last-mile', 'middle-mile', 'white-glove') NOT NULL,
	transit_period enum('same-day', 'express-overnight', 'standard') NOT NULL,
	PRIMARY KEY (id)
);

-- System table (doesn't have a `tenant_id` column because this table is populated by the SaaS provider and use by all tenants)
CREATE TABLE order_shipment_transit_carriers (
	id bigint NOT NULL,
	carrier_name varchar(70) NOT NULL, -- e.g. FedEx, UPS, USPS, DHL, KwikDelivery
	carrier_foul_up_rate char(4) NOT NULL DEFAULT '0%',
	PRIMARY KEY (id)
);

-- Pivot table
CREATE TABLE order_shipment_transit_carrier_delivery_methods (
	carried_id bigint NOT NULL,
	delivery_method_id bigint NOT NULL,
	PRIMARY KEY (carried_id, delivery_method_id),
	FOREIGN KEY (carrier_id) REFERENCES order_shipment_transit_carriers (id) ON DELETE CASCADE,
	FOREIGN KEY (delivery_method_id) REFERENCES order_shipment_transit_delivery_methods (id) ON DELETE CASCADE
);

-- System table (doesn't have a `tenant_id` column because this table is populated by the SaaS provider and use by all tenants)
CREATE TABLE order_shipment_transit_carrier_stops (
	id bigint NOT NULL,
	carrier_id bigint NOT NULL,
	type enum('consignor_drop-off', 'transit_drop-off') NOT NULL,
	stop_location_address text,
	PRIMARY KEY (id),
	FOREIGN KEY (carrier_id) REFERENCES order_shipment_transit_carriers (id) ON DELETE CASCADE
);

CREATE TABLE installed_order_shipment_transit_carriers (
	id bigint NOT NULL,
	tenant_id bigint NOT NULL,
	carried_id bigint NOT NULL,
	PRIMARY KEY (id),
	FOREIGN KEY (carrier_id) REFERENCES order_shipment_transit_carriers (id) ON DELETE CASCADE,
	FOREIGN KEY (tenant_id) REFERENCES organizations (id) ON DELETE CASCADE
);

CREATE TABLE order_shipping_log (
	id bigint NOT NULL,
	total_fee_amount_paid decimal(10, 2) NOT NULL DEFAULT 0.00, -- will only be populated when the shopper actually pays
	total_tax_amount_paid decimal(10, 2) NOT NULL DEFAULT 0.00, -- will only be populated when the shopper actually pays
	tenant_id bigint NOT NULL,
	consignee_id bigint NOT NULL,
	tracking_number varchar(100), -- The tracking number is unknown and only generated after payment for the order has been made hence it is NULL initially
	shipment_status_shipped tinyint(1) NOT NULL DEFAULT 0,
	shipment_status_in_transit tinyint(1) NOT NULL DEFAULT 0,
	shipment_status_delivered tinyint(1) NOT NULL DEFAULT 0,
	shipment_status_delayed tinyint(1) NOT NULL DEFAULT 0,
	PRIMARY KEY (id),
	FOREIGN KEY (consignee_id) REFERENCES shoppers (id) ON DELETE CASCADE,
	FOREIGN KEY (tenant_id) REFERENCES organizations (id) ON DELETE CASCADE
);

-- Shipment Statuses:
-- ==================
-- 1. It can be 'shipped' & 'in-transit'; meaning the shipment has left the fulfilment center and is on the move to the shopper for delivery
-- 2. It can be 'shipped' & 'delayed'; meaning the shipment has left the fufilment center but is stuck somewhere and not on the move for delivery
-- 3. It can be 'shipped' & 'delivered'; meaning the shipment has left the fufilment center and has reached its' destination and delivered to the shopper

ALTER TABLE order_shipping_log ADD CONSTRAINT chk_order_shipping_log_ship_stats_delivrd CHECK (shipment_status_delayed <> 1 AND (shipment_status_delivered = 1 OR shipment_status_delivered = 0));

ALTER TABLE order_shipping_log ADD CONSTRAINT chk_order_shipping_log_ship_stats_intrnst CHECK (shipment_status_delivered <> 1 AND (shipment_status_in_transit = 1 OR shipment_status_in_transit = 0));

ALTER TABLE order_shipping_log ADD CONSTRAINT chk_order_shipping_log_ship_stats_delayd CHECK (shipment_status_in_transit <> 1 AND (shipment_status_delayed = 0 OR shipment_status_delayed = 1));

ALTER TABLE order_shipping_log ADD CONSTRAINT chk_order_shipping_log_ship_stats_intrnst_shipd CHECK (shipment_status_in_transit = 1 AND shipment_status_delayed = 0 AND shipment_status_shipped = 1);

ALTER TABLE order_shipping_log ADD CONSTRAINT chk_order_shipping_log_ship_stats_delivrd_shipd CHECK (shipment_status_delivered = 1 AND shipment_status_in_transit = 0 AND shipment_status_shipped = 1);

ALTER TABLE order_shipping_log ADD CONSTRAINT chk_order_shipping_log_ship_stats_delayd_shipd CHECK (shipment_status_delayed = 1 AND (shipment_status_shipped = 0 OR shipment_status_shipped = 1));


CREATE TABLE order_shipment_transit_manifest (
	shipping_log_id bigint NOT NULL,
	shipment_pickup_date datetime NOT NULL,
	shipment_dropoff_date datetime NOT NULL,
	shipment_carrier_ingress_stop_id bigint NOT NULL,
	shipment_carrier_id bigint NOT NULL,
	tenant_id bigint NOT NULL,
	shipment_transit_delivery_method_id bigint NOT NULL,
	shipment_carrier_stop_ordering enum('first', 'intermediate', 'last') NOT NULL,
	requires_consignee_dropoff tinyint(1) NOT NULL DEFAULT 0,
	is_consignee_dropoff tinyint(1) NOT NULL DEFAULT 0,
	shipment_carrier_stop_status_details json NOT NULL DEFAULT (JSON_OBJECT()),
	PRIMARY KEY (shipping_log_id, shipment_carrier_id, shipment_carrier_ingress_stop_id),
	FOREIGN KEY (shipping_log_id) REFERENCES order_shipping_log (id) ON DELETE CASCADE,
	FOREIGN KEY (shipment_carrier_id) REFERENCES order_shipment_transit_carriers (id) ON DELETE NO ACTION,
	FOREIGN KEY (shipment_carrier_ingress_stop_id) REFERENCES order_shipment_transit_carrier_stops (id) ON DELETE NO ACTION,
	FOREIGN KEY (shipment_transit_delivery_method_id) REFERENCES order_shipment_transit_delivery_methods (id) ON DELETE NO ACTION,
	FOREIGN KEY (tenant_id) REFERENCES organizations (id) ON DELETE CASCADE
);

ALTER TABLE order_shipment_transit_manifest ADD CONSTRAINT chk_stop_ord_first_dropoff CHECK (shipment_carrier_stop_ordering = 'first' AND requires_consignee_dropoff = 0 AND is_consignee_dropoff = 0); -- The first stop cannot require consignee drop-off

ALTER TABLE order_shipment_transit_manifest ADD CONSTRAINT chk_stop_ord_intmd_dropoff CHECK (shipment_carrier_stop_ordering = 'intermediate' AND is_consignee_dropoff = 0); -- if it requires a consignee drop-off, then it has to be an intermediate stop!

ALTER TABLE order_shipment_transit_manifest ADD CONSTRAINT chk_stop_ord_intmd_dropoff CHECK (shipment_carrier_stop_ordering = 'last' AND requires_consignee_dropoff = 0); -- The last stop cannot require consignee drop-off

CREATE TABLE order_basket_transactions (
	invoice_id bigint NOT NULL UNIQUE,
	order_id bigint NOT NULL UNIQUE,
	shopping_cart_id bigint NOT NULL UNIQUE,
	payment_method enum('paypal', 'stripe', 'paystack', 'cash', 'bank-transfer', 'card', '-') NOT NULL DEFAULT '-',
	pay_on_delivery tinyint(1) NOT NULL DEFAULT 0,
	currency enum('NGN', 'USD', 'KSH', 'GBP') NOT NULL,
	shipment_start_date datetime, -- The start date of the shipment is unknown until ater payment for the order has been made hence it is NULL initially
	shipment_end_date datetime, -- The end date of the shipment is unknown until ater payment for the order has been made hence it is NULL initially
	shipment_carrier_stops_count int, -- knowing how many stops we have for a shipment helps to caculate the shipment statuses over time
	tenant_id bigint NOT NULL,
	shipping_log_id bigint NOT NULL UNIQUE,
	shopper_address_id bigint NOT NULL,
	PRIMARY KEY (order_id, shopper_id, invoice_id),
	FOREIGN KEY (invoice_id) REFERENCES order_invoices (id) ON DELETE NO ACTION,
	FOREIGN KEY (shopper_address_id) REFERENCES world_location_addresses (id) ON DELETE NO ACTION,
	FOREIGN KEY (shopper_id) REFERENCES shoppers (id) ON DELETE CASCADE,
	FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE,
	FOREIGN KEY (tenant_id) REFERENCES organizations (id) ON DELETE CASCADE,
	FOREIGN KEY (shipping_log_id) REFERENCES order_shipping_log (id) ON DELETE CASCADE
);

ALTER TABLE order_basket_transactions ADD CONSTRAINT chk_pay_methd_on_delvry CHECK (pay_on_delivery = 1 AND (payment_method = 'cash' OR payment_method = 'bank-transfer' payment_method = 'card'));

CREATE TABLE inventory_product_stock_manufacturers (
	id bigint NOT NULL,
	name varchar(90) NOT NULL,
	tenant_id bigint NOT NULL,
	industry enum('electronics', 'upholstery', 'food_processing', 'fashion_wears') NOT NULL,
	PRIMARY KEY (id),
	FOREIGN KEY (tenant_id) REFERENCES organizations (id) ON DELETE CASCADE
);

CREATE TABLE inventory_products (
  	id bigint NOT NULL,
  	unit_name varchar(130) NOT NULL,
	unit_description text,
	tenant_id bigint NOT NULL,
	unit_image_url varchar(190), -- start with NULL value and use a background job to upload image to `cloudinary` OR `aws-s3` then later write back url here
	refundable_on_return tinyint(1)  NOT NULL DEFAULT 0,
	PRIMARY KEY (id),
	FOREIGN KEY (tenant_id) REFERENCES organizations (id) ON DELETE CASCADE
);

-- Pivot table
CREATE TABLE shopper_wishlists (
    id bigint NOT NULL,
    shopper_id bigint NOT NULL,
	tenant_id bigint NOT NULL,
    wishlist_name varchar(90) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (id),
    FOREIGN KEY (shopper_id) REFERENCES shoppers (id) ON DELETE CASCADE,
	FOREIGN KEY (tenant_id) REFERENCES organizations (id) ON DELETE CASCADE
);

-- Pivot table
CREATE TABLE shoppers_wishlist_items (
    id bigint NOT NULL,
    wishlist_id bigint NOT NULL,
    product_id bigint NOT NULL,
	tenant_id bigint NOT NULL,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (id),
    FOREIGN KEY (wishlist_id) REFERENCES shopper_wishlists (id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES inventory_products (id) ON DELETE CASCADE,
	FOREIGN KEY (tenant_id) REFERENCES organizations (id) ON DELETE CASCADE,
    UNIQUE (wishlist_id, product_id) -- ensures no duplicate products in a single wishlist
);

CREATE TABLE inventory_categories (
	id bigint NOT NULL,
	tenant_id bigint NOT NULL,
	name varchar(160) NOT NULL,
	description text,
	parent_category_id bigint, -- for hierarchical categories (self-referencing foreign key)
  	PRIMARY KEY (id),
  	FOREIGN KEY (tenant_id) REFERENCES organizations (id) ON DELETE CASCADE,
	FOREIGN KEY (parent_category_id) REFERENCES inventory_categories (id) ON DELETE NO ACTION,
);

-- Pivot table
CREATE TABLE inventory_catalog_product_categories (
	product_id bigint NOT NULL,
	category_id bigint NOT NULL,
	tenant_id bigint NOT NULL,
	PRIMARY KEY (product_id, category_id),
	FOREIGN KEY (product_id) REFERENCES inventory_products (id) ON DELETE CASCADE,
	FOREIGN KEY (category_id) REFERENCES inventory_categories (id) ON DELETE NO ACTION,
	FOREIGN KEY (tenant_id) REFERENCES organizations (id) ON DELETE CASCADE
);

CREATE TABLE inventory_product_stock_variants (
	id bigint NOT NULL,
	-- upc_format varchar(16), -- e.g. 'XXXXXX-XXXXX-X' (Universal Product Code)
	sku_format varchar(210), -- e.g. '[Style]-[Color]-[Size]-[Material]-[Brand]-[Weight]-[Collection Month]-[Collection Year]' (Stock Keeping Unit)
	varaint_attributes json NOT NULL DEFAULT (JSON_OBJECT()), -- e.g. '{ "sizes", ["xl", "l", "sm"], "colors": ["red", "pink"], "styles": ["jumpsuit", "pina-fore"], "brands": ["nhn_couture", "house_of_josh"], "materials": ["spandex", "cotton"], "units_of_measure": ["kg", "g"], "weights": ["130 grams", "70 grams"], "collection_months_plus_years": ["October 2024", "February 2025"] }'
	brand_id bigint NOT NULL,
	tenant_id bigint NOT NULL,
	variant_image_urls json NOT NULL DEFAULT (JSON_ARRAY()), -- start with empty JSON array value and use a background job to upload images to `cloudinary` OR `aws-s3` then later write back urls here
	category_id bigint NOT NULL, -- data redundancy for easy access purposes
	PRIMARY KEY (id)
	FOREIGN KEY (brand_id) REFERENCES inventory_product_stock_manufacturers (id) ON DELETE CASCADE,
	FOREIGN KEY (category_id) REFERENCES inventory_categories (id) ON DELETE NO ACTION,
	FOREIGN KEY (tenant_id) REFERENCES organizations (id) ON DELETE CASCADE
);

CREATE TABLE inventory_product_stock_manifest (
	id bigint NOT NULL,
	stock_units_total_stored bigint NOT NULL DEFAULT 0, -- PN Counter modelling (from CRDTs)
	stock_units_total_sold bigint NOT NULL DEFAULT 0, -- PN Counter modelling (from CRDTs)
	unit_price_amount bigint NOT NULL DEFAULT 0, -- assume it's free hence price = 0
	unit_discount_price_amount bigint  NOT NULL DEFAULT 0, -- assume theres' no discount hence discount_price = 0
	unit_price_currency enum('NGN-kobo', 'USD-cents', 'GBP-shillings') NOT NULL,
	tenant_id bigint NOT NULL,
	product_id bigint NOT NULL UNIQUE,
	product_variant_id bigint NOT NULL UNIQUE,
	stock_units_min_count_limit bigint NOT NULL DEFAULT 0,
	stock_units_max_count_limit bigint NOT NULL,
	unit_discount_price_amount_updated_at datetime NOT NULL,
	stock_available_count_updated_at datetime NOT NULL, -- this column is updated only when either `stock_units_total_stored` or `stock_units_total_sold` column is updated
	PRIMARY KEY (id),
	FOREIGN KEY (product_id) REFERENCES inventory_products (id) ON DELETE CASCADE,
	FOREIGN KEY (product_variant_id) REFERENCES inventory_product_stock_variants (id) ON DELETE NO ACTION,
	FOREIGN KEY (tenant_id) REFERENCES organizations (id) ON DELETE CASCADE
);

ALTER TABLE inventory_product_stock_manifest ADD CONSTRAINT chk_stock_stored_sold CHECK (stock_unit_total_sold < stock_unit_total_stored);

ALTER TABLE inventory_product_stock_manifest ADD CONSTRAINT chk_stock_min_max_limits CHECK (stock_units_min_count_limit <= stock_units_max_count_limit);

CREATE TABLE shopping_cart_items (
    id bigint NOT NULL,
    shopping_cart_id bigint NOT NULL,
    product_stock_id bigint NOT NULL,
    item_quantity int NOT NULL DEFAULT 0,
	item_total decimal(10, 2) NOT NULL DEFAULT 0.00,
	item_description text,
	tenant_id bigint NOT NULL,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (id),
    FOREIGN KEY (shopping_cart_id) REFERENCES shopping_carts (id) ON DELETE CASCADE,
    FOREIGN KEY (product_stock_id) REFERENCES inventory_product_stock_manifest (id) ON DELETE CASCADE,
	FOREIGN KEY (tenant_id) REFERENCES organizations (id) ON DELETE CASCADE
);

CREATE TABLE inventory_stocking_managers (
	id bigint NOT NULL,
	first_name varchar(90) NOT NULL,
	last_name varchar(90) NOT NULL,
	tenant_id bigint NOT NULL,
	PRIMARY KEY (id),
	FOREIGN KEY (tenant_id) REFERENCES organizations (id) ON DELETE CASCADE
);

CREATE TABLE inventory_product_stock_suppliers (
	id bigint NOT NULL,
	supplier_name varchar(95) NOT NULL,
	contact_person_fullname varchar(182) NOT NULL,
	tenant_id bigint NOT NULL,
	contact_person_phone_number char(15) NOT NULL,
	contact_person_email varchar(50) NOT NULL,
	supplier_office_address_location json NOT NULL DEFAULT (JSON_OBJECT()), -- '{ "latitude": "XX", "longitude": "XX", "address": "xxxxx xxx" }'
	PRIMARY KEY (id),
	FOREIGN KEY (tenant_id) REFERENCES organizations (id) ON DELETE CASCADE
);

-- Pivot table
CREATE TABLE inventory_stock_supply_catalog_listings (
	supplier_id bigint NOT NULL,
	product_id bigint NOT NULL,
	category_id bigint NOT NULL, -- data redundancy for easier access
	stocking_manager_id bigint NOT NULL,
	restock_status_details json NOT NULL DEFAULT (JSON_OBJECT()),
	stock_unit_supply_count bigint NOT NULL,
	last_restock_date datetime NOT NULL,
	fufillment_center_address_location json NOT NULL DEFAULT (JSON_OBJECT()), -- '{ "latitude": "XX", "longitude": "XX", "address": "xxxxx xxx" }'
	tenant_id bigint NOT NULL,
	PRIMARY KEY (product_id, supplier_id),
	FOREIGN KEY (product_id) REFERENCES inventory_products (id) ON DELETE CASCADE,
	FOREIGN KEY (tenant_id) REFERENCES organizations (id) ON DELETE CASCADE,
   	FOREIGN KEY (stocking_manager_id) REFERENCES inventory_stocking_managers (id) ON DELETE NO ACTION,
	FOREIGN KEY (category_id) REFERENCES inventory_categories (id) ON DELETE NO ACTION
);

-- Pivot table
CREATE TABLE inventory_product_stock_movement_manifest (
	id bigint NOT NULL,
	product_id bigint NOT NULL,
	movement_direction enum('in', 'out') NOT NULL,
	quanity_change int NOT NULL,
	tenant_id bigint NOT NULL,
	movement_note text,
	movement_date datetime NOT NULL,
	recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (id),
	FOREIGN KEY (product_id) REFERENCES inventory_products (id) ON DELETE CASCADE,
	FOREIGN KEY (tenant_id) REFERENCES organizations (id) ON DELETE CASCADE
);

CREATE TABLE inventory_shipment_transit_delivery_methods (
	id bigint NOT NULL,
	transit_option enum('bike', 'truck', 'van', 'shipping_vessel') NOT NULL,
	transit_type enum('first-mile', 'white-glove') NOT NULL,
	transit_period enum('express-overnight', 'standard') NOT NULL,
	tenant_id bigint NOT NULL,
	PRIMARY KEY (id),
	FOREIGN KEY (tenant_id) REFERENCES organizations (id) ON DELETE CASCADE
);

-- 1. organizations (Base Tenant)
INSERT INTO organizations (id, company_name, company_logo_url, industry, company_type, registration_number, registration_country_code, verified_by, verified_at) VALUES
(100, 'Fashion Hub Nigeria Ltd', NULL, 'fashion-design + tailoring', 'corporation_BIG_FIRM', 'NG123456789', 'NG', 'premble', NOW()),
(101, 'Agro-Seeds Global', NULL, 'agro-materials', 'corporation_STARTUP', 'US987654321', 'US', 'ondato', NOW());

-- 2. world_address_countries (System table - Depends on organizations)
INSERT INTO world_address_countries (id, name) VALUES
(200, 'NG'), -- Nigeria
(201, 'US'); -- USA

-- 3. world_address_states (System table - Depends on world_address_countries)
INSERT INTO world_address_states (id, country_id, name, country_name) VALUES
(300, 200, 'LG', 'NG'), -- Lagos, Nigeria
(301, 201, 'NY', 'US'), -- New York, USA
(302, 200, 'ABJ', 'NG'); -- Abuja, Nigeria

-- 4. world_location_addresses (System + Tenant table - Depends on world_address_states, world_address_countries)
INSERT INTO world_location_addresses (id, street_name, house_number, landmarks, location_latlng, city_town, state_id, zipcode, country_id, device_id) VALUES
(400, 'Victoria Island Street', '5A', '["Near Bank Plaza", "Next to Ifesinachi Park"]', ST_GeomFromText('POINT(6.5244 3.3792)'), 'Ikeja', 300, '700568', 200, 'mobile-dev-id-001'),
(401, 'Helsinki Avenue', '101', '["Off 12th & 6th"]', ST_GeomFromText('POINT(40.7128 -74.0060)'), 'Harlem', 301, '10001', 201, 'browser-fingerprint-999'),
(402, 'Adelabu Crescent', 'H-38', '["Off Laide Street"]', ST_GeomFromText('POINT(96.6109 -51.0145)'), 'Garki', 302, NULL, 200, NULL); -- most likely a shipping carrier address

-- 5. shoppers (No external FKs, but needed for accounts/transactions)
INSERT INTO shoppers (id, first_name, last_name, profile_avatar_url, gender, date_of_birth, registered_at) VALUES
(500, 'Jane', 'Doe', 'https://res.cloudinary.com/shopper_avatars/image/upload/w_200,h_200/sample_1.jpg', 'female', '1995-03-20', NOW()),
(501, 'John', 'Smith', NULL, 'male', '1988-11-15', NOW()),
(502, 'Amaka', 'Uke', 'https://res.cloudinary.com/shopper_avatars/image/upload/w_200,h_200/sample_2.jpg', 'female', '1999-07-09', NOW());

-- 6. shopper_accounts (Depends on shoppers, organizations)
INSERT INTO shopper_accounts (id, primary_email, full_name, password, last_login_at, is_active, email_verified, tenant_id, referral_shareable_code, referrals_count, phone_number) VALUES
(500, 'shopper.jane@example.com', 'Jane Doe', 'hashed_pass_j1', NOW(), 1, 1, 100, 'JANE001AB', 1, NULL),
(501, 'shopper.john@example.com', 'John Smith', 'hashed_pass_j2', NULL, 1, 0, 100, 'JOHN999YZ', 0, NULL),
(502, 'shopper.amaka.uke@hotmail.com', 'Amaka Uke', 'hashed_pass_j3', NOW(), 1, 1, 100, 'AMAKA86UQ', 0, '+2348036689124');

-- 7. organization_teams (Depends on organizations)
INSERT INTO organization_teams (id, tenant_id, email, role) VALUES
(600, 100, 'manager.sam@fhn.com', 'manager'),
(601, 100, 'support.tina@fhn.com', 'support');

-- 8. organization_team_accounts (Depends on storefront_owner_teams, organizations)
INSERT INTO organization_team_accounts (id, email, password, profile_avatar_url, tenant_id) VALUES
(600, 'manager.sam@fhn.com', 'hashed_pass_m1', 'https://res.cloudinary.com/fhn-team_avatars/image/upload/w_220,h_220/headshot_1.jpg', 100),
(601, 'support.tina@fhn.com', 'hashed_pass_s1', 'https://res.cloudinary.com/fhn-team_avatars/image/upload/w_220,h_220/headshot_2.jpg', 100);

-- 9. shopper_shipping_addresses (Depends on shoppers, world_location_addresses)
INSERT INTO shopper_shipping_addresses (shopper_id, world_location_address_id) VALUES
(500, 400),
(501, 400);

-- 10. inventory_product_stock_manufacturers (Depends on organizations)
INSERT INTO inventory_product_stock_manufacturers (id, name, tenant_id, industry) VALUES
(700, 'NHN Couture', 100, 'fashion_wears'),
(701, 'House of Josh Designs', 100, 'fashion_wears');

-- 11. inventory_products (Depends on organizations)
INSERT INTO inventory_products (id, unit_name, unit_description, tenant_id, unit_image_url, refundable_on_return) VALUES
(800, 'Maxi Dress - Floral Print', 'A beautiful maxi dress with a vibrant floral pattern.', 100, 'https://res.cloudinary.com/product_unit_images/image/upload/maxi_floral_1.jpg', 1),
(801, 'Formal Suit - Men', 'Navy blue formal suit, slim fit.', 100, 'https://res.cloudinary.com/product_unit_images/image/upload/slimfit_suit_3.jpg', 1);

-- 12. inventory_categories (Depends on organizations, self-reference)
INSERT INTO inventory_categories (id, tenant_id, name, description, parent_category_id) VALUES
(900, 100, 'Dresses', 'One-piece & Multi-piece garments.', NULL),
(901, 100, 'Womenswear', 'Clothing for women.', 900),
(902, 100, 'Menswear', 'Clothing for men.', 900);

-- 13. inventory_catalog_product_categories (Depends on inventory_products, inventory_categories, organizations)
INSERT INTO inventory_catalog_product_categories (product_id, category_id, tenant_id) VALUES
(800, 900, 100),
(800, 901, 100),
(801, 902, 100);

-- 14. inventory_product_stock_variants (Depends on manufacturers, categories, organizations)
INSERT INTO inventory_product_stock_variants (id, sku_format, varaint_attributes, brand_id, tenant_id, variant_image_urls, category_id) VALUES
(1000, 'MD-RED-SM-COT-NHN', '{"sizes": ["sm", "lg"], "colors": ["red", "brown"], "styles": ["maxi", "slim-fit"], "brands": ["nhn_couture"], "materials": ["cotton"]}', 700, 100, '["https://res.cloudinary.com/product_variant_unit_images/image/upload/red_maxi_variant_1.jpg"]', 901),
(1001, 'FS-NVB-40-WOOL-JOSH', '{"sizes": ["40", "32"], "colors": ["navy_blue"], "styles": ["formal, "slim-fit"], "brands": ["house_of_josh"], "materials": ["wool"]}', 701, 100, '["https://res.cloudinary.com/product_variant_unit_images/image/upload/navy_blue_slimit_varaint_4.jpg"]', 902);

-- 15. inventory_product_stock_manifest (Depends on inventory_products, inventory_product_stock_variants, organizations)
INSERT INTO inventory_product_stock_manifest (id, stock_units_total_stored, stock_units_total_sold, unit_price_amount, unit_discount_price_amount, unit_price_currency, tenant_id, product_id, product_variant_id, stock_units_min_count_limit, stock_units_max_count_limit, unit_discount_price_amount_updated_at, stock_available_count_updated_at) VALUES
(1100, 50, 10, 1500000, 1200000, 'NGN-kobo', 100, 800, 1000, 5, 100, NOW(), NOW()), -- Maxi Dress (15000 NGN)
(1101, 20, 5, 4500000, 0, 'NGN-kobo', 100, 801, 1001, 2, 50, NOW(), NOW()); -- Formal Suit (45000 NGN)

-- 16. shopper_wishlists (Depends on shoppers, organizations)
INSERT INTO shopper_wishlists (id, shopper_id, tenant_id, wishlist_name, created_at, updated_at) VALUES
(1200, 500, 100, 'Summer Collection', NOW(), NOW());

-- 17. shoppers_wishlist_items (Depends on shopper_wishlists, inventory_products, organizations)
INSERT INTO shoppers_wishlist_items (id, wishlist_id, product_id, tenant_id, added_at) VALUES
(1300, 1200, 800, 100, NOW());

-- 18. shopping_carts (Depends on shoppers, organizations)
INSERT INTO shopping_carts (id, cart_reference, shopper_id, status, tenant_id, device_id, created_at, updated_at) VALUES
(1400, 'cuid-v2-ref-001', 500, 'in-use', 100, 'mobile-dev-id-001', NOW(), NOW()), -- Jane Doe's active cart
(1401, 'cuid-v2-ref-002', NULL, 'abandoned', 100, 'browser-fingerprint-999', DATE_SUB(NOW(), INTERVAL 5 DAY), DATE_SUB(NOW(), INTERVAL 5 DAY)); -- Guest cart, abandoned

-- 19. shopping_cart_items (Depends on shopping_carts, inventory_product_stock_manifest, organizations)
INSERT INTO shopping_cart_items (id, shopping_cart_id, product_stock_id, item_quantity, item_total, item_description, tenant_id, added_at) VALUES
(1500, 1400, 1100, 1, 0.00, 'Maxi Dress in Red, Size Small.', 100, NOW()); -- 1 of Maxi Dress (price in full currency for item_total for simplicity, though stock manifest uses sub-units)

-- 20. order_shipment_transit_delivery_methods (System table)
INSERT INTO order_shipment_transit_delivery_methods (id, transit_option, transit_type, transit_period) VALUES
(1600, 'bike', 'last-mile', 'same-day'),
(1601, 'van', 'last-mile', 'standard'),
(1602, 'truck', 'middle-mile', 'standard'),
(1603, 'truck', 'middle-mile', 'express-overnight'),
(1604, 'truck', 'first-mile', 'standard')
(1605, 'truck', 'white-glove', 'standard');

-- 21. order_shipment_transit_carriers (System table)
INSERT INTO order_shipment_transit_carriers (id, carrier_name, carrier_foul_up_rate) VALUES
(1700, 'KwikDelivery', '3%'),
(1701, 'FedEx', '1%'),
(1702, 'DHL', '2%');

-- 22. order_shipment_transit_carrier_stops (System table)
INSERT INTO order_shipment_transit_carrier_stops (id, carrier_id, type, stop_location_address) VALUES
(1800, 1702, 'consignor_drop-off', 'xxxxxxxxxx'), -- DHL picks-up consignments up from fulfilment center
(1801, 1700, 'transit_drop-off', 'oooooooooo'); -- KwikDelivery drops-off consignments to their collection center

-- 23. order_shipment_transit_carrier_delivery_methods (System table)
INSERT INTO order_shipment_transit_carrier_delivery_methods (carried_id, delivery_method_id) VALUES
(1700, 1601), -- KwikDelivery is a last mile delivery service
(1700, 1600), -- KwikDelivery is a last mile delivery service
(1702, 1602), -- DHL is a first-mile & middle-mile delivery service
(1702, 1603),
(1702, 1604);

-- 24. referral_rules (Depends on organizations)
INSERT INTO referral_rules (id, rule_valid_from_time, rule_valid_to_time, credit_for_referrer_user, credit_for_referred_user, credit_kickback_to_referrer, credit_kickback_referred_spend_minimum, tenant_id) VALUES
(1900, '2025-12-10 00:00:00', '2026-01-10 00:00:00', 500, 1000, 0.1, 50.00, 100);

-- 25. referral_manifest (Depends on shopper_accounts, referral_rules, organizations)
INSERT INTO referral_manifest (referrer_user_id, referred_user_id, used_at, tenant_id, rule_id) VALUES
(500, 502, NOW(), 100, 1900); -- Jane referred Amaka by giving Amaka her own referral code 'JANE001AB'

-- 26. orders (Depends on shoppers, organizations)
INSERT INTO orders (id, customer_id, tenant_id, checkout_status_started, checkout_status_completed, checkout_status_abandoned) VALUES
(2000, 500, 100, 0, 1, 0); -- Jane Doe's completed order

-- 27. order_invoices (Depends on shoppers, organizations)
INSERT INTO order_invoices (id, payer_id, tenant_id, total_fee_amount_paid, total_tax_amount_paid, due_date, note, order_items, payment_status_paid, payment_status_unpaid, payment_status_retried, payment_status_failed, created_at) VALUES
(2100, 500, 100, 30000.00, 2500.00, DATE_ADD(NOW(), INTERVAL 4 DAY), 'Order 2000 payment for Maxi Dress.', '{ "item_id_1100": { "quantity": 2, "item_total": 15000.00, "product_variant_id": 1000 } }', 1, 0, 0, 0, NOW());

-- 28. order_shipping_log (Depends on shoppers, organizations)
INSERT INTO order_shipping_log (id, total_fee_amount_paid, total_tax_amount_paid, tenant_id, consignee_id, tracking_number, shipment_status_shipped, shipment_status_in_transit, shipment_status_delivered, shipment_status_delayed) VALUES
(2200, 10.00, 1.00, 100, 500, 'TRKNG123456789', 1, 1, 0, 0);

-- 29. order_shipment_transit_manifest (Depends on shipping_log, carriers, carrier_stops, delivery_methods, organizations)
INSERT INTO order_shipment_transit_manifest (shipping_log_id, shipment_pickup_date, shipment_dropoff_date, shipment_carrier_ingress_stop_id, shipment_carrier_id, tenant_id, shipment_transit_delivery_method_id, shipment_carrier_stop_ordering, is_consignee_dropoff, requires_consignee_dropoff, shipment_carrier_stop_status_details) VALUES
(2200, DATE_ADD(NOW(), INTERVAL 1 DAY), DATE_ADD(NOW(), INTERVAL 1 DAY), 1800, 1702, 100, 1603, 'first', 0, 0, '{ "status": "Picked up from consignor and dropped off at first stop" }'),
(2201, DATE_ADD(NOW(), INTERVAL 3 DAY), DATE_ADD(NOW(), INTERVAL 4 DAY), 1801, 1700, 100, 1600, 'penultimate', 0, 1, '{ "status": "dropped off at collection center" }');

-- 30. order_basket_transactions (Depends on invoice, order, shopping_cart, shipping_log, shoppers, world_location_addresses, organizations)
INSERT INTO order_basket_transactions (invoice_id, order_id, pay_on_delivery, shopping_cart_id, payment_method, currency, shipment_start_date, shipment_end_date, shipment_carrier_stops_count, tenant_id, shipping_log_id, shopper_address_id) VALUES
(2100, 2000, 0, 1400, 'paystack', 'NGN', DATE_ADD(NOW(), INTERVAL 1 DAY), DATE_ADD(NOW(), INTERVAL 4 DAY), 2, 100, 2200, 400);

-- 31. installed_order_shipment_transit_carriers (Depends on organizations)
INSERT INTO installed_order_shipment_transit_carriers (id, tenant_id, carried_id) VALUES
(2400, 100, 1700),
(2401, 100, 1702);

-- 32. customer_emails ()
INSERT INTO customer_emails (id, email, is_primary) VALUES
(2500, 'shopper.jane@example.com', 1),
(2501, 'shopper.amaka.uke@hotmail.com', 1),
(2502, 'amaksbaby54@gmail.com', 0);

-- 33. shopper_emails (Depends on organizations)
INSERT INTO shopper_emails (email_id, tenant_id, shopper_id) VALUES
(2500, 100, 500),
(2501, 100, 502),
(2502, 100, 502);


INSERT INTO inventory_product_stock_variants (
	id,
	tenant_id,
	brand_id,
	category_id,
	sku_format,
	variant_attributes,
	varaint_image_urls
) VALUES

(
	4,
	1,
  	2,
	3,
	'[Network]-[Color]-[Weight]-[OS]-[SIM Type]-[SIM Count]-[Display]-[Brand]-[Model]',
	JSON_OBJECT(
		"networks",
		JSON_ARRAY("GSM", "CDMA", "HSPA", "EVDO"),
		"bodies",
		JSON_ARRAY("5.50_x_2.50_x_0.50_inches", "4.00_x_2.00_x_0.75_inches", "6.11_x_3.59_x_0.46_inches", "7.00_x_3.50_x_0.25_inches", "5.11 x 2.59 x 0.46 inches"),
		"weights",
		JSON_ARRAY("125_grams", "143_grams", "145_grams", "150_grams", "250_grams"),
		"sim_types",
		JSON_ARRAY("Nano-SIM" , "Micro-SIM"),
		"sim_counts",
		JSON_ARRAY("1", "2"),
		"displays",
		JSON_ARRAY("3.5_inches", "6.5_inches", "5.00_inches", "4.5_inches"),
		"colors",
		JSON_ARRAY("red", "black", "grey", "green", "white"),
		"resolutions",
		JSON_ARRAY("720_x_1280_pixels", "1920_x_1080_pixels"),
		"oses" ,
		JSON_ARRAY("Android_Lollipop_v4.4", "Android_Jellybean_v4.3", "Android_Marshmallow_v4.5", "Android_KitKat_v4.3")
	),
	JSON_ARRAY( -- use a URL shortner for these cloudinary secure URLs to conserve space in the JSON array here
		"https://res.cloudinary.com/<tenant_name>/image/upload/sample_1.jpg|?color=red,green&os=Android_Jellybean_v4.3&weight=145_grams&resolutions=720_x_1280_pixels&sim_type=Micro-SIM&display=3.5_inches&sim_count=1&network=GSM,HSPA",
		"https://res.cloudinary.com/<tenant_name>/image/upload/sample_2.jpg|?color=white,black,grey&os=Android_KitKat_v4.3&weight=150_grams&resolutions=720_x_1280_pixels&sim_type=Nano-SIM&display=3.5_inches&sim_count=2&network=GSM,CDMA,HSPA",
		"https://res.cloudinary.com/<tenant_name>/image/upload/sample_3.jpg|?color=grey,green&os=Android_Marshmallow_v4.5&weight=250_grams&resolutions=720_x_1280_pixels&sim_type=Micro-SIM&display=5.00_inches&sim_count=1&network=GSM,CDMA"
	)
);
No results found