Database Naming Guidelines

-- Hidden Physical Table
CREATE TABLE ".products" (
    "product_id" INTEGER PRIMARY KEY,
    "sku" TEXT UNIQUE,
    "unit_price" DECIMAL(10,2)
);

-- Logical Public View
CREATE VIEW product_catalog AS
SELECT sku, unit_price FROM ".products";

-- ===============================================================================
-- APPLICATION: Idempotent JSON Extraction & Persistent Views (DuckDB + SQLite)
-- PATTERN: schema.".table_name"
-- FIX: Removed ON CONFLICT for SQLite compatibility
-- ===============================================================================


-- 1. SETUP & ATTACH
-- surveilr handles the engine, but we ensure extensions are ready
INSTALL sqlite;
LOAD sqlite;

-- Attach target SQLite database
ATTACH 'inventory-system.sqlite.db' AS sqlite_db (TYPE SQLITE);

-- 2. MASTER DATA TABLES (SQLite Persistence)
CREATE TABLE IF NOT EXISTS sqlite_db.".products" (
    "product_id"    INTEGER PRIMARY KEY,
    "sku"           TEXT UNIQUE,
    "category"      TEXT,
    "unit_price"    DECIMAL(10,2)
);

CREATE TABLE IF NOT EXISTS sqlite_db.".warehouses" (
    "warehouse_id"  INTEGER PRIMARY KEY,
    "code"          TEXT UNIQUE,
    "location"      TEXT
);

