-- A synthetic shop to develop against.
--
-- INVENTED DATA, and that is the point. Copying rows out of production to get a
-- working local console puts real customers, real orders and real (cleartext)
-- passwords on a laptop — so this file makes up a merchant instead, which means
-- it can be committed, shared, and reset without anybody thinking about what is
-- in it.
--
-- Everything uses ids from 9000 up, well clear of anything real, so a local
-- database that has also had production rows loaded into it will not collide.
--
-- What it gives you:
--
--   * a complete merchant (9001 Testmart) — profile filled in, so the setup
--     walkthrough shows it finished
--   * an INCOMPLETE merchant (9002 Halfmart) with `categoryid = 0` — the exact
--     shape that broke `gettenantinfo` and made the profile step impossible to
--     finish. Worth keeping as a permanent regression fixture.
--   * two branches, so the "All branches" tenant-wide read has something to
--     aggregate and cannot silently show one outlet
--   * one account per role, so every workspace can be signed into
--   * products in each of the three app-visibility states — on sale, no stock,
--     no price — because those three are what most console bugs turn out to be
--
-- Passwords are the literal string below. Fiesta compares passwords in clear,
-- which is a real problem in production and simply a fact here.

BEGIN;

-- ── Masters the tenant joins hang off ───────────────────────────────────────
INSERT INTO app_location (applocationid, locationname, latitude, longitude, radius)
VALUES (9001, 'Testville', '11.0168', '76.9558', 25000)
ON CONFLICT (applocationid) DO NOTHING;

INSERT INTO app_category (categoryid, categoryname)
VALUES (9001, 'Grocery')
ON CONFLICT (categoryid) DO NOTHING;

-- ── Merchant one: complete ──────────────────────────────────────────────────
INSERT INTO tenants (
  tenantid, tenantname, companyname, configid, categoryid, applocationid,
  primaryemail, primarycontact, address, suburb, city, state, postcode,
  latitude, longitude, tenantimage, tenantinfo, licenseno, registrationno,
  minorder, approved, status
) VALUES (
  9001, 'Testmart', 'Testmart Retail', 1, 9001, 9001,
  'owner@testmart.invalid', '9000000001', '1 Test Street', 'Testville', 'Coimbatore',
  'Tamil Nadu', '641001', '11.0168', '76.9558',
  'https://placehold.co/200x200?text=Testmart', 'Daily needs and fresh produce.',
  '12345678901234', 'REG-TESTMART-1',
  99, 1, 'Active'
) ON CONFLICT (tenantid) DO NOTHING;

-- ── Merchant two: the regression fixture ────────────────────────────────────
--
-- `categoryid = 0` matches no `app_category` row. Both master joins in
-- GetTenantByID were INNER, so this tenant came back as an all-zero record —
-- the console showed an empty profile form over a real business, and the setup
-- walkthrough's first step could never complete. Four of two hundred live
-- tenants are in this state. Keep it: it is the cheapest possible guard against
-- that join going back.
INSERT INTO tenants (
  tenantid, tenantname, configid, categoryid, applocationid,
  primaryemail, primarycontact, address, city, state, postcode, approved, status
) VALUES (
  9002, 'Halfmart', 1, 0, 9001,
  'owner@halfmart.invalid', '9000000002', '2 Test Street', 'Coimbatore',
  'Tamil Nadu', '641002', 1, 'Active'
) ON CONFLICT (tenantid) DO NOTHING;

-- ── Two branches, so tenant-wide reads have something to aggregate ──────────
INSERT INTO tenantlocations (
  locationid, tenantid, locationname, email, contactno, address, suburb, city,
  state, postcode, latitude, longitude, opentime, closetime, applocationid,
  deliveryradius, deliverymins, status
) VALUES
  (9101, 9001, 'Testmart Main', 'main@testmart.invalid', '9000000011',
   '1 Test Street', 'Testville', 'Coimbatore', 'Tamil Nadu', '641001',
   '11.0168', '76.9558', '08:00', '22:00', 9001, 5000, 30, 'Active'),
  (9102, 9001, 'Testmart North', 'north@testmart.invalid', '9000000012',
   '9 North Road', 'Northville', 'Coimbatore', 'Tamil Nadu', '641004',
   '11.0500', '76.9600', '09:00', '21:00', 9001, 5000, 30, 'Active'),
  (9103, 9002, 'Halfmart Main', 'main@halfmart.invalid', '9000000021',
   '2 Test Street', 'Testville', 'Coimbatore', 'Tamil Nadu', '641002',
   '11.0170', '76.9560', '08:00', '22:00', 9001, 5000, 30, 'Active')
ON CONFLICT (locationid) DO NOTHING;

