-- Example query with merchant isolation
SELECT * FROM transactions 
WHERE merchant_id = ? 
  AND branch_id = ?
  AND deleted_at IS NULL;

-- Example FK constraints
ALTER TABLE transactions
    ADD CONSTRAINT fk_transactions_merchant 
    FOREIGN KEY (merchant_id) REFERENCES merchants(id),
    ADD CONSTRAINT fk_transactions_branch 
    FOREIGN KEY (branch_id) REFERENCES branches(id),
    ADD CONSTRAINT fk_transactions_cashier 
    FOREIGN KEY (cashier_id) REFERENCES users(id);

-- Unique per merchant
CREATE UNIQUE INDEX uq_merchant_sku 
    ON inventory(merchant_id, inventory_sku);

CREATE UNIQUE INDEX uq_merchant_warehouse 
    ON warehouses(merchant_id, warehouse_code);

CREATE UNIQUE INDEX uq_merchant_transfer 
    ON inventory_transfers(merchant_id, transfer_code);

-- Merchant filtering (most queries)
CREATE INDEX idx_merchant ON transactions(merchant_id);
CREATE INDEX idx_merchant ON inventory(merchant_id);
CREATE INDEX idx_merchant ON warehouse_stocks(merchant_id);

-- Composite indexes for common queries
CREATE INDEX idx_merchant_branch 
    ON transactions(merchant_id, branch_id);

CREATE INDEX idx_merchant_warehouse_inventory 
    ON warehouse_stocks(merchant_id, warehouse_id, inventory_id);

-- Status filtering
CREATE INDEX idx_status ON transactions(status);
CREATE INDEX idx_merchant_status 
    ON inventory_transfers(merchant_id, status);

-- Date range queries
CREATE INDEX idx_created_at ON transactions(created_at);
CREATE INDEX idx_movement_date ON inventory_movements(movement_date);

-- SLA monitoring
CREATE INDEX idx_status_due 
    ON approval_requests(status, due_at);

SELECT 
    b.branch_code,
    b.branch_name,
    i.inventory_sku,
    i.inventory_name,
    SUM(ws.qty_on_hand) AS total_on_hand,
    SUM(ws.qty_reserved) AS total_reserved,
    SUM(ws.qty_on_hand - ws.qty_reserved) AS total_available
FROM warehouse_stocks ws
JOIN warehouses w ON ws.warehouse_id = w.id
JOIN branches b ON w.branch_id = b.id
JOIN inventory i ON ws.inventory_id = i.id
WHERE ws.merchant_id = ?
  AND b.branch_id = ?
  AND ws.deleted_at IS NULL
GROUP BY b.branch_code, b.branch_name, i.inventory_sku, i.inventory_name;

SELECT 
    t.id,
    t.transaction_code,
    t.grand_total,
    t.status,
    ti.product_id,
    p.product_name,
    ti.quantity,
    ti.unit_price,
    ti.subtotal
FROM transactions t
JOIN transaction_items ti ON t.id = ti.transaction_id
JOIN products p ON ti.product_id = p.id
WHERE t.merchant_id = ?
  AND t.id = ?;

SELECT 
    ar.id,
    ar.doc_code,
    ar.doc_type,
    ar.amount,
    ar.current_level,
    ar.total_levels,
    ar.requested_at,
    ar.due_at,
    af.required_role
FROM approval_requests ar
JOIN approval_flows af ON ar.flow_id = af.id
JOIN approvers a ON af.required_role = a.role
WHERE a.user_id = ?
  AND ar.status = 'PENDING'
  AND ar.current_level = af.level_order
  AND a.is_active = TRUE;

SELECT 
    im.movement_date,
    im.movement_type,
    im.quantity,
    im.reference_type,
    im.reference_code,
    w.warehouse_name,
    i.inventory_name,
    ib.batch_code
FROM inventory_movements im
JOIN warehouses w ON im.warehouse_id = w.id
JOIN inventory i ON im.inventory_id = i.id
LEFT JOIN inventory_batches ib ON im.inventory_batch_id = ib.id
WHERE im.merchant_id = ?
  AND im.inventory_id = ?
ORDER BY im.movement_date DESC
LIMIT 100;

third_party/migrations/atlas/schema/
├── 01_core_tenant.my.hcl           # Merchants, Branches, Users
├── 02_rbac.my.hcl                  # Roles, Permissions
├── 03_master_warehouse.my.hcl      # Warehouses
├── 04_master_data_import.my.hcl    # Inventory, Products
├── 05_transactional.my.hcl         # Transactions, Payments
├── 06_approval_flow.my.hcl         # Approval System
├── 07_inventory_flow.my.hcl        # Warehouse Stocks, Movements
├── 08_promotions.my.hcl            # Bundles, Price Lists
└── 09_accounting.my.hcl            # Chart of Accounts, Journals

# Generate migration diff
atlas migrate diff \
  --dir "file://third_party/migrations/atlas/schema" \
  --to "file://third_party/migrations/atlas/schema" \
  --dev-url "mysql://root:root@localhost:3306/mstore-dev"

# Apply migrations
atlas migrate apply \
  --dir "file://third_party/migrations/atlas/migrations" \
  --url "mysql://root:root@localhost:3306/mstore-monolith"

# Verify schema
atlas schema inspect \
  --url "mysql://root:root@localhost:3306/mstore-monolith"

# Daily full backup
mysqldump \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  mstore-monolith > backup_$(date +%Y%m%d).sql

# Incremental backup (binary logs)
mysqlbinlog mysql-bin.000001 > incremental.sql

# Restore full backup
mysql mstore-monolith < backup_20251013.sql

# Apply binary logs up to specific time
mysqlbinlog --stop-datetime="2025-10-13 14:30:00" \
  mysql-bin.000001 | mysql -u root -p mstore-monolith