-- =============================================================================
-- APOTHECARY — Full PostgreSQL Schema
-- Run this once: psql -d your_db -f schema.sql
-- Or run setup.js which executes this automatically
-- =============================================================================

-- Extensions
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- =============================================================================
-- USERS
-- =============================================================================
CREATE TABLE IF NOT EXISTS users (
  id           SERIAL PRIMARY KEY,
  name         TEXT NOT NULL,
  initials     TEXT NOT NULL UNIQUE,
  role         TEXT NOT NULL DEFAULT 'maker' CHECK (role IN ('admin','maker','viewer')),
  color        TEXT NOT NULL DEFAULT '#c8a84b',
  password_hash TEXT NOT NULL,
  force_pw_change BOOLEAN NOT NULL DEFAULT true,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- =============================================================================
-- SUPPLIERS
-- =============================================================================
CREATE TABLE IF NOT EXISTS suppliers (
  id           SERIAL PRIMARY KEY,
  name         TEXT NOT NULL,
  website      TEXT,
  email        TEXT,
  phone        TEXT,
  notes        TEXT,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- =============================================================================
-- INGREDIENTS
-- =============================================================================
CREATE TABLE IF NOT EXISTS ingredients (
  id            SERIAL PRIMARY KEY,
  name          TEXT NOT NULL,
  inci          TEXT NOT NULL DEFAULT '',
  sku           TEXT NOT NULL DEFAULT '',
  cat           TEXT NOT NULL DEFAULT 'other' CHECK (cat IN ('oil','butter','wax','lye','chemical','eo','fragrance','colorant','additive','botanical','packaging','other')),
  unit          TEXT NOT NULL DEFAULT 'g',
  stock         NUMERIC(12,4) NOT NULL DEFAULT 0,
  min_stock     NUMERIC(12,4) NOT NULL DEFAULT 0,
  supplier_id   INTEGER REFERENCES suppliers(id) ON DELETE SET NULL,
  safety        TEXT NOT NULL DEFAULT '',
  notes         TEXT NOT NULL DEFAULT '',
  cost_per_unit NUMERIC(12,6) NOT NULL DEFAULT 0,
  lot_number    TEXT NOT NULL DEFAULT '',
  expiry_date   DATE,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Price history for each ingredient
CREATE TABLE IF NOT EXISTS ingredient_price_history (
  id            SERIAL PRIMARY KEY,
  ingredient_id INTEGER NOT NULL REFERENCES ingredients(id) ON DELETE CASCADE,
  price         NUMERIC(12,6) NOT NULL,
  recorded_date DATE NOT NULL DEFAULT CURRENT_DATE,
  note          TEXT NOT NULL DEFAULT '',
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- =============================================================================
-- RECIPES
-- =============================================================================
CREATE TABLE IF NOT EXISTS recipes (
  id            SERIAL PRIMARY KEY,
  name          TEXT NOT NULL,
  type          TEXT NOT NULL DEFAULT 'soap_cp' CHECK (type IN ('soap_cp','soap_mp','soap_hp','soap_liq','candle','perfume','incense','balm','lotion','other')),
  batch_size    NUMERIC(12,4) NOT NULL DEFAULT 1000,
  batch_unit    TEXT NOT NULL DEFAULT 'g',
  tags          TEXT[] NOT NULL DEFAULT '{}',
  version       TEXT NOT NULL DEFAULT 'v1',
  notes         TEXT NOT NULL DEFAULT '',
  cure_weeks    INTEGER NOT NULL DEFAULT 0,
  superfat      NUMERIC(5,2),
  water_pct     NUMERIC(5,2),
  lye_type      TEXT CHECK (lye_type IN ('naoh','koh')),
  koh_purity    NUMERIC(5,2),
  perf_type     TEXT,
  arom_pct      NUMERIC(5,2),
  expected_yield INTEGER,
  is_archived   BOOLEAN NOT NULL DEFAULT false,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Version snapshots of recipes
CREATE TABLE IF NOT EXISTS recipe_versions (
  id          SERIAL PRIMARY KEY,
  recipe_id   INTEGER NOT NULL REFERENCES recipes(id) ON DELETE CASCADE,
  version     TEXT NOT NULL,
  snapshot    JSONB NOT NULL,
  saved_by    INTEGER REFERENCES users(id) ON DELETE SET NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Recipe ingredient lines
CREATE TABLE IF NOT EXISTS recipe_ingredients (
  id            SERIAL PRIMARY KEY,
  recipe_id     INTEGER NOT NULL REFERENCES recipes(id) ON DELETE CASCADE,
  ingredient_id INTEGER REFERENCES ingredients(id) ON DELETE SET NULL,
  name          TEXT NOT NULL,
  amount        NUMERIC(12,4) NOT NULL DEFAULT 0,
  unit          TEXT NOT NULL DEFAULT 'g',
  pct           NUMERIC(8,4) NOT NULL DEFAULT 0,
  phase         TEXT NOT NULL DEFAULT '',
  sort_order    INTEGER NOT NULL DEFAULT 0
);

-- =============================================================================
-- BATCHES
-- =============================================================================
CREATE TABLE IF NOT EXISTS batches (
  id              TEXT PRIMARY KEY,  -- e.g. B-0041
  product         TEXT NOT NULL,
  type            TEXT NOT NULL DEFAULT 'soap_cp',
  recipe_id       INTEGER REFERENCES recipes(id) ON DELETE SET NULL,
  size            NUMERIC(12,4) NOT NULL DEFAULT 1000,
  unit            TEXT NOT NULL DEFAULT 'g',
  batch_date      DATE NOT NULL DEFAULT CURRENT_DATE,
  maker_id        INTEGER REFERENCES users(id) ON DELETE SET NULL,
  maker_initials  TEXT NOT NULL DEFAULT '',
  maker_name      TEXT NOT NULL DEFAULT '',
  status          TEXT NOT NULL DEFAULT 'planned' CHECK (status IN ('planned','in_process','curing','ready','archived')),
  yield_amount    TEXT NOT NULL DEFAULT '',
  notes           TEXT NOT NULL DEFAULT '',
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Batch status history
CREATE TABLE IF NOT EXISTS batch_status_history (
  id         SERIAL PRIMARY KEY,
  batch_id   TEXT NOT NULL REFERENCES batches(id) ON DELETE CASCADE,
  status     TEXT NOT NULL,
  changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  changed_by INTEGER REFERENCES users(id) ON DELETE SET NULL
);

-- Next batch number counter
CREATE TABLE IF NOT EXISTS counters (
  name  TEXT PRIMARY KEY,
  value INTEGER NOT NULL DEFAULT 1
);

-- =============================================================================
-- PRODUCTS
-- =============================================================================
CREATE TABLE IF NOT EXISTS products (
  id          SERIAL PRIMARY KEY,
  name        TEXT NOT NULL,
  type        TEXT NOT NULL DEFAULT 'soap_cp',
  recipe_id   INTEGER REFERENCES recipes(id) ON DELETE SET NULL,
  sku         TEXT NOT NULL DEFAULT '',
  price       NUMERIC(10,2) NOT NULL DEFAULT 0,
  status      TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active','seasonal','discontinued')),
  notes       TEXT NOT NULL DEFAULT '',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- =============================================================================
-- SALES
-- =============================================================================
CREATE TABLE IF NOT EXISTS sales (
  id          SERIAL PRIMARY KEY,
  product_id  INTEGER REFERENCES products(id) ON DELETE SET NULL,
  product_name TEXT NOT NULL DEFAULT '',
  channel     TEXT NOT NULL DEFAULT 'direct',
  qty         INTEGER NOT NULL DEFAULT 1,
  price       NUMERIC(10,2) NOT NULL DEFAULT 0,
  total       NUMERIC(10,2) NOT NULL DEFAULT 0,
  sale_date   DATE NOT NULL DEFAULT CURRENT_DATE,
  notes       TEXT NOT NULL DEFAULT '',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- =============================================================================
-- JOURNAL
-- =============================================================================
CREATE TABLE IF NOT EXISTS journal (
  id          SERIAL PRIMARY KEY,
  title       TEXT NOT NULL,
  body        TEXT NOT NULL DEFAULT '',
  tags        TEXT[] NOT NULL DEFAULT '{}',
  author_id   INTEGER REFERENCES users(id) ON DELETE SET NULL,
  entry_date  DATE NOT NULL DEFAULT CURRENT_DATE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- =============================================================================
-- INDEXES
-- =============================================================================
CREATE INDEX IF NOT EXISTS idx_ingredients_cat         ON ingredients(cat);
CREATE INDEX IF NOT EXISTS idx_ingredients_supplier    ON ingredients(supplier_id);
CREATE INDEX IF NOT EXISTS idx_recipe_ingredients_recipe ON recipe_ingredients(recipe_id);
CREATE INDEX IF NOT EXISTS idx_recipe_ingredients_ing  ON recipe_ingredients(ingredient_id);
CREATE INDEX IF NOT EXISTS idx_batches_status          ON batches(status);
CREATE INDEX IF NOT EXISTS idx_batches_recipe          ON batches(recipe_id);
CREATE INDEX IF NOT EXISTS idx_batches_date            ON batches(batch_date);
CREATE INDEX IF NOT EXISTS idx_sales_date              ON sales(sale_date);
CREATE INDEX IF NOT EXISTS idx_sales_product           ON sales(product_id);
CREATE INDEX IF NOT EXISTS idx_journal_date            ON journal(entry_date);
CREATE INDEX IF NOT EXISTS idx_price_history_ing       ON ingredient_price_history(ingredient_id);

-- =============================================================================
-- UPDATED_AT TRIGGER
-- =============================================================================
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN NEW.updated_at = NOW(); RETURN NEW; END;
$$ LANGUAGE plpgsql;

DO $$ DECLARE t TEXT;
BEGIN
  FOREACH t IN ARRAY ARRAY['users','suppliers','ingredients','recipes','batches','products','journal'] LOOP
    EXECUTE format('DROP TRIGGER IF EXISTS trg_updated_at ON %I', t);
    EXECUTE format('CREATE TRIGGER trg_updated_at BEFORE UPDATE ON %I FOR EACH ROW EXECUTE FUNCTION set_updated_at()', t);
  END LOOP;
END $$;

-- =============================================================================
-- SEED: COUNTERS
-- =============================================================================
INSERT INTO counters (name, value) VALUES ('batch_num', 46) ON CONFLICT (name) DO NOTHING;

-- =============================================================================
-- SEED: SUPPLIERS
-- (passwords seeded by setup.js — not here, for security)
-- =============================================================================
INSERT INTO suppliers (id, name, website, email, notes) VALUES
  (1, 'Brambleberry',              'https://www.brambleberry.com',              'cs@brambleberry.com',          'Main soap supply vendor. Free ship over $75.'),
  (2, 'Wholesale Supplies Plus',   'https://www.wholesalesuppliesplus.com',     'info@wholesalesuppliesplus.com','Good for fragrance oils and additives.'),
  (3, 'New Directions Aromatics',  'https://www.newdirectionsaromatics.com',    'info@newdirectionsaromatics.com','Best source for EOs in bulk. Canadian — factor shipping time.')
ON CONFLICT (id) DO NOTHING;

SELECT setval('suppliers_id_seq', (SELECT MAX(id) FROM suppliers));

-- =============================================================================
-- SEED: INGREDIENTS (all 73)
-- =============================================================================
INSERT INTO ingredients (id,name,inci,sku,cat,unit,stock,min_stock,supplier_id,safety,notes,cost_per_unit) VALUES
  (1,'Coconut Oil (76°)','Cocos nucifera','OIL-001','oil','g',2000,500,1,'','Primary lather and hardness builder in CP soap. 76° refined — neutral scent. Melt before use.',0.0045),
  (2,'Olive Oil (Pomace)','Olea europaea','OIL-002','oil','g',3000,1000,1,'','Conditioning workhorse. Pomace grade acceptable for soap — smoother than EVOO for tracing.',0.0038),
  (3,'Sodium Hydroxide (NaOH)','Sodium Hydroxide','CHE-001','chemical','g',1000,300,2,'CORROSIVE. Full PPE required. Never add water to lye. Store in sealed container — absorbs moisture from air.','Food-grade lye for CP/HP soap only. Do not substitute KOH.',0.0082),
  (4,'Shea Butter (raw)','Butyrospermum parkii','BUT-001','butter','g',500,200,1,'','Unrefined Grade A shea. Adds creaminess and conditioning. Use up to 15% in CP soap.',0.0125),
  (5,'Fragrance Oil — Lavender','Fragrance','FO-001','fragrance','oz',8,2,2,'Avoid eyes.','Phthalate-free. Behaves well in CP soap — slow trace. Test in M&P first if reformulating.',0.85),
  (6,'Fragrance Oil — Oakmoss & Amber','Fragrance','FO-002','fragrance','oz',5,2,2,'Avoid eyes.','Complex base-forward blend. Accelerates trace slightly. May discolor to light tan — expected.',0.92),
  (7,'Soy Wax (Container — 464)','Cocos nucifera (modified)','WAX-001','wax','g',5000,1000,2,'','Golden 464 soy wax. Optimised for container candles. FO load max 10%. Pour at 135°F.',0.0038),
  (8,'Castor Oil','Ricinus communis','OIL-003','oil','ml',300,100,1,'','Boosts lather and bubbles. Use 5–10% in soap. Essential in liquid soap recipes.',0.0095),
  (9,'CD-18 Pre-Tabbed Wick','n/a','WIC-001','other','pcs',50,20,2,'Keep away from flame during assembly.','For 8oz amber jar. Test burn time before production batch.',0.22),
  (10,'Fragrance Oil — Patchouli Resin','Fragrance','FO-003','fragrance','oz',4,2,2,'Avoid eyes.','Dark resinous FO. Discolors to dark brown — use in dark-colored wax or uncolored bars only.',0.88),
  (11,'Vitamin E Oil','Tocopherol','ADD-001','additive','ml',60,20,1,'','Antioxidant — extends shelf life of oils. Add at 0.1–0.5%. Do not overshoot.',1.20),
  (12,'Perfumer''s Alcohol (SDA 40-B)','Alcohol denat.','CHE-002','chemical','ml',500,200,3,'Flammable. Store away from heat and open flame.','SD-40B denatured alcohol. Standard perfume carrier. Anhydrous — will cloud if water contaminated.',0.018),
  (13,'Fragrance Oil — Rose & Oud','Fragrance','FO-004','fragrance','oz',3,2,2,'Avoid eyes.','Strong — use at 6% max in soap. Slow trace. Rose scent fades; oud anchors well.',1.15),
  (14,'Beeswax (filtered)','Cera Alba','WAX-002','wax','g',200,50,1,'','Filtered yellow beeswax. Used in lotion bars and some candle blends. Higher MP than soy.',0.025),
  (15,'Kaolin Clay','Kaolin','ADD-002','additive','g',500,100,2,'Avoid inhaling powder.','White cosmetic clay. Adds slip and silkiness to soap. Can help anchor fragrance. Use 1 tsp PPO.',0.012),
  (16,'Sweet Almond Oil','Prunus amygdalus','OIL-005','oil','ml',500,150,1,'','Light, skin-friendly carrier. Good for massage and leave-on products. Mild enough for most skin types.',0.0088),
  (17,'Lye (NaOH) - 2lb Bag','Sodium Hydroxide','CHE-001B','chemical','g',900,300,1,'CORROSIVE. Full PPE required.','Same material as CHE-001 — different pack size. Track separately for stock accuracy.',0.0078),
  (18,'Fragrance Oil — Sandalwood','Fragrance','FO-005','fragrance','oz',6,2,2,'Avoid eyes.','Warm, creamy sandalwood. Behaves well in CP — no acceleration. Pale tan discoloration normal.',0.80),
  (19,'Melt & Pour Soap Base (Shea)','Sodium olivate (and) Butyrospermum parkii','SOA-001','other','g',2000,500,2,'','White M&P base with added shea. Good clarity. Melt at 130°F. Add FO below 140°F.',0.0095),
  (20,'Activated Charcoal','Charcoal Powder','ADD-003','additive','g',100,30,2,'Avoid inhaling.','Cosmetic grade. Detoxifying additive. Turns soap dark grey — pair with peppermint or tea tree.',0.028),
  (21,'Argan Oil','Argania spinosa','OIL-006','oil','ml',100,30,3,'','Luxury conditioning oil. High in Vitamin E. Use at 5–10% in soap or as leave-on serum.',0.085),
  (22,'Neem Oil','Melia azadirachta','OIL-007','oil','ml',200,50,2,'Strong odor — use cautiously.','Potent anti-fungal and antibacterial. Pungent smell — pair with strong EOs. Use ≤5% in soap.',0.022),
  (23,'Sodium Lactate','Sodium Lactate','ADD-004','additive','ml',200,50,2,'','Speeds unmolding in CP soap. Use 1 tsp PPO dissolved in lye water. Do not use above 3%.',0.018),
  (24,'Rosemary Extract (ROE)','Rosmarinus officinalis','ADD-005','additive','ml',50,15,3,'','Rosemary oleoresin antioxidant. Extends shelf life of oils and finished bars. Use 0.02–0.1%.',0.38),
  (25,'Cocoa Butter','Theobroma cacao','BUT-002','butter','g',300,100,1,'','Deodorized white cocoa butter. Adds hardness and conditioning. Use up to 15% in soap. Strong scent if undeodorized.',0.016),
  (26,'Lemon EO','Citrus limon','EO-001','eo','oz',2,0.5,3,'Phototoxic — do not apply before sun exposure.','Cold-pressed lemon. Fades quickly in CP soap. Use in rinse-off products or with fixative.',1.20),
  (27,'Peppermint EO','Mentha piperita','EO-002','eo','oz',2,0.5,3,'Avoid contact with eyes. Not for use on children under 6.','Steam-distilled. Behaves well in CP soap. Cooling effect. Pairs with charcoal for detox bars.',0.95),
  (28,'Tea Tree EO','Melaleuca alternifolia','EO-003','eo','oz',2,0.5,3,'Avoid eyes and mucous membranes.','Antimicrobial. Popular in acne and foot soap. Behaves well in CP. Strong scent throw.',1.05),
  (29,'Eucalyptus EO','Eucalyptus globulus','EO-004','eo','oz',1,0.5,3,'Keep away from young children.','Camphoraceous. Blends with peppermint, tea tree, lavender. Good in shampoo bars and decongestant products.',0.75),
  (30,'Lavender EO','Lavandula angustifolia','EO-005','eo','oz',4,1,3,'','True lavender — Lavandula angustifolia only. Calming. Pairs with nearly everything. Industry standard.',1.45),
  (31,'Rose Clay','Kaolin (and) Iron Oxides','ADD-006','additive','g',200,50,2,'','Pale pink cosmetic clay. Adds a warm flush to white soap base. Use 1 tsp PPO.',0.018),
  (32,'Mango Butter','Mangifera indica','BUT-003','butter','g',200,50,1,'','Light butter — less greasy than shea. Good in lip balms and lotion bars. Use up to 10% in soap.',0.019),
  (33,'Avocado Oil','Persea gratissima','OIL-008','oil','ml',200,50,1,'','Rich in Vitamins A, B, E. Deeply conditioning. Good for sensitive or mature skin. Use up to 20% in soap.',0.019),
  (34,'Dead Sea Salt','Sodium Chloride','ADD-007','additive','g',1000,200,2,'','Fine grind. Salt bars use 70–100% PPO — unmold within 1hr or they crack. Very hard bar, creamy lather.',0.008),
  (35,'Poppy Seeds','Papaver somniferum (seed)','ADD-008','additive','g',200,50,2,'','Exfoliant — add at trace. Use 1–2 tbsp PPO. Visual appeal in herbal bars.',0.012),
  (36,'Fragrance Oil — Vanilla Bean','Fragrance','FO-006','fragrance','oz',4,2,2,'Avoid eyes. Will discolor soap brown.','Vanilla accelerates trace and always discolors brown. Expected — plan accordingly. Great in candles.',0.75),
  (37,'Aloe Vera Juice','Aloe barbadensis','ADD-009','additive','ml',300,100,2,'Refrigerate after opening.','100% inner fillet aloe. Can replace portion of lye water (freeze first). Adds soothing properties.',0.012),
  (38,'Goat Milk Powder','Caprae lac','ADD-010','additive','g',200,50,2,'','Full-fat goat milk powder. Add to lye water (frozen) or at trace. Sugars accelerate trace and may volcano — soap cold.',0.038),
  (39,'Isopropyl Alcohol 99% (IPA)','Alcohol isopropyl','CHE-003','chemical','ml',500,200,2,'Flammable. Keep away from open flame.','Spray on M&P soap to prevent air bubbles. Also used to spritz between layers. 99% only — not 70%.',0.0085),
  (40,'Yuzu EO','Citrus junos','EO-030','eo','ml',30,10,3,'Phototoxic at high concentrations.','Japanese citrus. Bright, tart, slightly floral. Top note — high volatility. Use in accord with base fixatives.',0.95),
  (41,'Bamboo EO','Bambusa vulgaris','EO-031','eo','ml',15,5,3,'','Green, clean, slightly sweet. Modern freshness note. Good in masculine and unisex blends.',0.55),
  (42,'Japanese Mint (Hakka) EO','Mentha arvensis','EO-032','eo','ml',20,5,3,'Avoid mucous membranes. Do not use neat.','Highly mentholic — cooling and sharp. Use sparingly. Distinct from peppermint — more camphoraceous.',0.38),
  (43,'Hinoki (Japanese Cypress) EO','Chamaecyparis obtusa','EO-033','eo','ml',25,8,3,'','Distinctive woody-cedar Japanese forest note. Heart of the Shinrin Yoku accord. Rare — preserve stock.',1.45),
  (44,'Japanese Cedarwood EO','Cryptomeria japonica','EO-034','eo','ml',30,10,3,'','Warmer and softer than Atlas or Virginian cedar. Excellent fixative and base extender.',0.62),
  (45,'Shiso Leaf EO','Perilla frutescens','EO-035','eo','ml',15,5,3,'','Unusual herbaceous-anise green note. Authentic Japanese botanical. Very distinctive — use carefully.',1.20),
  (46,'Agarwood (Oud) EO','Aquilaria malaccensis','EO-036','eo','ml',5,2,3,'','Laboratory-cultivated oud. Smoky, animalic, complex. Base note fixative — use at 1–3% maximum. Expensive.',18.50),
  (47,'Oakmoss Absolute','Evernia prunastri','EO-037','eo','ml',10,3,3,'IFRA restricted. Sensitizer — patch test. Max 0.001% leave-on products.','Classic chypre base note. Powerful fixative. IFRA Amendment 49 strictly limits usage — check calculator before use.',3.80),
  (48,'Hiba Wood EO','Thujopsis dolabrata','EO-038','eo','ml',10,3,3,'','Rare Japanese hinoki relative. Mossy, slightly medicinal. Difficult to source — note substitute in recipe.',2.20),
  (49,'Birch Tar EO','Betula alba','EO-039','eo','ml',15,5,3,'Sensitizer at high concentrations. IFRA limits apply.','Smoked, leathery, medicinal. Use in tiny amounts — overpowering. Authentic smoky-forest base note.',1.85),
  (50,'Toasted Rice Absolute','Oryza sativa','EO-040','eo','ml',8,3,3,'','Unusual warm, nutty, slightly smoky absolute. Distinctive Japanese culinary note. Use 2–5%.',4.20),
  (51,'Iso E Super','p-Acetoxyethylcyclohexane','EO-041','eo','ml',50,15,3,'Sensitizer. IFRA limits apply. Patch test essential.','Woody-cedar-violet abstract aroma chemical. Phantom note — enhances other ingredients. IFRA max 17% fine fragrance.',0.28),
  (52,'Glass Spray Bottle — 10ml','n/a','PKG-001','packaging','pcs',20,5,NULL,'','Amber glass with silver spray pump. For travel perfume.',1.85),
  (53,'Glass Spray Bottle — 30ml','n/a','PKG-002','packaging','pcs',15,5,NULL,'','Amber glass with silver spray pump. Standard perfume bottle.',2.40),
  (54,'Roll-On Bottle — 10ml','n/a','PKG-003','packaging','pcs',30,10,NULL,'','Amber glass with stainless steel roller ball. For oil-based perfumes.',1.20),
  (55,'Perfume Outer Box — 10ml','n/a','PKG-004','packaging','pcs',20,5,NULL,'','Kraft paperboard gift box for 10ml bottles.',0.65),
  (56,'Perfume Outer Box — 30ml','n/a','PKG-005','packaging','pcs',15,5,NULL,'','Kraft paperboard gift box for 30ml bottles.',0.85),
  (57,'Candle Jar — 8oz Glass Amber','n/a','PKG-006','packaging','pcs',48,12,NULL,'','Amber glass straight-sided jar with wide mouth. 70mm opening.',1.95),
  (58,'Candle Wick — CD-18 (Pre-tabbed)','n/a','PKG-007','packaging','pcs',100,25,NULL,'','Pre-tabbed CD-18 cotton wick. Suitable for 8oz amber jar with 464 soy wax.',0.22),
  (59,'Candle Jar Lid — 70mm Metal','n/a','PKG-008','packaging','pcs',48,12,NULL,'','Black metal lid, 70mm. Fits amber glass candle jars.',0.45),
  (60,'Incense Cone Box — Kraft 10-Pack','n/a','PKG-009','packaging','pcs',30,10,NULL,'','Kraft paperboard slide-out box. Holds 10 cones. Window panel.',0.55),
  (61,'Incense Burner / Ash Catcher','n/a','PKG-010','packaging','pcs',10,3,NULL,'','Ceramic leaf ash catcher. Sold bundled with incense boxes.',3.50),
  (62,'Soap Paper Band / Belly Band','n/a','PKG-011','packaging','pcs',100,25,NULL,'','Natural kraft belly band. Wrap-around label area. 1.5in wide.',0.12),
  (63,'Soap Shrink Band','n/a','PKG-012','packaging','pcs',100,25,NULL,'','Clear PVC heat shrink band. Tamper-evident seal for soap.',0.08),
  (64,'Tin — 2oz Round (Balm / Salve)','n/a','PKG-013','packaging','pcs',50,15,NULL,'','Silver aluminium screw-top tin. 2oz / 60ml.',0.38),
  (65,'Wick Sticker (Adhesive Tab)','n/a','PKG-014','packaging','pcs',60,20,NULL,'','Self-adhesive tab to centre wick in jar before pouring.',0.03),
  (66,'Potassium Hydroxide (KOH)','Potassium Hydroxide','CHE-004','chemical','g',500,200,2,'Corrosive. Wear PPE. Keep dry — extremely hygroscopic. Always add lye to water, never reverse.','90% purity standard. Adjust lye calc for actual purity. For liquid soap (KOH) only — do not substitute for NaOH in bar soap.',0.0062),
  (67,'Olive Oil (Extra Virgin)','Olea europaea','OIL-003','oil','g',1000,500,1,'','Higher quality than pomace — preferred for liquid castile. Can substitute pomace at same weight.',0.0055),
  (68,'Sunflower Oil','Helianthus annuus','OIL-019','oil','g',500,200,1,'','High-oleic preferred for longer shelf life. Adds conditioning and fluidity to liquid soap.',0.0028),
  (69,'Beeswax (pastilles)','Cera Alba','WAX-002','wax','g',200,50,1,'','White cosmetic-grade pastilles. Sets balms and salves. Higher % = firmer texture. Melt with shea before adding liquid oils.',0.018),
  (70,'Calendula-infused Oil','Helianthus annuus (and) Calendula officinalis','OIL-020','oil','g',150,50,3,'','Sunflower oil infused with dried calendula flowers. Soothing and anti-inflammatory.',0.022),
  (71,'Chamomile EO (Roman)','Anthemis nobilis','EO-060','eo','ml',10,5,3,'Avoid in first trimester of pregnancy.','Precious — use sparingly. True Roman chamomile is calming and anti-inflammatory.',1.85),
  (72,'Lavender EO (ml)','Lavandula angustifolia','EO-007B','eo','ml',100,30,3,'','Pure lavender — ml unit for balm/liquid soap recipes.',0.19),
  (73,'Distilled Water','Aqua','CHE-005','chemical','g',5000,1000,NULL,'','Use distilled only — tap water introduces minerals and bacteria.',0.0002)
ON CONFLICT (id) DO NOTHING;

SELECT setval('ingredients_id_seq', (SELECT MAX(id) FROM ingredients));

-- =============================================================================
-- SEED: RECIPES
-- =============================================================================
INSERT INTO recipes (id,name,type,batch_size,batch_unit,tags,version,notes,cure_weeks,superfat,water_pct,lye_type,koh_purity,perf_type,arom_pct,expected_yield) VALUES
  (1,'Lavender Dream CP Soap','soap_cp',1000,'g',ARRAY['lavender','floral','classic','beginner','gift'],'v2','Classic cold-process lavender. Creamy lather, conditioning bar. This is the original Apothecary signature bar.',6,5,33,NULL,NULL,NULL,NULL,NULL),
  (2,'Oakmoss & Amber Soy Candle','candle',500,'g',ARRAY['soy','oakmoss','amber','cosy','autumn'],'v1','Soy container candle in 8oz amber jar. FO load 8%. Pour temp 135°F. Cure minimum 48hrs before burning.',0,NULL,NULL,NULL,NULL,NULL,NULL,3),
  (3,'Night Garden EDP','perfume',100,'ml',ARRAY['floral','rose','oud','feminine','luxury','romantic'],'v1','Extrait de Parfum concentrate. Mature 4 weeks minimum before evaluating. Rose fades first — oud anchors.',0,NULL,NULL,NULL,NULL,'extrait',30,NULL),
  (4,'Patchouli Resin Incense Cones','incense',200,'g',ARRAY['incense','patchouli','resin','earthy','meditation'],'v1','Raw makko-based cone incense. Dry time 72hrs minimum. Test burn before boxing — adjust makko % if needed.',0,NULL,NULL,NULL,NULL,NULL,NULL,40),
  (7,'Whipped Shea Body Butter','balm',500,'g',ARRAY['body butter','shea','whipped','moisturizer','fragrance-free'],'v1','Anhydrous whipped butter — no water phase, no preservative needed. Whip cooled shea until fluffy.',0,NULL,NULL,NULL,NULL,NULL,NULL,10),
  (8,'Charcoal & Tea Tree M&P Soap','soap_mp',800,'g',ARRAY['charcoal','tea tree','detox','acne','melt-pour'],'v1','Melt and pour soap with activated charcoal and tea tree EO.',2,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
  (9,'Rose Clay & Goat Milk CP Soap','soap_cp',1000,'g',ARRAY['rose clay','goat milk','sensitive skin','feminine','luxury'],'v1','Rose clay gives a warm flush. Goat milk for extra creaminess. Soap cold to avoid volcanic reaction.',6,5,33,NULL,NULL,NULL,NULL,NULL),
  (10,'Lotion Bar','balm',300,'g',ARRAY['lotion bar','beeswax','argan','portable','moisturizer'],'v1','Solid lotion bar. Melts on contact with skin warmth. Beeswax dominant for hard structure.',0,NULL,NULL,NULL,NULL,NULL,NULL,6),
  (12,'Classic Castile Liquid Soap','soap_liq',715,'g',ARRAY['castile','olive','liquid','gentle','KOH','unscented'],'v1','Hot-process liquid soap. Cook to full paste (zap test clear), then dilute to finished product. KOH at 90% purity.',0,0,34,'koh',90,NULL,NULL,NULL),
  (13,'Calendula & Beeswax Balm','balm',200,'g',ARRAY['balm','calendula','beeswax','soothing','lavender','chamomile','sensitive-skin'],'v1','Soothing all-purpose balm. Pour at 65°C. Texture: firmer → increase beeswax to 22g. Softer → reduce to 24g.',0,NULL,NULL,NULL,NULL,NULL,NULL,7)
ON CONFLICT (id) DO NOTHING;

SELECT setval('recipes_id_seq', (SELECT MAX(id) FROM recipes));

-- =============================================================================
-- SEED: RECIPE INGREDIENTS
-- =============================================================================
-- Recipe 1: Lavender Dream CP Soap
INSERT INTO recipe_ingredients (recipe_id,ingredient_id,name,amount,unit,pct,phase,sort_order) VALUES
  (1,1,'Coconut Oil (76°)',300,'g',30,'Oil Phase',1),
  (1,2,'Olive Oil (Pomace)',400,'g',40,'Oil Phase',2),
  (1,16,'Sweet Almond Oil',100,'g',10,'Oil Phase',3),
  (1,4,'Shea Butter (raw)',100,'g',10,'Oil Phase',4),
  (1,8,'Castor Oil',100,'ml',10,'Oil Phase',5),
  (1,3,'Sodium Hydroxide (NaOH)',141,'g',0,'Lye Solution',6),
  (1,30,'Lavender EO',2,'oz',0,'Scent',7),
  (1,62,'Soap Paper Band / Belly Band',8,'pcs',0,'Packaging',8),
  (1,63,'Soap Shrink Band',8,'pcs',0,'Packaging',9);

-- Recipe 2: Oakmoss & Amber Soy Candle
INSERT INTO recipe_ingredients (recipe_id,ingredient_id,name,amount,unit,pct,phase,sort_order) VALUES
  (2,7,'Soy Wax (Container — 464)',460,'g',92,'Wax',1),
  (2,6,'Fragrance Oil — Oakmoss & Amber',40,'oz',8,'Fragrance',2),
  (2,57,'Candle Jar — 8oz Glass Amber',3,'pcs',0,'Packaging',3),
  (2,58,'Candle Wick — CD-18 (Pre-tabbed)',3,'pcs',0,'Packaging',4),
  (2,59,'Candle Jar Lid — 70mm Metal',3,'pcs',0,'Packaging',5),
  (2,65,'Wick Sticker (Adhesive Tab)',3,'pcs',0,'Packaging',6);

-- Recipe 3: Night Garden EDP
INSERT INTO recipe_ingredients (recipe_id,ingredient_id,name,amount,unit,pct,phase,sort_order) VALUES
  (3,12,'Perfumer''s Alcohol (SDA 40-B)',70,'ml',70,'Carrier',1),
  (3,13,'Fragrance Oil — Rose & Oud',0.5,'oz',0,'Heart Notes',2),
  (3,30,'Lavender EO',0.3,'oz',0,'Heart Notes',3),
  (3,46,'Agarwood (Oud) EO',0.2,'oz',0,'Base Notes',4),
  (3,47,'Oakmoss Absolute',0.1,'oz',0,'Base Notes',5),
  (3,11,'Vitamin E Oil',2,'ml',0,'Additives',6),
  (3,53,'Glass Spray Bottle — 30ml',1,'pcs',0,'Packaging',7),
  (3,56,'Perfume Outer Box — 30ml',1,'pcs',0,'Packaging',8);

-- Recipe 4: Patchouli Resin Incense
INSERT INTO recipe_ingredients (recipe_id,ingredient_id,name,amount,unit,pct,phase,sort_order) VALUES
  (4,10,'Fragrance Oil — Patchouli Resin',0.5,'oz',0,'Fragrance',1),
  (4,60,'Incense Cone Box — Kraft 10-Pack',4,'pcs',0,'Packaging',2),
  (4,61,'Incense Burner / Ash Catcher',1,'pcs',0,'Packaging',3);

-- Recipe 7: Whipped Shea Body Butter
INSERT INTO recipe_ingredients (recipe_id,ingredient_id,name,amount,unit,pct,phase,sort_order) VALUES
  (7,4,'Shea Butter (raw)',350,'g',70,'Base',1),
  (7,25,'Cocoa Butter',100,'g',20,'Base',2),
  (7,16,'Sweet Almond Oil',40,'ml',8,'Oils',3),
  (7,11,'Vitamin E Oil',10,'ml',2,'Additives',4);

-- Recipe 8: Charcoal M&P Soap
INSERT INTO recipe_ingredients (recipe_id,ingredient_id,name,amount,unit,pct,phase,sort_order) VALUES
  (8,19,'Melt & Pour Soap Base (Shea)',750,'g',0,'Base',1),
  (8,20,'Activated Charcoal',15,'g',0,'Additives',2),
  (8,28,'Tea Tree EO',1,'oz',0,'Scent',3),
  (8,39,'Isopropyl Alcohol 99% (IPA)',30,'ml',0,'Processing',4),
  (8,62,'Soap Paper Band / Belly Band',8,'pcs',0,'Packaging',5),
  (8,63,'Soap Shrink Band',8,'pcs',0,'Packaging',6);

-- Recipe 9: Rose Clay & Goat Milk CP Soap
INSERT INTO recipe_ingredients (recipe_id,ingredient_id,name,amount,unit,pct,phase,sort_order) VALUES
  (9,1,'Coconut Oil (76°)',250,'g',25,'Oil Phase',1),
  (9,2,'Olive Oil (Pomace)',400,'g',40,'Oil Phase',2),
  (9,25,'Cocoa Butter',150,'g',15,'Oil Phase',3),
  (9,16,'Sweet Almond Oil',100,'ml',10,'Oil Phase',4),
  (9,8,'Castor Oil',100,'ml',10,'Oil Phase',5),
  (9,3,'Sodium Hydroxide (NaOH)',138,'g',0,'Lye Solution',6),
  (9,38,'Goat Milk Powder',30,'g',0,'Additives',7),
  (9,31,'Rose Clay',15,'g',0,'Additives',8),
  (9,13,'Fragrance Oil — Rose & Oud',2,'oz',0,'Scent',9),
  (9,62,'Soap Paper Band / Belly Band',8,'pcs',0,'Packaging',10),
  (9,63,'Soap Shrink Band',8,'pcs',0,'Packaging',11);

-- Recipe 10: Lotion Bar
INSERT INTO recipe_ingredients (recipe_id,ingredient_id,name,amount,unit,pct,phase,sort_order) VALUES
  (10,14,'Beeswax (filtered)',90,'g',30,'Wax',1),
  (10,25,'Cocoa Butter',90,'g',30,'Base',2),
  (10,4,'Shea Butter (raw)',60,'g',20,'Base',3),
  (10,21,'Argan Oil',45,'ml',15,'Oils',4),
  (10,11,'Vitamin E Oil',15,'ml',5,'Additives',5);

-- Recipe 12: Classic Castile Liquid Soap
INSERT INTO recipe_ingredients (recipe_id,ingredient_id,name,amount,unit,pct,phase,sort_order) VALUES
  (12,67,'Olive Oil (Extra Virgin)',500,'g',70,'Oil Phase',1),
  (12,1,'Coconut Oil (76°)',100,'g',14,'Oil Phase',2),
  (12,8,'Castor Oil',72,'g',10,'Oil Phase',3),
  (12,68,'Sunflower Oil',43,'g',6,'Oil Phase',4),
  (12,66,'Potassium Hydroxide (KOH)',116,'g',0,'Lye Solution',5),
  (12,73,'Distilled Water (lye)',240,'g',0,'Lye Solution',6),
  (12,73,'Distilled Water (dilution)',800,'g',0,'Dilution',7);

-- Recipe 13: Calendula & Beeswax Balm
INSERT INTO recipe_ingredients (recipe_id,ingredient_id,name,amount,unit,pct,phase,sort_order) VALUES
  (13,16,'Sweet Almond Oil',120,'g',60,'Oils',1),
  (13,69,'Beeswax (pastilles)',30,'g',15,'Wax / Butter',2),
  (13,4,'Shea Butter (raw)',30,'g',15,'Wax / Butter',3),
  (13,70,'Calendula-infused Oil',16,'g',8,'Oils',4),
  (13,11,'Vitamin E Oil',4,'g',2,'Additives',5),
  (13,72,'Lavender EO (ml)',2,'ml',1,'Scent',6),
  (13,71,'Chamomile EO (Roman)',1,'ml',0.5,'Scent',7),
  (13,64,'Tin — 2oz Round (Balm)',7,'pcs',0,'Packaging',8);

-- =============================================================================
-- SEED: BATCHES
-- =============================================================================
INSERT INTO batches (id,product,type,recipe_id,size,unit,batch_date,maker_id,maker_initials,maker_name,status,yield_amount,notes) VALUES
  ('B-0041','Lavender Dream CP Soap','soap_cp',1,1000,'g','2026-02-15',1,'TM','Tyler Mayhall','curing','',''),
  ('B-0042','Oakmoss & Amber Soy Candle','candle',2,500,'g','2026-02-16',2,'KD','Kyle Dalton','ready','3 jars','Great scent throw.'),
  ('B-0043','Night Garden EDP','perfume',3,100,'ml','2026-02-17',1,'TM','Tyler Mayhall','in_process','','Macerating. Check Feb 28.'),
  ('B-0044','Patchouli Resin Incense Cones','incense',4,200,'g','2026-02-17',3,'AS','Alex Sansone','planned','~40 cones',''),
  ('B-0045','Whipped Shea Body Butter','balm',7,500,'g','2026-02-17',1,'TM','Tyler Mayhall','planned','~10 pots','')
ON CONFLICT (id) DO NOTHING;

INSERT INTO batch_status_history (batch_id,status,changed_at) VALUES
  ('B-0041','planned','2026-02-14 09:00:00+00'),
  ('B-0041','in_process','2026-02-15 10:00:00+00'),
  ('B-0041','curing','2026-02-15 14:00:00+00'),
  ('B-0042','planned','2026-02-15 09:00:00+00'),
  ('B-0042','in_process','2026-02-16 10:00:00+00'),
  ('B-0042','ready','2026-02-16 16:00:00+00'),
  ('B-0043','planned','2026-02-16 09:00:00+00'),
  ('B-0043','in_process','2026-02-17 11:00:00+00'),
  ('B-0044','planned','2026-02-17 09:00:00+00'),
  ('B-0045','planned','2026-02-17 09:00:00+00');

-- =============================================================================
-- SEED: PRODUCTS
-- =============================================================================
INSERT INTO products (id,name,type,recipe_id,sku,price,status,notes) VALUES
  (1,'Lavender Dream Bar Soap','soap_cp',1,'SOAP-001',9.00,'active','Our signature bar. 4oz.'),
  (2,'Oakmoss & Amber Soy Candle','candle',2,'CNDL-001',22.00,'active','8oz amber jar, 40hr burn time.'),
  (3,'Night Garden EDP','perfume',3,'PERF-001',68.00,'active','30ml amber spray bottle.')
ON CONFLICT (id) DO NOTHING;

SELECT setval('products_id_seq', (SELECT MAX(id) FROM products));

-- Done
SELECT 'Schema and seed data applied successfully.' AS result;