Optimise a slow SQL query for Manufacturing

You are an expert in this kind of work, and you know the Manufacturing sector from the inside.

## Your task
This query is slow on a [DATABASE] database at a manufacturing plant running shift-based production lines. Table sizes: [ROW COUNTS]. Explain why it is slow, rewrite it, suggest indexes (with CREATE INDEX statements) and tell me what to look for in EXPLAIN to confirm the improvement.

## About my situation
- Who this is for: plant managers, line operators and procurement teams
- What we must respect: safety rules, ISO 9001 quality standards and fixed production schedules
- How we measure success: OEE and defect rate

## What matters in Manufacturing
- Rules and standards that usually apply: ISO 9001 quality management; worker-safety law (OSHA in the US, the Factories Act and labour codes in India) and ISO 45001; lockout/tagout and machine guarding; ISO 14001 and pollution-control consents; product marks such as CE or BIS certification.
- Common risks to plan for: machine breakdowns; supplier delays and raw-material price swings; quality escapes reaching customers; worker injuries; errors at shift handover.
- Tools commonly used: ERP such as SAP or Tally; MES; CMMS for maintenance; SPC software; Excel.
- Words people in this field use: OEE; takt time; cycle time; WIP; BOM; changeover; kaizen; 5S; SPC; CAPA.
- Rules differ by country. If I have not said where I work, ask me, and do not present one country's law as if it applied everywhere.

## What I will give you
Query:
[PASTE QUERY]
Schema:
[PASTE SCHEMA]

## How to work through it
1. Read the query and schema and explain how the database will execute it.
2. Point out what makes it slow (full scans, bad joins, functions on indexed columns, N+1 patterns).
3. Rewrite the query.
4. Suggest indexes with CREATE INDEX statements and their trade-offs.
5. Explain what to look for in EXPLAIN before and after.

## What to deliver
- Why it is slow
- The rewritten query
- Index statements
- EXPLAIN checklist
- Expected improvement

## Rules
- Make every point specific to my situation and my industry; replace generic advice with detail that fits.
- Use plain language, short sentences, and tables where they make the answer easier to scan.
- Use only the facts I give you. Do not invent figures, laws, sources or quotes. Mark anything you had to assume with "Assumption:".
- If something important is missing, ask me up to 3 short questions before you start.

## Before you answer
Check your draft against this list and fix anything that fails:
- The code works as written: no invented libraries, functions or APIs, and versions are named where they matter.
- It is written for a manufacturing plant running shift-based production lines; nothing in it would read the same for any other business.
- Nothing in it breaks safety rules, ISO 9001 quality standards and fixed production schedules.
- Every item under "What to deliver" is included.