-- 3. RAW INGEST TABLE (Append-only / Immutable)
CREATE TABLE IF NOT EXISTS sqlite_db.".raw_inventory_ingest" (
    "ingest_id"     INTEGER PRIMARY KEY,
    "source_ref"    TEXT UNIQUE, 
    "payload"       TEXT,        
    "ingested_at"   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 4. IDEMPOTENT SEEDING: MASTER DATA
-- Using WHERE NOT EXISTS instead of ON CONFLICT for SQLite compatibility
INSERT INTO sqlite_db.".products" ("sku", "category", "unit_price")
SELECT 'SKU-' || i, CASE WHEN i % 2 = 0 THEN 'Electronics' ELSE 'Hardware' END, (random() * 500 + 10)::DECIMAL(10,2)
FROM range(1, 21) AS t(i)
WHERE NOT EXISTS (SELECT 1 FROM sqlite_db.".products" WHERE "sku" = 'SKU-' || i);

INSERT INTO sqlite_db.".warehouses" ("code", "location")
SELECT 'WH-001', 'North' WHERE NOT EXISTS (SELECT 1 FROM sqlite_db.".warehouses" WHERE "code" = 'WH-001');
INSERT INTO sqlite_db.".warehouses" ("code", "location")
SELECT 'WH-002', 'South' WHERE NOT EXISTS (SELECT 1 FROM sqlite_db.".warehouses" WHERE "code" = 'WH-002');
INSERT INTO sqlite_db.".warehouses" ("code", "location")
SELECT 'WH-003', 'East'  WHERE NOT EXISTS (SELECT 1 FROM sqlite_db.".warehouses" WHERE "code" = 'WH-003');

-- 5. IDEMPOTENT SEEDING: 200 JSON RECORDS
INSERT INTO sqlite_db.".raw_inventory_ingest" ("source_ref", "payload")
SELECT 
    'TXN-' || i AS "source_ref",
    json_object(
        'product_sku', 'SKU-' || (1 + (i % 20)),
        'warehouse_code', 'WH-00' || (1 + (i % 3)),
        'details', json_object(
            'quantity', CAST(random() * 100 AS INTEGER),
            'condition', CASE WHEN i % 5 = 0 THEN 'Refurbished' ELSE 'New' END
        ),
        'tags', json_array('batch-2026', 'audit-pending')
    ) AS "payload"
FROM range(1, 201) AS t(i)
WHERE NOT EXISTS (SELECT 1 FROM sqlite_db.".raw_inventory_ingest" WHERE "source_ref" = 'TXN-' || i);

-- 6. PERSISTENT VIEWS (Created in DuckDB, stored in SQLite)
-- We use ->> to ensure data is extracted as clean strings
DROP VIEW IF EXISTS main.structured_inventory_logic;

CREATE VIEW main.structured_inventory_logic AS
SELECT 
    r."ingest_id",
    r."source_ref",
    r."payload"->>'$.product_sku' AS "sku",
    r."payload"->>'$.warehouse_code' AS "wh_code",
    CAST(r."payload"->>'$.details.quantity' AS INTEGER) AS "qty",
    p."category",
    CAST(p."unit_price" AS DECIMAL(10,2)) AS "unit_price" -- Explicit Cast
FROM sqlite_db.".raw_inventory_ingest" r
JOIN sqlite_db.".products" p ON r."payload"->>'$.product_sku' = p."sku";


-- 3. EXPORT 1: Materialize structured data into SQLite
-- This physically moves the data into the .db file
DROP TABLE IF EXISTS sqlite_db.".structured_inventory";

CREATE TABLE sqlite_db.".structured_inventory" AS 
SELECT * FROM main.structured_inventory_logic;

-- 7. SUMMARY VIEW
DROP VIEW IF EXISTS main.warehouse_stock_levels;

CREATE VIEW main.warehouse_stock_levels AS
SELECT 
    "wh_code",
    "category",
    COUNT(*) AS "transaction_count",
    SUM("qty") AS "total_on_hand",
    -- Now performing math on (INTEGER * DECIMAL)
    ROUND(SUM(CAST("qty" AS DECIMAL(18,2)) * "unit_price"), 2) AS "inventory_value"
FROM main.structured_inventory_logic
GROUP BY 1, 2;


-- 3. EXPORT 1: Materialize structured data into SQLite
-- This physically moves the data into the .db file
DROP TABLE IF EXISTS sqlite_db.".warehouse_stock_levels";

CREATE TABLE sqlite_db.".warehouse_stock_levels" AS 
SELECT * FROM main.warehouse_stock_levels;

-- 8. VERIFICATION
SELECT * FROM main.warehouse_stock_levels ORDER BY "inventory_value" DESC;

/*
-- ===============================================================================
-- APPLICATION: Fully Idempotent & Immutable Inventory Management (PostgreSQL)
-- PATTERN: schema.".table_name"
-- FEATURES: Unique Source Refs, Append-only Journaling, Master Data Protection
-- ===============================================================================
*/

-- 1. SCHEMA SETUP (Idempotent)
CREATE SCHEMA IF NOT EXISTS inventory;

-- 2. CLEANUP (For a fresh start in a dev environment)
DROP VIEW IF EXISTS inventory.current_stock;
DROP TABLE IF EXISTS inventory.".stock_transactions";
DROP TABLE IF EXISTS inventory.".products";
DROP TABLE IF EXISTS inventory.".locations";

-- 3. MASTER DATA TABLES
-- Table: .locations
CREATE TABLE inventory.".locations" (
    "location_id"   SERIAL PRIMARY KEY,
    "name"          TEXT NOT NULL UNIQUE,
    "zone"          TEXT,
    "created_at"    TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Table: .products
CREATE TABLE inventory.".products" (
    "product_id"    SERIAL PRIMARY KEY,
    "sku"           TEXT UNIQUE NOT NULL,
    "description"   TEXT,
    "category"      TEXT,
    "created_at"    TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- 4. THE TRUTH TABLE (Appendable & Immutable)
CREATE TABLE inventory.".stock_transactions" (
    "transaction_id" SERIAL PRIMARY KEY,
    "source_ref"     TEXT UNIQUE NOT NULL, -- The Idempotency Key (e.g., 'INV-1001')
    "product_id"     INT REFERENCES inventory.".products"("product_id"),
    "location_id"    INT REFERENCES inventory.".locations"("location_id"),
    "quantity_delta" INT NOT NULL,
    "reason_code"    TEXT CHECK ("reason_code" IN ('PURCHASE', 'SALE', 'ADJUSTMENT', 'RETURN')),
    "recorded_by"    TEXT DEFAULT CURRENT_USER,
    "created_at"     TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- 5. PERFORMANCE INDEXES
CREATE INDEX idx_transactions_product ON inventory.".stock_transactions"("product_id");
CREATE INDEX idx_transactions_ref ON inventory.".stock_transactions"("source_ref");

-- 6. IDEMPOTENT BUSINESS LOGIC
-- This function skips processing if the source_ref already exists.
CREATE OR REPLACE FUNCTION inventory."usp_record_movement"(
    p_source_ref TEXT,
    p_sku TEXT,
    p_location_name TEXT,
    p_qty INT,
    p_reason TEXT
) RETURNS VOID AS $$
DECLARE
    v_prod_id INT;
    v_loc_id INT;
BEGIN
    -- Check if this transaction was already processed (Idempotency check)
    IF EXISTS (SELECT 1 FROM inventory.".stock_transactions" WHERE "source_ref" = p_source_ref) THEN
        RAISE NOTICE 'Transaction % already exists. Skipping.', p_source_ref;
        RETURN;
    END IF;

    -- Look up IDs
    SELECT "product_id" INTO v_prod_id FROM inventory.".products" WHERE "sku" = p_sku;
    SELECT "location_id" INTO v_loc_id FROM inventory.".locations" WHERE "name" = p_location_name;

    IF v_prod_id IS NULL OR v_loc_id IS NULL THEN
        RAISE EXCEPTION 'Product SKU (%) or Location Name (%) not found.', p_sku, p_location_name;
    END IF;

    -- Append to the log (Immutable entry)
    INSERT INTO inventory.".stock_transactions" ("source_ref", "product_id", "location_id", "quantity_delta", "reason_code")
    VALUES (p_source_ref, v_prod_id, v_loc_id, p_qty, p_reason);
END;
$$ LANGUAGE plpgsql;

-- 7. CALCULATION VIEW (The State of Truth)
CREATE OR REPLACE VIEW inventory.current_stock AS
SELECT 
    p."sku",
    l."name" AS "location_name",
    SUM(t."quantity_delta") AS "on_hand"
FROM inventory.".products" p
JOIN inventory.".stock_transactions" t ON p."product_id" = t."product_id"
JOIN inventory.".locations" l ON t."location_id" = l."location_id"
GROUP BY p."sku", l."name"
HAVING SUM(t."quantity_delta") <> 0;

-- 8. SEED DATA (20 Idempotent Records)
DO $$
BEGIN
    -- Seed Master Data
    INSERT INTO inventory.".locations" ("name", "zone") VALUES 
    ('Warehouse-A', 'Bulk'), ('Store-Front', 'Retail'), ('Return-Zone', 'Quarantine') ON CONFLICT DO NOTHING;

    INSERT INTO inventory.".products" ("sku", "description", "category") VALUES 
    ('TSHIRT-L', 'Large Cotton T-Shirt', 'Apparel'),
    ('JEANS-32', 'Denim Jeans Size 32', 'Apparel'),
    ('CAP-RED', 'Red Baseball Cap', 'Accessories') ON CONFLICT DO NOTHING;

    -- Seed Transactions (Append-only)
    -- Purchases
    PERFORM inventory."usp_record_movement"('REF-001', 'TSHIRT-L', 'Warehouse-A', 100, 'PURCHASE');
    PERFORM inventory."usp_record_movement"('REF-002', 'JEANS-32', 'Warehouse-A', 50, 'PURCHASE');
    PERFORM inventory."usp_record_movement"('REF-003', 'CAP-RED', 'Warehouse-A', 200, 'PURCHASE');

    -- Stock Movement (Transfer from Warehouse to Store)
    PERFORM inventory."usp_record_movement"('REF-004', 'TSHIRT-L', 'Warehouse-A', -20, 'ADJUSTMENT');
    PERFORM inventory."usp_record_movement"('REF-005', 'TSHIRT-L', 'Store-Front', 20, 'ADJUSTMENT');

    -- Sales
    PERFORM inventory."usp_record_movement"('REF-006', 'TSHIRT-L', 'Store-Front', -1, 'SALE');
    PERFORM inventory."usp_record_movement"('REF-007', 'TSHIRT-L', 'Store-Front', -2, 'SALE');
    PERFORM inventory."usp_record_movement"('REF-008', 'JEANS-32', 'Warehouse-A', -5, 'SALE');
    PERFORM inventory."usp_record_movement"('REF-009', 'CAP-RED', 'Warehouse-A', -10, 'SALE');
    PERFORM inventory."usp_record_movement"('REF-010', 'TSHIRT-L', 'Store-Front', -1, 'SALE');

    -- Returns
    PERFORM inventory."usp_record_movement"('REF-011', 'TSHIRT-L', 'Return-Zone', 1, 'RETURN');

    -- More Transactions to reach 20
    PERFORM inventory."usp_record_movement"('REF-012', 'JEANS-32', 'Warehouse-A', -2, 'SALE');
    PERFORM inventory."usp_record_movement"('REF-013', 'CAP-RED', 'Warehouse-A', -50, 'SALE');
    PERFORM inventory."usp_record_movement"('REF-014', 'CAP-RED', 'Warehouse-A', 10, 'PURCHASE');
    PERFORM inventory."usp_record_movement"('REF-015', 'JEANS-32', 'Warehouse-A', -1, 'SALE');
    PERFORM inventory."usp_record_movement"('REF-016', 'TSHIRT-L', 'Store-Front', -3, 'SALE');
    PERFORM inventory."usp_record_movement"('REF-017', 'CAP-RED', 'Warehouse-A', -2, 'SALE');
    PERFORM inventory."usp_record_movement"('REF-018', 'JEANS-32', 'Warehouse-A', 5, 'PURCHASE');
    PERFORM inventory."usp_record_movement"('REF-019', 'TSHIRT-L', 'Warehouse-A', -5, 'ADJUSTMENT');
    PERFORM inventory."usp_record_movement"('REF-020', 'TSHIRT-L', 'Store-Front', 5, 'ADJUSTMENT');

END $$;

-- 9. VERIFICATION
SELECT * FROM inventory.current_stock;

-- Required for allowing dots/special characters in object names
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
GO

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'sales')
BEGIN
    EXEC('CREATE SCHEMA sales');
END
GO

-- 1. Create Tables using the schema.".tablename" pattern
CREATE TABLE sales.".customers" (
    "customer_id"   INT IDENTITY(1,1) PRIMARY KEY,
    "full_name"     NVARCHAR(255) NOT NULL,
    "email"         NVARCHAR(255) NOT NULL,
    "created_at"    DATETIME2 DEFAULT GETDATE(),
    "created_by"    NVARCHAR(255) DEFAULT SUSER_SNAME()
);

CREATE TABLE sales.".orders" (
    "order_id"      INT IDENTITY(1,1) PRIMARY KEY,
    "customer_id"   INT NOT NULL,
    "order_date"    DATETIME2 DEFAULT GETDATE(),
    "total_amount"  DECIMAL(18, 2),
    "status"        NVARCHAR(50) DEFAULT 'pending',
    CONSTRAINT FK_orders_customer FOREIGN KEY ("customer_id") 
        REFERENCES sales.".customers"("customer_id")
);

-- 2. Create Indexes
CREATE INDEX IX_orders_customer ON sales.".orders"("customer_id");
GO

-- 3. Create Procedure using the pattern
CREATE PROCEDURE sales."usp_create_order"
    @customer_id INT,
    @amount DECIMAL(18,2)
AS
BEGIN
    INSERT INTO sales.".orders" ("customer_id", "total_amount")
    VALUES (@customer_id, @amount);
END;
GO

-- 4. Seed Data
INSERT INTO sales.".customers" ("full_name", "email") 
VALUES ('John Doe', 'john@example.com');

EXEC sales."usp_create_order" @customer_id = 1, @amount = 99.50;

-- 5. Final Query (Note the mandatory double quotes)
SELECT 
    c."full_name", 
    o."total_amount", 
    o."status"
FROM sales.".customers" AS c
JOIN sales.".orders" AS o ON c."customer_id" = o."customer_id";
GO

WITH 
-- Mocking your ".products" table
`".products"` AS (
    SELECT 1 AS product_id, 'SKU-001' AS sku, 'Electronics' AS category, 299.99 AS unit_price
),
-- Mocking your ".raw_inventory_ingest" table
`".raw_inventory_ingest"` AS (
    SELECT 101 AS ingest_id, 'TXN-A1' AS source_ref, '{"product_sku":"SKU-001", "qty":5}' AS payload
)
-- Testing the extraction and join logic
SELECT 
    r.ingest_id,
    JSONExtractString(r.payload, 'product_sku') AS extracted_sku,
    JSONExtractInt(r.payload, 'qty') AS qty,
    p.category,
    p.unit_price * qty AS total_value
FROM `".raw_inventory_ingest"` AS r
JOIN `".products"` AS p ON JSONExtractString(r.payload, 'product_sku') = p.sku;

import datetime
from sqlalchemy import create_engine, Column, Integer, String, DateTime, text
from sqlalchemy.orm import declarative_base, sessionmaker

# 1. Use modern SQLAlchemy 2.0 base
Base = declarative_base()

class Customer(Base):
    # This exactly matches your requirement for leading dots
    # SQLite will create a table named: [sales.".customers"]
    __tablename__ = 'sales.".customers"' 
    
    customer_id = Column(Integer, primary_key=True, autoincrement=True)
    full_name = Column(String(255), nullable=False)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)

# 2. Use 'echo=True' to see the generated SQL in your terminal
engine = create_engine('sqlite:///orm-leadingdot-check.sqlite.db', echo=True)

# 3. Create the table
Base.metadata.create_all(engine)

# 4. Verify insertion
Session = sessionmaker(bind=engine)
with Session() as session:
    new_user = Customer(full_name="Anitha Varghese")
    session.add(new_user)
    session.commit()
    print("\nSuccess! Data inserted into the dotted table.")