-- ── One account per role ────────────────────────────────────────────────────
--
-- `authname` and `configid` are both set on every row. Login is
-- `WHERE authname = ? AND configid = ?` and never looks at the email column, so
-- an account missing either is created, listed, and refused at the sign-in
-- screen. That was a real bug; these rows are what it looks like done right.
--
-- roleid decides the workspace: 1 and 3 reach Store Admin, everything else is a
-- branch user. 7 and 8 are till accounts and are excluded from every
-- back-office query by the backend itself.
INSERT INTO app_users (
  userid, authname, firstname, lastname, email, dialcode, contactno,
  configid, roleid, password, tenantid, locationid, applocationid,
  status, issuperadmin
) VALUES
  (9201, 'admin@testmart.invalid', 'Tessa', 'Admin', 'admin@testmart.invalid',
   '+91', '9000000101', 1, 3, 'localdev', 9001, 9101, 9001, 'Active', false),
  (9202, 'main@testmart.invalid', 'Mani', 'Manager', 'main@testmart.invalid',
   '+91', '9000000102', 1, 4, 'localdev', 9001, 9101, 9001, 'Active', false),
  -- Hired, not yet placed. `locationid = 0` is the state the people screen
  -- exists to resolve, and it was invisible until GetStaffs stopped INNER
  -- JOINing tenantlocations.
  (9203, 'newhire@testmart.invalid', 'Nila', 'Newhire', 'newhire@testmart.invalid',
   '+91', '9000000103', 1, 4, 'localdev', 9001, 0, 9001, 'Active', false),
  (9204, 'super@testmart.invalid', 'Sup', 'Ervisor', 'super@testmart.invalid',
   '+91', '9000000104', 1, 7, 'localdev', 9001, 9101, 9001, 'Active', false),
  (9205, 'cash@testmart.invalid', 'Cash', 'Ier', 'cash@testmart.invalid',
   '+91', '9000000105', 1, 8, 'localdev', 9001, 9101, 9001, 'Active', false),
  (9206, 'admin@halfmart.invalid', 'Hal', 'Admin', 'admin@halfmart.invalid',
   '+91', '9000000201', 1, 3, 'localdev', 9002, 9103, 9001, 'Active', false)
ON CONFLICT (userid) DO NOTHING;

-- ── Products, in each of the three visibility states ────────────────────────
--
-- categoryid 2 is what the customer app browses. A product filed anywhere else
-- is invisible to shoppers however well priced and stocked, which is the single
-- most common cause of "it is not showing in the app".
INSERT INTO products (
  productid, tenantid, categoryid, subcategoryid, productname, productbrand,
  productsku, productunit, productcost, retailprice, taxpercent, approve,
  productimage, productdesc
) VALUES
  (9301, 9001, 2, 0, 'Test Rice 5kg', 'Testbrand', 'TM-RICE-5K', '5kg',
   320, 395, 5, 1, 'https://placehold.co/120x120?text=Rice', 'Everyday long grain'),
  (9302, 9001, 2, 0, 'Test Oil 1L', 'Testbrand', 'TM-OIL-1L', '1L',
   150, 198, 5, 1, 'https://placehold.co/120x120?text=Oil', 'Cooking oil'),
  (9303, 9001, 2, 0, 'Test Biscuits 100g', 'Testbrand', 'TM-BISC-100', '100g',
   18, 25, 12, 1, 'https://placehold.co/120x120?text=Biscuits', 'Sweet biscuits'),
  -- Filed under no category: priced, released and stocked below, and still
  -- invisible to the app. The state that catches everybody.
  (9304, 9001, 0, 0, 'Test Uncategorised', 'Testbrand', 'TM-UNCAT', '1pc',
   10, 15, 0, 1, 'https://placehold.co/120x120?text=Uncat', 'No category on purpose')
ON CONFLICT (productid) DO NOTHING;

-- price + publishedat = priced and released. Both are needed: a product with
-- one and not the other looks identical on the shelf and cannot be sold.
INSERT INTO productlocations (
  productlocationid, tenantid, locationid, productid, price, status, publishedat
) VALUES
  (9401, 9001, 9101, 9301, 395, 'Active', NOW()),   -- on sale
  (9402, 9001, 9101, 9302, 198, 'Active', NOW()),   -- released, no stock below
  (9403, 9001, 9101, 9303,   0, 'Active', NULL),    -- no price, not released
  (9404, 9001, 9101, 9304,  15, 'Active', NOW()),   -- everything but a category
  (9405, 9001, 9102, 9301, 395, 'Active', NOW())    -- same product, second branch
ON CONFLICT (productlocationid) DO NOTHING;

-- Stock is SUM(in) - SUM(out) per outlet, never a stored figure.
INSERT INTO productstocks (
  productstockid, tenantid, locationid, productid, stockdate, stocktype, quantity, status
) VALUES
  (9501, 9001, 9101, 9301, NOW(), 'in',  40, 'Active'),
  (9502, 9001, 9101, 9301, NOW(), 'out',  5, 'Active'),  -- balance 35
  (9503, 9001, 9101, 9304, NOW(), 'in',  10, 'Active'),
  (9504, 9001, 9102, 9301, NOW(), 'in',  12, 'Active')
ON CONFLICT (productstockid) DO NOTHING;

-- ── The platform operator ───────────────────────────────────────────────────
--
-- Without this there is nobody who can open the Nearle Admin workspace, which
-- is the one this console was built for first. `resolveRole` checks
-- `issuperadmin` BEFORE roleid — deliberately, because the flag is derived by
-- the server and a roleid is just a number in a row — so no amount of role 1
-- gets you in without it, and every local session landed in Store Admin
-- instead. The accounts above are one per role and this was the role they were
-- missing.
--
-- Not attached to either merchant in spirit, only in columns: a platform
-- operator has to carry a tenantid because the column is not nullable, and
-- nothing in the admin workspace reads it.
INSERT INTO app_users (
  userid, authname, firstname, lastname, email, dialcode, contactno,
  configid, roleid, password, tenantid, locationid, applocationid,
  status, issuperadmin
) VALUES
  (9299, 'super@nearle.invalid', 'Nearle', 'Operator', 'super@nearle.invalid',
   '+91', '9000009999', 1, 1, 'localdev', 9001, 9101, 9001, 'Active', true)
ON CONFLICT (userid) DO NOTHING;

-- ── The role ladder ─────────────────────────────────────────────────────────
--
-- `getstaffs` LEFT JOINs app_roles for `rolename`, so an empty table is not an
-- error — every person on Users & access simply reads "—" where their role
-- should be. The ids are the ones the rest of the system already assumes:
-- 1 and 3 reach Store Admin, 4 is a branch manager, 7 and 8 are till accounts
-- and are excluded from every back-office query by the backend itself.
INSERT INTO app_roles (roleid, rolename, configid) VALUES
  (1, 'Super admin', 1),
  (3, 'Admin',       1),
  (4, 'Manager',     1),
  (7, 'Supervisor',  1),
  (8, 'Cashier',     1)
ON CONFLICT (roleid) DO NOTHING;

-- ── Aisles under the category the customer app browses ──────────────────────
--
-- categoryid 2 is the only category the app lists, and the aisle a shopper
-- reads is the SUBCATEGORY. With none of these the sheet importer has nothing
-- to resolve a row's category against, so every imported product falls back to
-- subcategoryid 0 and lands under "Uncategorized".
INSERT INTO productsubcategories (subcatid, categoryid, tenantid, subcatname, status, sortorder)
VALUES
  (9601, 2, 9001, 'Rice & Grains', 'Active', 1),
  (9602, 2, 9001, 'Oils & Ghee',   'Active', 2),
  (9603, 2, 9001, 'Snacks',        'Active', 3)
ON CONFLICT (subcatid) DO NOTHING;

-- ── Move the sequences past the seeded ids ──────────────────────────────────
--
-- Everything above inserts an explicit id, which does NOT advance the sequence
-- behind that column. So the first tenant, outlet or product created against a
-- fresh local database came back as id 1 — harmless here, but it means local
-- ids look nothing like the ones the same code produces in production, and a
-- seed that ever collides with a sequence value fails on a duplicate key.
--
-- `GREATEST(..., 1)` because setval refuses a value below the sequence minimum,
-- and a table the seed does not touch is legitimately empty.
SELECT setval('tenants_tenantid_seq',                 GREATEST((SELECT COALESCE(MAX(tenantid),0)         FROM tenants), 1));
SELECT setval('tenantlocations_locationid_seq',       GREATEST((SELECT COALESCE(MAX(locationid),0)       FROM tenantlocations), 1));
SELECT setval('app_users_userid_seq',                 GREATEST((SELECT COALESCE(MAX(userid),0)           FROM app_users), 1));
SELECT setval('products_productid_seq',               GREATEST((SELECT COALESCE(MAX(productid),0)        FROM products), 1));
SELECT setval('productlocations_productlocationid_seq', GREATEST((SELECT COALESCE(MAX(productlocationid),0) FROM productlocations), 1));
SELECT setval('productstocks_productstockid_seq',     GREATEST((SELECT COALESCE(MAX(productstockid),0)   FROM productstocks), 1));
SELECT setval('customers_customerid_seq',             GREATEST((SELECT COALESCE(MAX(customerid),0)       FROM customers), 1));

COMMIT;