"""
KVPL Sale Contracts — Flask Application
Production-hardened: all QA/security/functional issues resolved.
"""
import csv
import io
import logging
import os
import re
import secrets
import string
import sqlite3
import time

import requests
from datetime import date, timedelta
from functools import wraps
from logging.handlers import RotatingFileHandler
from urllib.parse import urlparse
from html import unescape

from flask import (Flask, render_template, request, jsonify,
                   redirect, url_for, session, flash, g, make_response)
from werkzeug.middleware.proxy_fix import ProxyFix
from werkzeug.security import generate_password_hash, check_password_hash

app = Flask(__name__)

_secret = os.environ.get('SECRET_KEY', '')
if not _secret:
    _secret = secrets.token_hex(32)
    print('\n' + '='*60)
    print('  WARNING: SECRET_KEY not set. Using a random key.')
    print('  ALL SESSIONS WILL BE LOST ON RESTART.')
    print('  Set SECRET_KEY in your environment for production.')
    print('='*60 + '\n')
app.secret_key = _secret

_app_root = os.environ.get('APP_ROOT', '/')
app.config['APPLICATION_ROOT'] = _app_root
app.wsgi_app = ProxyFix(app.wsgi_app, x_for=1, x_proto=1, x_host=1, x_prefix=1)
app.config.update(
    SESSION_COOKIE_NAME='kvpl_contracts_session',
    SESSION_COOKIE_PATH=_app_root,
    SESSION_COOKIE_HTTPONLY=True,
    SESSION_COOKIE_SAMESITE='Lax',
    SESSION_COOKIE_SECURE=os.environ.get('HTTPS', '0') == '1',
    PERMANENT_SESSION_LIFETIME=28800,
)

if not app.debug:
    _log_file = os.environ.get('LOG_FILE', os.path.join(os.path.dirname(__file__), 'app.log'))
    try:
        _fh = RotatingFileHandler(_log_file, maxBytes=10_000_000, backupCount=5)
        _fh.setLevel(logging.WARNING)
        _fh.setFormatter(logging.Formatter('[%(asctime)s] %(levelname)s %(module)s: %(message)s'))
        app.logger.addHandler(_fh)
    except Exception:
        pass

DB = os.environ.get('DB_PATH', '/var/lib/kvpl/contracts.db')

FACTORIES = {
    'DE': 'Dewlakande Factory',
    'PW': 'Panawatte Factory',
    'CF': 'Centrifuged Factory',
    'LV': 'Lavant Factory',
    'DD': 'Dunedin Factory',
}
ROLES = {'admin': 'Administrator', 'editor': 'Editor', 'viewer': 'Viewer'}

WORLD_PRICE_CACHE = {
    'ts': 0,
    'data': None,
}
WORLD_PRICE_CACHE_SECONDS = 900  # 15 minutes
WORLD_PRICE_CSV_URL = (
    'https://docs.google.com/spreadsheets/d/e/'
    '2PACX-1vTk-pqz44pV3vsE8pId-msmbPgIZIQyMblsiHnSEzQ4mPs8d7jqXJ7LYDDcQTXcUg/'
    'pub?output=csv'
)

_login_attempts = {}

def _check_rate_limit(ip):
    import time
    now = time.time()
    attempts = [t for t in _login_attempts.get(ip, []) if now - t < 60]
    _login_attempts[ip] = attempts
    return len(attempts) >= 10

def _record_failed_login(ip):
    import time
    _login_attempts.setdefault(ip, []).append(time.time())

def _clear_failed_login(ip):
    _login_attempts.pop(ip, None)

def _is_safe_redirect(url):
    parsed = urlparse(url)
    return not parsed.netloc and not parsed.scheme

def get_db():
    conn = sqlite3.connect(DB, timeout=15)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    conn.execute("PRAGMA journal_mode = WAL")
    return conn

def init_db():
    conn = get_db()
    conn.executescript('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL UNIQUE,
            full_name TEXT NOT NULL,
            password_hash TEXT NOT NULL,
            role TEXT NOT NULL DEFAULT 'viewer',
            is_active INTEGER NOT NULL DEFAULT 1,
            created_at TEXT DEFAULT (datetime('now')),
            last_login TEXT
        );
        CREATE TABLE IF NOT EXISTS audit_log (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER,
            username TEXT,
            action TEXT NOT NULL,
            entity TEXT,
            entity_id INTEGER,
            detail TEXT,
            ip TEXT,
            ts TEXT DEFAULT (datetime('now'))
        );
        CREATE TABLE IF NOT EXISTS customers (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL UNIQUE,
            contact TEXT,
            created_at TEXT DEFAULT (datetime('now'))
        );
        CREATE TABLE IF NOT EXISTS materials (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL UNIQUE,
            unit TEXT DEFAULT 'kg'
        );
        CREATE TABLE IF NOT EXISTS contracts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            contract_no TEXT NOT NULL UNIQUE,
            sale_order_no TEXT,
            customer_id INTEGER NOT NULL,
            factory_code TEXT NOT NULL,
            broker TEXT,
            price REAL,
            qty REAL NOT NULL,
            wet_qty REAL,
            delivery_start TEXT,
            delivery_end TEXT NOT NULL,
            status TEXT DEFAULT 'open',
            notes TEXT,
            created_at TEXT DEFAULT (datetime('now')),
            updated_at TEXT DEFAULT (datetime('now')),
            created_by INTEGER,
            FOREIGN KEY(customer_id) REFERENCES customers(id)
        );
        CREATE TABLE IF NOT EXISTS contract_allocations (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            contract_id INTEGER NOT NULL,
            factory_code TEXT NOT NULL,
            qty REAL NOT NULL,
            FOREIGN KEY(contract_id) REFERENCES contracts(id) ON DELETE CASCADE
        );
        CREATE TABLE IF NOT EXISTS contract_materials (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            contract_id INTEGER NOT NULL,
            material_id INTEGER NOT NULL,
            qty REAL,
            FOREIGN KEY(contract_id) REFERENCES contracts(id) ON DELETE CASCADE,
            FOREIGN KEY(material_id) REFERENCES materials(id)
        );
        CREATE TABLE IF NOT EXISTS deliveries (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            contract_id INTEGER NOT NULL,
            allocation_id INTEGER,
            delivery_date TEXT NOT NULL,
            invoice_no TEXT NOT NULL,
            qty REAL NOT NULL,
            wet_qty REAL,
            notes TEXT,
            created_at TEXT DEFAULT (datetime('now')),
            created_by INTEGER,
            FOREIGN KEY(contract_id) REFERENCES contracts(id),
            FOREIGN KEY(allocation_id) REFERENCES contract_allocations(id)
        );
    ''')

    conn.executescript('''
        CREATE INDEX IF NOT EXISTS idx_contracts_status        ON contracts(status);
        CREATE INDEX IF NOT EXISTS idx_contracts_delivery_end  ON contracts(delivery_end);
        CREATE INDEX IF NOT EXISTS idx_contracts_customer_id   ON contracts(customer_id);
        CREATE INDEX IF NOT EXISTS idx_contracts_factory_code  ON contracts(factory_code);
        CREATE INDEX IF NOT EXISTS idx_deliveries_contract_id  ON deliveries(contract_id);
        CREATE INDEX IF NOT EXISTS idx_deliveries_alloc_id     ON deliveries(allocation_id);
        CREATE INDEX IF NOT EXISTS idx_allocs_contract_id      ON contract_allocations(contract_id);
        CREATE INDEX IF NOT EXISTS idx_audit_ts                ON audit_log(ts);
    ''')

    existing_cols = [r[1] for r in conn.execute("PRAGMA table_info(contracts)").fetchall()]
    for col, typedef in [('delivery_start','TEXT'),('delivery_end','TEXT'),
                         ('created_by','INTEGER'),('updated_at','TEXT')]:
        if col not in existing_cols:
            conn.execute(f"ALTER TABLE contracts ADD COLUMN {col} {typedef}")

    if 'delivery_period' in existing_cols:
        conn.execute("""UPDATE contracts SET delivery_end = delivery_period
            WHERE (delivery_end IS NULL OR delivery_end = '')
              AND delivery_period IS NOT NULL AND delivery_period != ''""")

    if 'parent_contract_id' in existing_cols:
        for child in conn.execute("SELECT * FROM contracts WHERE parent_contract_id IS NOT NULL").fetchall():
            parent_id = child['parent_contract_id']
            if not conn.execute("SELECT id FROM contract_allocations WHERE contract_id=? AND factory_code=?",
                (parent_id, child['factory_code'])).fetchone():
                conn.execute("INSERT INTO contract_allocations(contract_id,factory_code,qty) VALUES(?,?,?)",
                    (parent_id, child['factory_code'], child['qty']))

    del_cols = [r[1] for r in conn.execute("PRAGMA table_info(deliveries)").fetchall()]
    for col, typedef in [('allocation_id','INTEGER'),('created_by','INTEGER')]:
        if col not in del_cols:
            conn.execute(f"ALTER TABLE deliveries ADD COLUMN {col} {typedef}")

    if 'material_id' in existing_cols:
        for r in conn.execute("SELECT id, material_id, qty FROM contracts WHERE material_id IS NOT NULL").fetchall():
            if not conn.execute("SELECT id FROM contract_materials WHERE contract_id=? AND material_id=?",
                    (r['id'], r['material_id'])).fetchone():
                conn.execute("INSERT INTO contract_materials(contract_id,material_id,qty) VALUES(?,?,?)",
                    (r['id'], r['material_id'], r['qty']))

    if not conn.execute("SELECT id FROM users LIMIT 1").fetchone():
        tmp_pw = ''.join(secrets.choice(string.ascii_letters + string.digits) for _ in range(16))
        conn.execute("INSERT INTO users(username,full_name,password_hash,role) VALUES(?,?,?,?)",
            ('admin', 'Administrator', generate_password_hash(tmp_pw), 'admin'))
        print('\n' + '='*60)
        print(f'  INITIAL ADMIN PASSWORD: {tmp_pw}')
        print('  Change this immediately after first login!')
        print('='*60 + '\n')

    conn.commit()
    conn.close()

init_db()

def audit(action, entity=None, entity_id=None, detail=None):
    try:
        conn = get_db()
        conn.execute(
            "INSERT INTO audit_log(user_id,username,action,entity,entity_id,detail,ip) VALUES(?,?,?,?,?,?,?)",
            (g.user['id'] if g.user else None,
             g.user['username'] if g.user else 'system',
             action, entity, entity_id, detail, request.remote_addr))
        conn.commit(); conn.close()
    except Exception:
        pass

@app.before_request
def load_user():
    g.user = None
    uid = session.get('user_id')
    if uid:
        conn = get_db()
        row = conn.execute("SELECT * FROM users WHERE id=? AND is_active=1", (uid,)).fetchone()
        conn.close()
        g.user = dict(row) if row else None
        if not g.user: session.clear()

def login_required(f):
    @wraps(f)
    def decorated(*args, **kwargs):
        if not g.user:
            next_url = request.script_root + request.path
            if request.query_string:
                next_url += '?' + request.query_string.decode()
            return redirect(url_for('login', next=next_url))
        return f(*args, **kwargs)
    return decorated

def role_required(*roles):
    def decorator(f):
        @wraps(f)
        def decorated(*args, **kwargs):
            if not g.user: return redirect(url_for('login'))
            if g.user['role'] not in roles:
                flash('You do not have permission to perform this action.', 'error')
                return redirect(url_for('dashboard'))
            return f(*args, **kwargs)
        return decorated
    return decorator

def can_edit():
    return g.user and g.user['role'] in ('admin', 'editor')

def can_admin():
    return g.user and g.user['role'] == 'admin'

app.jinja_env.globals.update(can_edit=can_edit, can_admin=can_admin, ROLES=ROLES)

@app.after_request
def security_headers(response):
    response.headers['X-Content-Type-Options'] = 'nosniff'
    response.headers['X-Frame-Options'] = 'SAMEORIGIN'
    response.headers['Referrer-Policy'] = 'strict-origin-when-cross-origin'
    response.headers['X-XSS-Protection'] = '1; mode=block'
    if os.environ.get('HTTPS', '0') == '1':
        response.headers['Strict-Transport-Security'] = 'max-age=31536000; includeSubDomains'
    response.headers['Content-Security-Policy'] = (
        "default-src 'self'; "
        "script-src 'self' 'unsafe-inline' https://cdn.jsdelivr.net; "
        "style-src 'self' 'unsafe-inline' https://cdn.jsdelivr.net https://fonts.googleapis.com; "
        "font-src 'self' https://fonts.gstatic.com https://cdn.jsdelivr.net; "
        "img-src 'self' data:; connect-src 'self';"
    )
    return response

@app.errorhandler(404)
def not_found(e):
    return render_template('error.html', code=404, message='Page not found.'), 404

@app.errorhandler(500)
def server_error(e):
    app.logger.error(f'Server error: {e}')
    return render_template('error.html', code=500, message='An internal error occurred.'), 500

@app.route('/health')
def health():
    try:
        conn = get_db(); conn.execute('SELECT 1').fetchone(); conn.close()
        return jsonify({'status': 'ok'}), 200
    except Exception as ex:
        return jsonify({'status': 'error', 'detail': str(ex)}), 500

# ── Core helpers ──────────────────────────────────────────────────
def due_date(contract):
    return (contract['delivery_end'] or '').strip()

def contract_summary(conn, contract_id):
    c  = conn.execute('SELECT COALESCE(SUM(qty),0) FROM deliveries WHERE contract_id=?', (contract_id,)).fetchone()[0]
    wq = conn.execute('SELECT COALESCE(SUM(wet_qty),0) FROM deliveries WHERE contract_id=?', (contract_id,)).fetchone()[0]
    return c, wq

def get_contract_materials(conn, contract_id):
    return conn.execute('''SELECT cm.id, cm.material_id, cm.qty as mat_qty, m.name, m.unit
        FROM contract_materials cm JOIN materials m ON m.id = cm.material_id
        WHERE cm.contract_id = ? ORDER BY m.name''', (contract_id,)).fetchall()

def get_contract_allocations(conn, contract_id):
    allocs = conn.execute(
        'SELECT * FROM contract_allocations WHERE contract_id=? ORDER BY id',
        (contract_id,)).fetchall()
    result = []
    single_alloc = len(allocs) == 1
    for a in allocs:
        if single_alloc:
            delivered = conn.execute(
                '''SELECT COALESCE(SUM(qty),0) FROM deliveries
                   WHERE contract_id=? AND (allocation_id=? OR allocation_id IS NULL)''',
                (contract_id, a['id'])).fetchone()[0]
        else:
            delivered = conn.execute(
                'SELECT COALESCE(SUM(qty),0) FROM deliveries WHERE allocation_id=?',
                (a['id'],)).fetchone()[0]
        result.append({'id': a['id'], 'factory_code': a['factory_code'],
            'factory_name': FACTORIES.get(a['factory_code'], a['factory_code']),
            'qty': a['qty'], 'delivered': delivered, 'balance': a['qty'] - delivered})
    return result

def _sanitize_csv_cell(value):
    s = str(value) if value is not None else ''
    if s and s[0] in ('=', '+', '-', '@', '\t', '\r'):
        s = "'" + s
    return s

def _build_report_rows(conn, factory_filter, month_filter):
    sql = '''SELECT c.*, cu.name as customer_name FROM contracts c
             JOIN customers cu ON cu.id=c.customer_id WHERE c.status='open' '''
    params = []
    if month_filter:
        sql += " AND strftime('%Y-%m', c.delivery_end)=?"
        params.append(month_filter)
    sql += " ORDER BY c.delivery_end"
    rows_out = []
    for r in conn.execute(sql, params).fetchall():
        mats   = get_contract_materials(conn, r['id'])
        allocs = get_contract_allocations(conn, r['id'])
        dd     = due_date(r)
        mat_names = ', '.join(m['name'] for m in mats) or '-'
        if allocs:
            for a in allocs:
                if factory_filter and a['factory_code'] != factory_filter: continue
                rows_out.append({'contract_no': r['contract_no'],
                    'sale_order_no': r['sale_order_no'] or '',
                    'customer_name': r['customer_name'],
                    'factory_code': a['factory_code'], 'broker': r['broker'] or '',
                    'mat_names': mat_names, 'dd': dd,
                    'delivery_start': r['delivery_start'] or '',
                    'qty': a['qty'], 'delivered': a['delivered'], 'balance': a['balance'],
                    'wet_qty': r['wet_qty'], 'id': r['id'], 'price': r['price'],
                    'month_key': dd[:7] if dd else 'Unknown', 'contract_row': dict(r)})
        else:
            if factory_filter and r['factory_code'] != factory_filter: continue
            delivered_total, _ = contract_summary(conn, r['id'])
            rows_out.append({'contract_no': r['contract_no'],
                'sale_order_no': r['sale_order_no'] or '',
                'customer_name': r['customer_name'],
                'factory_code': r['factory_code'], 'broker': r['broker'] or '',
                'mat_names': mat_names, 'dd': dd,
                'delivery_start': r['delivery_start'] or '',
                'qty': r['qty'], 'delivered': delivered_total,
                'balance': r['qty'] - delivered_total,
                'wet_qty': r['wet_qty'], 'id': r['id'], 'price': r['price'],
                'month_key': dd[:7] if dd else 'Unknown', 'contract_row': dict(r)})
    return rows_out

def _validate_contract_form(d):
    if not d.get('contract_no', '').strip():
        return 'Contract number is required.'
    if not d.get('customer_id', '').strip():
        return 'Customer is required.'
    if not d.get('delivery_end', '').strip():
        return 'Delivery due date is required.'
    alloc_codes = d.getlist('alloc_factory[]')
    alloc_qtys  = d.getlist('alloc_qty[]')
    valid_allocs = [(fc, qty) for fc, qty in zip(alloc_codes, alloc_qtys) if fc and qty]
    if not valid_allocs:
        return 'At least one factory allocation with quantity is required.'
    for fc, qty in valid_allocs:
        if fc not in FACTORIES:
            return f'Invalid factory code: {fc}'
        try:
            if float(qty) <= 0: return 'Allocation quantity must be greater than zero.'
        except (ValueError, TypeError):
            return 'Invalid allocation quantity.'
    return None

def _save_contract_relations(conn, contract_id, form):
    alloc_codes   = form.getlist('alloc_factory[]')
    alloc_qtys    = form.getlist('alloc_qty[]')
    material_ids  = form.getlist('material_id[]')
    material_qtys = form.getlist('material_qty[]')
    new_allocs = {}
    for fc, qty in zip(alloc_codes, alloc_qtys):
        if fc and qty and fc in FACTORIES:
            try: new_allocs[fc] = float(qty)
            except (ValueError, TypeError): pass
    existing = {a['factory_code']: a for a in conn.execute(
        'SELECT * FROM contract_allocations WHERE contract_id=? ORDER BY id',
        (contract_id,)).fetchall()}
    for fc, qty in new_allocs.items():
        if fc in existing:
            conn.execute('UPDATE contract_allocations SET qty=? WHERE id=?', (qty, existing[fc]['id']))
        else:
            conn.execute('INSERT INTO contract_allocations(contract_id,factory_code,qty) VALUES(?,?,?)',
                (contract_id, fc, qty))
    for fc, old_alloc in existing.items():
        if fc not in new_allocs:
            dcnt = conn.execute('SELECT COUNT(*) FROM deliveries WHERE allocation_id=?',
                (old_alloc['id'],)).fetchone()[0]
            if dcnt == 0:
                conn.execute('DELETE FROM contract_allocations WHERE id=?', (old_alloc['id'],))
            else:
                flash(f'Allocation for {fc} kept (has {dcnt} deliveries). Update qty manually if needed.', 'warn')
    conn.execute('DELETE FROM contract_materials WHERE contract_id=?', (contract_id,))
    for mid, mqty in zip(material_ids, material_qtys):
        if mid:
            try: mqty_val = float(mqty) if mqty else None
            except (ValueError, TypeError): mqty_val = None
            conn.execute('INSERT INTO contract_materials(contract_id,material_id,qty) VALUES(?,?,?)',
                (contract_id, int(mid), mqty_val))

def _parse_form_totals(form):
    alloc_codes = form.getlist('alloc_factory[]')
    alloc_qtys  = form.getlist('alloc_qty[]')
    primary_factory = next((fc for fc in alloc_codes if fc and fc in FACTORIES),
        form.get('factory_code', ''))
    total_qty = sum(float(q) for q, fc in zip(alloc_qtys, alloc_codes)
        if q and fc and fc in FACTORIES)
    return primary_factory, total_qty

def _validate_delivery_alloc(conn, cid, alloc_id):
    if alloc_id is None: return True
    return conn.execute(
        'SELECT id FROM contract_allocations WHERE id=? AND contract_id=?',
        (alloc_id, cid)).fetchone() is not None


def parse_world_price_csv(text):
    reader = csv.reader(io.StringIO(text))
    raw_rows = list(reader)
    if not raw_rows:
        return []

    keys = ['SMR20', 'SIR20', 'STR20', 'RSS3', 'Latex']

    def norm(s):
        return re.sub(r'[^a-z0-9]+', '', (s or '').strip().lower())

    def detect_key(header):
        h = norm(header)

        if 'smr20' in h or ('smr' in h and '20' in h):
            return 'SMR20'
        if 'sir20' in h or ('sir' in h and '20' in h):
            return 'SIR20'
        if 'str20' in h or ('str' in h and '20' in h):
            return 'STR20'
        if 'rss3' in h or ('rss' in h and '3' in h):
            return 'RSS3'
        if 'latex' in h:
            return 'Latex'
        return None

    def to_num(v):
        s = str(v or '').strip()
        s = s.replace(',', '')
        s = re.sub(r'[^0-9.+-]', '', s)
        try:
            return float(s) if s else None
        except ValueError:
            return None

    header_index = None
    col_idx = {}

    # scan first 15 rows to find actual header row
    for i, row in enumerate(raw_rows[:15]):
        temp = {}
        for j, cell in enumerate(row):
            k = detect_key(cell)
            if k and k not in temp:
                temp[k] = j

        first_cell = (row[0] if row else '').strip().lower()
        has_date = 'date' in first_cell or first_cell in ('date', 'day')

        if has_date and len(temp) >= 3:
            header_index = i
            col_idx = temp
            break

    # fallback: best row with most matching headers
    if header_index is None:
        best_count = 0
        for i, row in enumerate(raw_rows[:15]):
            temp = {}
            for j, cell in enumerate(row):
                k = detect_key(cell)
                if k and k not in temp:
                    temp[k] = j
            if len(temp) > best_count:
                best_count = len(temp)
                header_index = i
                col_idx = temp

    if header_index is None:
        return []

    data = []
    for row in raw_rows[header_index + 1:]:
        if not row:
            continue

        date_val = (row[0] if len(row) > 0 else '').strip()
        if not date_val:
            continue

        entry = {'date': date_val}
        non_null_count = 0

        for k in keys:
            idx = col_idx.get(k)
            val = row[idx] if idx is not None and idx < len(row) else ''
            num = to_num(val)
            entry[k] = num
            if num is not None:
                non_null_count += 1

        # skip junk rows
        if non_null_count == 0:
            continue

        data.append(entry)

    return data[-60:]


def parse_world_prices_from_anrpc_html(html_text):
    text = re.sub(r'<script\b[^>]*>.*?</script>', ' ', html_text, flags=re.I | re.S)
    text = re.sub(r'<style\b[^>]*>.*?</style>', ' ', text, flags=re.I | re.S)
    text = re.sub(r'<[^>]+>', '\n', text)
    text = unescape(text)
    lines = [re.sub(r'\s+', ' ', ln).strip() for ln in text.splitlines()]
    lines = [ln for ln in lines if ln]

    # Find the Daily Prices section
    start = 0
    for i, ln in enumerate(lines):
        if 'Daily Prices of Natural Rubber' in ln:
            start = i
            break
    lines = lines[start:start + 120]

    date_pat = re.compile(r'^\d{1,2}/\d{1,2}/\d{4}$')
    num_pat = re.compile(r'^-?\d+(?:\.\d+)?$')
    dates = [ln for ln in lines if date_pat.match(ln)]
    if len(dates) < 2:
        raise ValueError('Could not find current and previous trading dates on ANRPC page.')
    prev_date, latest_date = dates[0], dates[1]

    labels = {
        'SMR 20': 'SMR20',
        'STR 20': 'STR20',
        'SIR 20': 'SIR20',
        'BKK (RSS3)': 'RSS3',
        'RSS3': 'RSS3',
        'Latex-in-bulk': 'Latex',
        'Latex in Bulk': 'Latex',
        'Latex': 'Latex',
    }

    values = {}
    for i, ln in enumerate(lines):
        if ln in labels:
            nums = []
            for j in range(i + 1, min(i + 10, len(lines))):
                if num_pat.match(lines[j]):
                    nums.append(float(lines[j]))
                    if len(nums) == 2:
                        break
            if len(nums) >= 2:
                values[labels[ln]] = {'previous': nums[0], 'latest': nums[1]}

    if len(values) < 4:
        raise ValueError('Could not parse enough price rows from ANRPC page.')

    latest = {'date': latest_date}
    previous = {'date': prev_date}
    for key in ['SMR20', 'SIR20', 'STR20', 'RSS3', 'Latex']:
        latest[key] = values.get(key, {}).get('latest')
        previous[key] = values.get(key, {}).get('previous')

    return [previous, latest]


def get_world_prices(force=False):
    now = time.time()
    if (
        not force and
        WORLD_PRICE_CACHE['data'] is not None and
        now - WORLD_PRICE_CACHE['ts'] < WORLD_PRICE_CACHE_SECONDS
    ):
        return WORLD_PRICE_CACHE['data']

    errors = []

    # Primary source: published CSV / Google Sheet export
    try:
        response = requests.get(WORLD_PRICE_CSV_URL, timeout=20)
        response.raise_for_status()
        data = parse_world_price_csv(response.text)
        if data:
            WORLD_PRICE_CACHE['ts'] = now
            WORLD_PRICE_CACHE['data'] = data
            return data
        errors.append('CSV source returned no rows')
    except Exception as ex:
        errors.append(f'CSV source failed: {ex}')

    # Fallback source: scrape current values from ANRPC daily prices page
    try:
        response = requests.get('https://www.anrpc.org/anrpc-daily-price', timeout=20)
        response.raise_for_status()
        data = parse_world_prices_from_anrpc_html(response.text)
        if data:
            WORLD_PRICE_CACHE['ts'] = now
            WORLD_PRICE_CACHE['data'] = data
            return data
        errors.append('ANRPC page returned no parsed rows')
    except Exception as ex:
        errors.append(f'ANRPC fallback failed: {ex}')

    raise ValueError(' | '.join(errors))


# ── Auth routes ───────────────────────────────────────────────────
@app.route('/login', methods=['GET', 'POST'])
def login():
    if g.user: return redirect(url_for('dashboard'))
    error = None
    if request.method == 'POST':
        ip = request.remote_addr
        if _check_rate_limit(ip):
            error = 'Too many failed attempts. Wait a minute and try again.'
        else:
            username = request.form.get('username', '').strip().lower()
            password = request.form.get('password', '')
            conn = get_db()
            user = conn.execute("SELECT * FROM users WHERE username=? AND is_active=1",
                (username,)).fetchone()
            if user and check_password_hash(user['password_hash'], password):
                conn.execute("UPDATE users SET last_login=datetime('now') WHERE id=?", (user['id'],))
                conn.commit(); conn.close()
                _clear_failed_login(ip)
                session.clear()
                session['user_id'] = user['id']
                session.permanent = True
                g.user = dict(user)
                audit('login')
                nxt = request.form.get('next', '') or url_for('dashboard')
                if not _is_safe_redirect(nxt): nxt = url_for('dashboard')
                return redirect(nxt)
            conn.close()
            _record_failed_login(ip)
            error = 'Invalid username or password.'
    return render_template('login.html', error=error, next=request.args.get('next', ''))

@app.route('/logout', methods=['POST'])
def logout():
    audit('logout')
    session.clear()
    return redirect(url_for('login'))

@app.route('/profile', methods=['GET', 'POST'])
@login_required
def profile():
    error = success = None
    if request.method == 'POST':
        full_name = request.form.get('full_name', '').strip()
        cur_pw    = request.form.get('current_password', '')
        new_pw    = request.form.get('new_password', '')
        conn = get_db()
        user = conn.execute("SELECT * FROM users WHERE id=?", (g.user['id'],)).fetchone()
        if not full_name:
            error = 'Full name cannot be empty.'
        elif new_pw and not check_password_hash(user['password_hash'], cur_pw):
            error = 'Current password is incorrect.'
        elif new_pw and len(new_pw) < 8:
            error = 'New password must be at least 8 characters.'
        if not error:
            conn.execute("UPDATE users SET full_name=? WHERE id=?", (full_name, g.user['id']))
            if new_pw:
                conn.execute("UPDATE users SET password_hash=? WHERE id=?",
                    (generate_password_hash(new_pw), g.user['id']))
                audit('change_password')
                success = 'Profile and password updated.'
            else:
                success = 'Profile updated.'
            conn.commit()
        conn.close()
    return render_template('profile.html', error=error, success=success)

@app.route('/admin/users')
@login_required
@role_required('admin')
def admin_users():
    conn = get_db()
    users = conn.execute("SELECT * FROM users ORDER BY username").fetchall()
    conn.close()
    return render_template('admin_users.html', users=users)

@app.route('/admin/users/new', methods=['GET', 'POST'])
@login_required
@role_required('admin')
def admin_new_user():
    error = None
    if request.method == 'POST':
        username  = request.form.get('username', '').strip().lower()
        full_name = request.form.get('full_name', '').strip()
        password  = request.form.get('password', '')
        role      = request.form.get('role', 'viewer')
        if not username or not full_name or not password:
            error = 'All fields are required.'
        elif not re.match(r'^[a-z0-9_]+$', username):
            error = 'Username: lowercase letters, numbers and underscores only.'
        elif len(password) < 8:
            error = 'Password must be at least 8 characters.'
        elif role not in ROLES:
            error = 'Invalid role.'
        else:
            conn = get_db()
            try:
                conn.execute("INSERT INTO users(username,full_name,password_hash,role) VALUES(?,?,?,?)",
                    (username, full_name, generate_password_hash(password), role))
                conn.commit()
                audit('create_user', 'user', None, f'Created {username} ({role})')
                flash(f'User "{username}" created.', 'success')
                conn.close()
                return redirect(url_for('admin_users'))
            except sqlite3.IntegrityError:
                error = f'Username "{username}" is already taken.'
            conn.close()
    return render_template('admin_user_form.html', user=None, error=error)

@app.route('/admin/users/<int:uid>/edit', methods=['GET', 'POST'])
@login_required
@role_required('admin')
def admin_edit_user(uid):
    conn = get_db()
    user = conn.execute("SELECT * FROM users WHERE id=?", (uid,)).fetchone()
    conn.close()
    if not user:
        flash('User not found.', 'error')
        return redirect(url_for('admin_users'))
    error = None
    if request.method == 'POST':
        full_name = request.form.get('full_name', '').strip()
        role      = request.form.get('role', 'viewer')
        is_active = 1 if request.form.get('is_active') else 0
        new_pw    = request.form.get('new_password', '')
        if not full_name: error = 'Full name is required.'
        elif role not in ROLES: error = 'Invalid role.'
        elif uid == g.user['id'] and not is_active: error = 'You cannot deactivate your own account.'
        elif new_pw and len(new_pw) < 8: error = 'Password must be at least 8 characters.'
        if not error:
            conn = get_db()
            conn.execute("UPDATE users SET full_name=?, role=?, is_active=? WHERE id=?",
                (full_name, role, is_active, uid))
            if new_pw:
                conn.execute("UPDATE users SET password_hash=? WHERE id=?",
                    (generate_password_hash(new_pw), uid))
            conn.commit()
            audit('edit_user', 'user', uid, f'Edited {user["username"]}')
            flash(f'User "{user["username"]}" updated.', 'success')
            conn.close()
            return redirect(url_for('admin_users'))
    return render_template('admin_user_form.html', user=user, error=error)

@app.route('/admin/users/<int:uid>/delete', methods=['POST'])
@login_required
@role_required('admin')
def admin_delete_user(uid):
    if uid == g.user['id']:
        flash('You cannot delete your own account.', 'error')
        return redirect(url_for('admin_users'))
    conn = get_db()
    user = conn.execute("SELECT username FROM users WHERE id=?", (uid,)).fetchone()
    if user:
        conn.execute("DELETE FROM users WHERE id=?", (uid,))
        conn.commit()
        audit('delete_user', 'user', uid, f'Deleted {user["username"]}')
        flash(f'User "{user["username"]}" deleted.', 'success')
    conn.close()
    return redirect(url_for('admin_users'))

@app.route('/admin/audit')
@login_required
@role_required('admin')
def admin_audit():
    try:
        page = max(1, int(request.args.get('page', 1)))
    except (ValueError, TypeError):
        page = 1
    per   = 50
    conn  = get_db()
    total = conn.execute("SELECT COUNT(*) FROM audit_log").fetchone()[0]
    logs  = conn.execute("SELECT * FROM audit_log ORDER BY ts DESC LIMIT ? OFFSET ?",
        (per, (page-1)*per)).fetchall()
    conn.close()
    pages = max(1, (total + per - 1) // per)
    return render_template('admin_audit.html', logs=logs, page=page, pages=pages, total=total)

# ── Dashboard ─────────────────────────────────────────────────────
@app.route('/')
@login_required
def dashboard():
    conn  = get_db()
    today = date.today().isoformat()
    fmap  = {}
    def _ensure(fc):
        if fc not in fmap:
            fmap[fc] = {'total_qty':0.0,'delivered':0.0,
                'contract_ids':set(),'open_ids':set(),'overdue_ids':set()}
    def _add(fc, contract_id, alloc_qty, alloc_delivered, status, due):
        _ensure(fc)
        fmap[fc]['total_qty']  += alloc_qty
        fmap[fc]['delivered']  += alloc_delivered
        fmap[fc]['contract_ids'].add(contract_id)
        if status == 'open':
            fmap[fc]['open_ids'].add(contract_id)
            if due and due < today: fmap[fc]['overdue_ids'].add(contract_id)

    for r in conn.execute('''SELECT ca.factory_code, ca.qty AS alloc_qty, c.id AS contract_id,
               c.status, c.delivery_end AS due,
               COALESCE((SELECT SUM(d.qty) FROM deliveries d WHERE d.allocation_id = ca.id), 0) AS alloc_delivered
        FROM contract_allocations ca JOIN contracts c ON c.id = ca.contract_id''').fetchall():
        _add(r['factory_code'], r['contract_id'], r['alloc_qty'],
             r['alloc_delivered'], r['status'], (r['due'] or '').strip())

    for r in conn.execute('''SELECT c.factory_code, c.qty AS alloc_qty, c.id AS contract_id,
               c.status, c.delivery_end AS due,
               COALESCE((SELECT SUM(d.qty) FROM deliveries d WHERE d.contract_id = c.id), 0) AS alloc_delivered
        FROM contracts c WHERE NOT EXISTS
            (SELECT 1 FROM contract_allocations ca WHERE ca.contract_id = c.id)''').fetchall():
        _add(r['factory_code'], r['contract_id'], r['alloc_qty'],
             r['alloc_delivered'], r['status'], (r['due'] or '').strip())

    factory_summary = []
    for fc in FACTORIES:
        if fc not in fmap: continue
        fm = fmap[fc]
        factory_summary.append({'factory_code': fc, 'factory_name': FACTORIES[fc],
            'total': len(fm['contract_ids']), 'open_count': len(fm['open_ids']),
            'overdue_count': len(fm['overdue_ids']), 'total_qty': fm['total_qty'],
            'delivered': fm['delivered'], 'balance': fm['total_qty'] - fm['delivered']})

    def enrich(rows, split=False):
        out = []
        for r in rows:
            mats = get_contract_materials(conn, r['id'])
            mat_names = ', '.join(m['name'] for m in mats) or '-'
            dd = due_date(r)
            allocs = get_contract_allocations(conn, r['id'])
            if split and allocs:
                for a in allocs:
                    balance = max(a['qty'] - a['delivered'], 0)
                    out.append({**dict(r), 'material_names': mat_names, 'delivery_due': dd,
                        'factory_code': a['factory_code'], 'factory_name': a['factory_name'],
                        'qty': a['qty'], 'delivered': a['delivered'],
                        'balance': balance, 'allocation_id': a['id']})
            else:
                total_delivered = conn.execute(
                    'SELECT COALESCE(SUM(qty),0) FROM deliveries WHERE contract_id=?', (r['id'],)
                ).fetchone()[0]
                balance = max(r['qty'] - total_delivered, 0)
                out.append({**dict(r), 'material_names': mat_names, 'delivery_due': dd,
                    'factory_name': FACTORIES.get(r['factory_code'], r['factory_code']),
                    'delivered': total_delivered, 'balance': balance, 'allocation_id': None})
        return out

    recent  = enrich(conn.execute('''SELECT c.*, cu.name as customer_name FROM contracts c
        JOIN customers cu ON cu.id = c.customer_id
        ORDER BY c.created_at DESC LIMIT 10''').fetchall(), split=True)
    overdue = enrich(conn.execute('''SELECT c.*, cu.name as customer_name FROM contracts c
        JOIN customers cu ON cu.id = c.customer_id
        WHERE c.status='open' AND c.delivery_end != '' AND c.delivery_end < ?
        ORDER BY c.delivery_end ASC''', (today,)).fetchall(), split=True)
    next30  = (date.today() + timedelta(days=30)).isoformat()
    upcoming= enrich(conn.execute('''SELECT c.*, cu.name as customer_name FROM contracts c
        JOIN customers cu ON cu.id = c.customer_id
        WHERE c.status='open' AND c.delivery_end >= ? AND c.delivery_end <= ?
        ORDER BY c.delivery_end ASC''', (today, next30)).fetchall(), split=True)
    conn.close()
    return render_template('dashboard.html', factory_summary=factory_summary, recent=recent,
        overdue=overdue, upcoming=upcoming, factories=FACTORIES, today=today)

# ── Contracts ─────────────────────────────────────────────────────
@app.route('/contracts')
@login_required
def contracts():
    conn = get_db()
    status_filter  = request.args.get('status', 'open')
    factory_filter = request.args.get('factory', '')
    month_filter   = request.args.get('month', '')
    search         = request.args.get('search', '').strip()
    sql = '''SELECT c.*, cu.name as customer_name FROM contracts c
             JOIN customers cu ON cu.id=c.customer_id WHERE c.status=?'''
    params = [status_filter]
    if month_filter:
        sql += " AND strftime('%Y-%m', c.delivery_end)=?"
        params.append(month_filter)
    if search:
        sql += ' AND (c.contract_no LIKE ? OR cu.name LIKE ? OR c.sale_order_no LIKE ? OR c.broker LIKE ?)'
        s = f'%{search}%'; params += [s,s,s,s]
    sql += " ORDER BY c.delivery_end ASC"
    today = date.today().isoformat()
    contracts_data = []
    for r in conn.execute(sql, params).fetchall():
        mats   = get_contract_materials(conn, r['id'])
        allocs = get_contract_allocations(conn, r['id'])
        dd     = due_date(r)
        mat_names = ', '.join(m['name'] for m in mats) or '-'
        is_overdue = bool(dd) and dd < today and r['status'] == 'open'
        if allocs:
            for a in allocs:
                if factory_filter and a['factory_code'] != factory_filter: continue
                contracts_data.append({**dict(r), 'qty': a['qty'], 'delivered': a['delivered'],
                    'wet_delivered': None, 'balance': a['balance'], 'overdue': is_overdue,
                    'factory_code': a['factory_code'], 'factory_name': a['factory_name'],
                    'material_names': mat_names, 'delivery_due': dd,
                    'allocations': allocs, 'allocation_id': a['id']})
        else:
            if factory_filter and r['factory_code'] != factory_filter: continue
            delivered_total, _ = contract_summary(conn, r['id'])
            contracts_data.append({**dict(r), 'delivered': delivered_total,
                'wet_delivered': None, 'balance': r['qty'] - delivered_total,
                'overdue': is_overdue,
                'factory_name': FACTORIES.get(r['factory_code'], r['factory_code']),
                'material_names': mat_names, 'delivery_due': dd,
                'allocations': allocs, 'allocation_id': None})
    conn.close()
    return render_template('contracts.html', contracts=contracts_data, factories=FACTORIES,
        status_filter=status_filter, factory_filter=factory_filter,
        month_filter=month_filter, search=search)

@app.route('/contracts/new', methods=['GET', 'POST'])
@login_required
@role_required('admin', 'editor')
def new_contract():
    conn = get_db()
    if request.method == 'POST':
        d = request.form
        err = _validate_contract_form(d)
        if err:
            flash(err, 'error')
            customers = conn.execute('SELECT * FROM customers ORDER BY name').fetchall()
            materials = conn.execute('SELECT * FROM materials ORDER BY name').fetchall()
            conn.close()
            return render_template('contract_form.html', customers=customers, materials=materials,
                factories=FACTORIES, contract=None, contract_materials=[], contract_allocations=[])
        primary_factory, total_qty = _parse_form_totals(d)
        try:
            conn.execute('''INSERT INTO contracts
                (contract_no, sale_order_no, customer_id, factory_code, broker, price,
                 qty, wet_qty, delivery_start, delivery_end, notes, created_by)
                VALUES(?,?,?,?,?,?,?,?,?,?,?,?)''',
                (d['contract_no'].strip(), d.get('sale_order_no') or None,
                 int(d['customer_id']), primary_factory,
                 d.get('broker') or None, d.get('price') or None,
                 total_qty, d.get('wet_qty') or None,
                 d.get('delivery_start') or None, d['delivery_end'].strip(),
                 d.get('notes') or None, g.user['id']))
            new_id = conn.execute('SELECT last_insert_rowid()').fetchone()[0]
            _save_contract_relations(conn, new_id, d)
            conn.commit()
            audit('create_contract', 'contract', new_id, d['contract_no'])
            conn.close()
            flash(f'Contract {d["contract_no"]} created.', 'success')
            return redirect(url_for('view_contract', cid=new_id))
        except sqlite3.IntegrityError:
            conn.close()
            flash(f'Contract number "{d["contract_no"]}" already exists.', 'error')
            return redirect(url_for('new_contract'))
    customers = conn.execute('SELECT * FROM customers ORDER BY name').fetchall()
    materials = conn.execute('SELECT * FROM materials ORDER BY name').fetchall()
    conn.close()
    return render_template('contract_form.html', customers=customers, materials=materials,
        factories=FACTORIES, contract=None, contract_materials=[], contract_allocations=[])

@app.route('/contracts/<int:cid>/edit', methods=['GET', 'POST'])
@login_required
@role_required('admin', 'editor')
def edit_contract(cid):
    conn = get_db()
    if request.method == 'POST':
        d = request.form
        err = _validate_contract_form(d)
        if err:
            flash(err, 'error')
            contract        = conn.execute('SELECT * FROM contracts WHERE id=?', (cid,)).fetchone()
            customers       = conn.execute('SELECT * FROM customers ORDER BY name').fetchall()
            materials       = conn.execute('SELECT * FROM materials ORDER BY name').fetchall()
            contract_mats   = get_contract_materials(conn, cid)
            contract_allocs = get_contract_allocations(conn, cid)
            conn.close()
            return render_template('contract_form.html', customers=customers, materials=materials,
                factories=FACTORIES, contract=contract,
                contract_materials=contract_mats, contract_allocations=contract_allocs)
        primary_factory, total_qty = _parse_form_totals(d)
        conn.execute('''UPDATE contracts SET contract_no=?, sale_order_no=?, customer_id=?,
            factory_code=?, broker=?, price=?, qty=?, wet_qty=?, delivery_start=?,
            delivery_end=?, notes=?, updated_at=datetime('now') WHERE id=?''',
            (d['contract_no'].strip(), d.get('sale_order_no') or None, int(d['customer_id']),
             primary_factory, d.get('broker') or None, d.get('price') or None,
             total_qty, d.get('wet_qty') or None,
             d.get('delivery_start') or None, d['delivery_end'].strip(),
             d.get('notes') or None, cid))
        _save_contract_relations(conn, cid, d)
        conn.commit()
        delivered_total, _ = contract_summary(conn, cid)
        if delivered_total > total_qty:
            flash(f'Warning: {delivered_total:,.3f} kg already delivered exceeds '
                  f'new declared qty of {total_qty:,.3f} kg.', 'warn')
        audit('edit_contract', 'contract', cid, d['contract_no'])
        conn.close()
        flash('Contract updated.', 'success')
        return redirect(url_for('view_contract', cid=cid))
    contract = conn.execute('SELECT * FROM contracts WHERE id=?', (cid,)).fetchone()
    if not contract:
        conn.close(); flash('Contract not found.', 'error'); return redirect(url_for('contracts'))
    customers       = conn.execute('SELECT * FROM customers ORDER BY name').fetchall()
    materials       = conn.execute('SELECT * FROM materials ORDER BY name').fetchall()
    contract_mats   = get_contract_materials(conn, cid)
    contract_allocs = get_contract_allocations(conn, cid)
    conn.close()
    return render_template('contract_form.html', customers=customers, materials=materials,
        factories=FACTORIES, contract=contract,
        contract_materials=contract_mats, contract_allocations=contract_allocs)

@app.route('/contracts/<int:cid>')
@login_required
def view_contract(cid):
    conn = get_db()
    contract = conn.execute('''SELECT c.*, cu.name as customer_name
        FROM contracts c JOIN customers cu ON cu.id=c.customer_id WHERE c.id=?''', (cid,)).fetchone()
    if not contract:
        conn.close(); flash('Contract not found.', 'error'); return redirect(url_for('contracts'))
    contract_mats = get_contract_materials(conn, cid)
    allocations   = get_contract_allocations(conn, cid)
    deliveries    = conn.execute('''SELECT d.*, ca.factory_code as alloc_factory, u.full_name as created_by_name
        FROM deliveries d LEFT JOIN contract_allocations ca ON ca.id = d.allocation_id
        LEFT JOIN users u ON u.id = d.created_by
        WHERE d.contract_id=? ORDER BY d.delivery_date, d.id''', (cid,)).fetchall()
    creator = None
    if contract['created_by']:
        cr = conn.execute("SELECT full_name FROM users WHERE id=?", (contract['created_by'],)).fetchone()
        creator = cr['full_name'] if cr else None
    delivered     = sum(d['qty'] for d in deliveries)
    wet_delivered = sum((d['wet_qty'] or 0) for d in deliveries)
    balance       = contract['qty'] - delivered
    has_cf_alloc  = any(a['factory_code'] == 'CF' for a in allocations)
    conn.close()
    return render_template('contract_view.html', contract=contract, contract_mats=contract_mats,
        allocations=allocations, deliveries=deliveries, delivered=delivered,
        wet_delivered=wet_delivered, balance=balance, has_cf_alloc=has_cf_alloc,
        today=date.today().isoformat(), delivery_due=due_date(contract),
        factories=FACTORIES, creator=creator,
        factory_name=FACTORIES.get(contract['factory_code'], contract['factory_code']))

@app.route('/contracts/<int:cid>/toggle_status', methods=['POST'])
@login_required
@role_required('admin', 'editor')
def toggle_status(cid):
    conn = get_db()
    row = conn.execute('SELECT status FROM contracts WHERE id=?', (cid,)).fetchone()
    if not row:
        conn.close(); flash('Contract not found.', 'error'); return redirect(url_for('contracts'))
    cur = row['status']
    new_status = 'closed' if cur == 'open' else 'open'
    conn.execute("UPDATE contracts SET status=?, updated_at=datetime('now') WHERE id=?", (new_status, cid))
    conn.commit()
    audit('toggle_status', 'contract', cid, f'{cur} -> {new_status}')
    conn.close()
    referrer = request.referrer or ''
    safe = referrer if _is_safe_redirect(referrer) else ''
    return redirect(safe or url_for('view_contract', cid=cid))

@app.route('/contracts/<int:cid>/duplicate', methods=['POST'])
@login_required
@role_required('admin', 'editor')
def duplicate_contract(cid):
    conn = get_db()
    row = conn.execute('SELECT * FROM contracts WHERE id=?', (cid,)).fetchone()
    if not row:
        conn.close(); flash('Contract not found.', 'error'); return redirect(url_for('contracts'))
    src = dict(row)
    src_mats = get_contract_materials(conn, cid)
    src_allocs = get_contract_allocations(conn, cid)
    suffix = 1; new_no = f"{src['contract_no']}-COPY{suffix}"
    while conn.execute('SELECT id FROM contracts WHERE contract_no=?', (new_no,)).fetchone():
        suffix += 1; new_no = f"{src['contract_no']}-COPY{suffix}"
    conn.execute('''INSERT INTO contracts(contract_no, sale_order_no, customer_id, factory_code,
         broker, price, qty, wet_qty, delivery_start, delivery_end, notes, created_by)
         VALUES(?,?,?,?,?,?,?,?,?,?,?,?)''',
        (new_no, None, src['customer_id'], src['factory_code'], src['broker'],
         src['price'], src['qty'], src['wet_qty'], src.get('delivery_start'),
         src.get('delivery_end', ''), src['notes'], g.user['id']))
    new_id = conn.execute('SELECT last_insert_rowid()').fetchone()[0]
    for a in src_allocs:
        conn.execute('INSERT INTO contract_allocations(contract_id,factory_code,qty) VALUES(?,?,?)',
            (new_id, a['factory_code'], a['qty']))
    for m in src_mats:
        conn.execute('INSERT INTO contract_materials(contract_id,material_id,qty) VALUES(?,?,?)',
            (new_id, m['material_id'], m['mat_qty']))
    conn.commit()
    audit('duplicate_contract', 'contract', new_id, f'From {src["contract_no"]}')
    conn.close()
    return redirect(url_for('edit_contract', cid=new_id))

# ── Deliveries ────────────────────────────────────────────────────
@app.route('/contracts/<int:cid>/delivery', methods=['POST'])
@login_required
@role_required('admin', 'editor')
def add_delivery(cid):
    d = request.form; conn = get_db()
    contract_row = conn.execute('SELECT status FROM contracts WHERE id=?', (cid,)).fetchone()
    if not contract_row:
        conn.close(); flash('Contract not found.', 'error'); return redirect(url_for('contracts'))
    if contract_row['status'] != 'open':
        conn.close(); flash('Cannot add delivery to a closed contract.', 'error')
        return redirect(url_for('view_contract', cid=cid))
    if not d.get('delivery_date', '').strip():
        conn.close(); flash('Delivery date is required.', 'error')
        return redirect(url_for('view_contract', cid=cid))
    if not d.get('invoice_no', '').strip():
        conn.close(); flash('Invoice number is required.', 'error')
        return redirect(url_for('view_contract', cid=cid))
    try:
        qty = float(d.get('qty', 0))
        if qty <= 0: raise ValueError
    except (ValueError, TypeError):
        conn.close(); flash('Delivery quantity must be a positive number.', 'error')
        return redirect(url_for('view_contract', cid=cid))
    alloc_id = d.get('allocation_id') or None
    if alloc_id:
        alloc_id = int(alloc_id)
        if not _validate_delivery_alloc(conn, cid, alloc_id):
            conn.close(); flash('Invalid factory allocation.', 'error')
            return redirect(url_for('view_contract', cid=cid))
    wet_qty = None
    if d.get('wet_qty', '').strip():
        try: wet_qty = float(d['wet_qty'])
        except (ValueError, TypeError): wet_qty = None
    conn.execute('''INSERT INTO deliveries
        (contract_id,allocation_id,delivery_date,invoice_no,qty,wet_qty,notes,created_by)
        VALUES(?,?,?,?,?,?,?,?)''',
        (cid, alloc_id, d['delivery_date'].strip(), d['invoice_no'].strip(),
         qty, wet_qty, d.get('notes') or None, g.user['id']))
    conn.commit()
    audit('add_delivery', 'contract', cid, f'Invoice {d["invoice_no"]}')
    conn.close()
    return redirect(url_for('view_contract', cid=cid))

@app.route('/deliveries/<int:did>/delete', methods=['POST'])
@login_required
@role_required('admin', 'editor')
def delete_delivery(did):
    conn = get_db()
    row = conn.execute('SELECT contract_id FROM deliveries WHERE id=?', (did,)).fetchone()
    if not row:
        conn.close(); flash('Delivery not found.', 'error'); return redirect(url_for('contracts'))
    cid = row['contract_id']
    contract_row = conn.execute('SELECT status FROM contracts WHERE id=?', (cid,)).fetchone()
    if contract_row and contract_row['status'] != 'open':
        conn.close(); flash('Cannot delete delivery on a closed contract.', 'error')
        return redirect(url_for('view_contract', cid=cid))
    conn.execute('DELETE FROM deliveries WHERE id=?', (did,))
    conn.commit()
    audit('delete_delivery', 'delivery', did, f'contract_id={cid}')
    conn.close()
    return redirect(url_for('view_contract', cid=cid))

@app.route('/deliveries/<int:did>/edit', methods=['POST'])
@login_required
@role_required('admin', 'editor')
def edit_delivery(did):
    d = request.form; conn = get_db()
    row = conn.execute('SELECT contract_id, allocation_id FROM deliveries WHERE id=?', (did,)).fetchone()
    if not row:
        conn.close(); flash('Delivery not found.', 'error'); return redirect(url_for('contracts'))
    cid = row['contract_id']
    contract_row = conn.execute('SELECT status FROM contracts WHERE id=?', (cid,)).fetchone()
    if contract_row and contract_row['status'] != 'open':
        conn.close(); flash('Cannot edit delivery on a closed contract.', 'error')
        return redirect(url_for('view_contract', cid=cid))
    if not d.get('delivery_date', '').strip():
        conn.close(); flash('Delivery date is required.', 'error')
        return redirect(url_for('view_contract', cid=cid))
    if not d.get('invoice_no', '').strip():
        conn.close(); flash('Invoice number is required.', 'error')
        return redirect(url_for('view_contract', cid=cid))
    try:
        qty = float(d.get('qty', 0))
        if qty <= 0: raise ValueError
    except (ValueError, TypeError):
        conn.close(); flash('Delivery quantity must be a positive number.', 'error')
        return redirect(url_for('view_contract', cid=cid))
    alloc_id_form = d.get('allocation_id') or None
    if alloc_id_form:
        alloc_id = int(alloc_id_form)
        if not _validate_delivery_alloc(conn, cid, alloc_id):
            conn.close(); flash('Invalid factory allocation.', 'error')
            return redirect(url_for('view_contract', cid=cid))
    else:
        alloc_id = row['allocation_id']  # preserve existing — never silently NULL
    wet_qty = None
    if d.get('wet_qty', '').strip():
        try: wet_qty = float(d['wet_qty'])
        except (ValueError, TypeError): wet_qty = None
    conn.execute('UPDATE deliveries SET delivery_date=?,invoice_no=?,qty=?,wet_qty=?,notes=?,allocation_id=? WHERE id=?',
        (d['delivery_date'].strip(), d['invoice_no'].strip(), qty,
         wet_qty, d.get('notes') or None, alloc_id, did))
    conn.commit()
    audit('edit_delivery', 'contract', cid, f'id={did}')
    conn.close()
    return redirect(url_for('view_contract', cid=cid))

# ── Customers ─────────────────────────────────────────────────────
@app.route('/customers', methods=['GET', 'POST'])
@login_required
def customers():
    conn = get_db(); error = None
    if request.method == 'POST':
        if not can_edit():
            flash('Permission denied.', 'error'); conn.close()
            return redirect(url_for('customers'))
        d = request.form
        if d.get('action') == 'delete':
            cid = d.get('id', '').strip()
            if cid:
                count = conn.execute('SELECT COUNT(*) FROM contracts WHERE customer_id=?', (cid,)).fetchone()[0]
                if count > 0:
                    error = f'Cannot delete: this customer has {count} contract(s).'
                else:
                    conn.execute('DELETE FROM customers WHERE id=?', (cid,))
                    conn.commit(); flash('Customer deleted.', 'success')
        elif d.get('id'):
            name = d.get('name', '').strip()
            if not name: error = 'Customer name cannot be empty.'
            else:
                try:
                    conn.execute('UPDATE customers SET name=?,contact=? WHERE id=?',
                        (name, d.get('contact') or None, d['id']))
                    conn.commit(); flash('Customer updated.', 'success')
                except sqlite3.IntegrityError:
                    error = f'Customer "{name}" already exists.'
        else:
            name = d.get('name', '').strip()
            if not name: error = 'Customer name cannot be empty.'
            else:
                try:
                    conn.execute('INSERT INTO customers(name,contact) VALUES(?,?)',
                        (name, d.get('contact') or None))
                    conn.commit(); flash('Customer added.', 'success')
                except sqlite3.IntegrityError:
                    error = f'Customer "{name}" already exists.'
    rows = conn.execute('''SELECT c.*, COUNT(ct.id) as contract_count FROM customers c
        LEFT JOIN contracts ct ON ct.customer_id=c.id GROUP BY c.id ORDER BY c.name''').fetchall()
    conn.close()
    return render_template('customers.html', customers=rows, error=error)

# ── Materials ─────────────────────────────────────────────────────
@app.route('/materials', methods=['GET', 'POST'])
@login_required
def materials():
    conn = get_db()
    if request.method == 'POST':
        if not can_edit():
            flash('Permission denied.', 'error'); conn.close()
            return redirect(url_for('materials'))
        d = request.form
        if d.get('action') == 'delete':
            mid = d.get('id', '').strip()
            if mid:
                in_use = conn.execute('SELECT COUNT(*) FROM contract_materials WHERE material_id=?',
                    (mid,)).fetchone()[0]
                if in_use > 0:
                    flash(f'Cannot delete: used in {in_use} contract(s).', 'error')
                else:
                    conn.execute('DELETE FROM materials WHERE id=?', (mid,))
                    conn.commit(); flash('Material deleted.', 'success')
        elif d.get('id'):
            name = d.get('name', '').strip()
            if not name: flash('Material name cannot be empty.', 'error')
            else:
                try:
                    conn.execute('UPDATE materials SET name=?,unit=? WHERE id=?',
                        (name, d.get('unit','kg'), d['id']))
                    conn.commit(); flash('Material updated.', 'success')
                except sqlite3.IntegrityError:
                    flash(f'Material "{name}" already exists.', 'error')
        else:
            name = d.get('name', '').strip()
            if not name: flash('Material name cannot be empty.', 'error')
            else:
                try:
                    conn.execute('INSERT INTO materials(name,unit) VALUES(?,?)',
                        (name, d.get('unit','kg')))
                    conn.commit(); flash('Material added.', 'success')
                except sqlite3.IntegrityError:
                    flash(f'Material "{name}" already exists.', 'error')
    rows = conn.execute('SELECT * FROM materials ORDER BY name').fetchall()
    conn.close()
    return render_template('materials.html', materials=rows)

# ── Report ────────────────────────────────────────────────────────
@app.route('/report')
@login_required
def report():
    conn = get_db()
    factory_filter = request.args.get('factory', '')
    month_filter   = request.args.get('month', '')
    raw_rows = _build_report_rows(conn, factory_filter, month_filter)
    conn.close()
    factory_groups = {}
    for rr in raw_rows:
        fc = rr['factory_code']; month_key = rr['month_key']
        row = {**rr['contract_row'], 'factory_code_display': fc,
               'qty': rr['qty'], 'delivered': rr['delivered'], 'balance': rr['balance'],
               'wet_qty': rr['wet_qty'], 'material_names': rr['mat_names'],
               'delivery_due': rr['dd'], 'month_key': month_key,
               'price': rr.get('price')}
        fg = factory_groups.setdefault(fc, {'name': FACTORIES.get(fc, fc),
            'is_cf': fc == 'CF', 'months': {}})
        fg['months'].setdefault(month_key, []).append(row)
    for fg in factory_groups.values():
        fg['months'] = {mk: fg['months'][mk] for mk in sorted(fg['months'])}
    factory_groups = {fc: factory_groups[fc] for fc in FACTORIES if fc in factory_groups}
    return render_template('report.html', factory_groups=factory_groups, factories=FACTORIES,
        factory_filter=factory_filter, month_filter=month_filter,
        today=date.today().strftime('%d %B %Y'))

@app.route('/report/export')
@login_required
def report_export():
    conn = get_db()
    factory_filter = request.args.get('factory', '')
    month_filter   = request.args.get('month', '')
    raw_rows = _build_report_rows(conn, factory_filter, month_filter)
    conn.close()
    audit('export_csv', 'report', None, f'factory={factory_filter} month={month_filter}')
    output = io.StringIO()
    w = csv.writer(output)
    w.writerow(['Contract No','Sale Order','Customer','Factory','Broker','Materials',
                'Delivery Due','Delivery Start','Declared Qty (kg)','Delivered (kg)','Balance (kg)'])
    for rr in raw_rows:
        w.writerow([_sanitize_csv_cell(rr['contract_no']), _sanitize_csv_cell(rr['sale_order_no']),
            _sanitize_csv_cell(rr['customer_name']), _sanitize_csv_cell(rr['factory_code']),
            _sanitize_csv_cell(rr['broker']), _sanitize_csv_cell(rr['mat_names']),
            _sanitize_csv_cell(rr['dd']), _sanitize_csv_cell(rr['delivery_start']),
            rr['qty'], rr['delivered'], rr['balance']])
    response = make_response(output.getvalue())
    response.headers['Content-Type'] = 'text/csv; charset=utf-8'
    response.headers['Content-Disposition'] = (
        f'attachment; filename=kvpl_contracts_{date.today().isoformat()}.csv')
    return response

# ── API ───────────────────────────────────────────────────────────
@app.route('/api/deliveries/<int:did>', methods=['PUT'])
@login_required
def update_delivery(did):
    if not can_edit(): return jsonify({'error': 'Permission denied'}), 403
    d = request.get_json(silent=True)
    if not d: return jsonify({'error': 'Invalid JSON'}), 400
    conn = get_db()
    row = conn.execute('SELECT contract_id FROM deliveries WHERE id=?', (did,)).fetchone()
    if not row: conn.close(); return jsonify({'error': 'Not found'}), 404
    cid = row['contract_id']
    contract_row = conn.execute('SELECT status FROM contracts WHERE id=?', (cid,)).fetchone()
    if contract_row and contract_row['status'] != 'open':
        conn.close(); return jsonify({'error': 'Contract is closed'}), 400
    try:
        qty = float(d.get('qty', 0))
        if qty <= 0: raise ValueError
    except (ValueError, TypeError):
        conn.close(); return jsonify({'error': 'qty must be a positive number'}), 400
    if not d.get('delivery_date', '').strip(): conn.close(); return jsonify({'error': 'delivery_date required'}), 400
    if not d.get('invoice_no', '').strip(): conn.close(); return jsonify({'error': 'invoice_no required'}), 400
    conn.execute('UPDATE deliveries SET delivery_date=?,invoice_no=?,qty=?,wet_qty=?,notes=? WHERE id=?',
        (d['delivery_date'].strip(), d['invoice_no'].strip(), qty,
         d.get('wet_qty') or None, d.get('notes') or None, did))
    conn.commit(); conn.close()
    return jsonify({'ok': True})

@app.route('/api/dashboard/chart')
@login_required
def dashboard_chart():
    conn = get_db()
    rows = conn.execute("""
        SELECT strftime('%Y-%m', d.delivery_date) as month,
               COALESCE(ca.factory_code, c.factory_code) as factory_code,
               SUM(d.qty) as qty
        FROM deliveries d JOIN contracts c ON c.id = d.contract_id
        LEFT JOIN contract_allocations ca ON ca.id = d.allocation_id
        WHERE d.delivery_date >= date('now', '-6 months')
        GROUP BY month, COALESCE(ca.factory_code, c.factory_code) ORDER BY month""").fetchall()
    conn.close()
    data = {}
    for r in rows:
        data.setdefault(r['month'], {})[r['factory_code']] = round(r['qty'], 3)
    today = date.today(); months = []
    for i in range(5, -1, -1):
        m = today.month - i; y = today.year
        while m <= 0: m += 12; y -= 1
        months.append(f'{y:04d}-{m:02d}')
    all_months = sorted(set(months) | set(data.keys()))
    return jsonify({'months': all_months, 'data': data, 'factories': list(FACTORIES)})

@app.route('/api/contracts/search')
@login_required
def search_contracts():
    q = request.args.get('q', '').strip()
    if len(q) < 2: return jsonify([])
    conn = get_db()
    rows = conn.execute("""SELECT c.id, c.contract_no, c.factory_code, c.status,
               cu.name as customer_name, c.delivery_end as delivery_due
        FROM contracts c JOIN customers cu ON cu.id=c.customer_id
        WHERE c.contract_no LIKE ? OR cu.name LIKE ? OR c.sale_order_no LIKE ? OR c.broker LIKE ?
        LIMIT 10""", (f'%{q}%',)*4).fetchall()
    conn.close()
    return jsonify([dict(r) for r in rows])


# ── World Prices ─────────────────────────────────────────────────
@app.route('/world-prices')
@login_required
def world_prices():
    return render_template('world_prices.html')

@app.route('/api/world-prices')
@login_required
def api_world_prices():
    force = request.args.get('refresh') == '1'
    try:
        data = get_world_prices(force=force)
        latest = data[-1]
        prev = data[-2] if len(data) > 1 else None
        return jsonify({
            'ok': True,
            'latest': latest,
            'previous': prev,
            'history': data,
            'count': len(data),
        })
    except Exception as ex:
        app.logger.warning(f'World prices fetch failed: {ex}')
        return jsonify({'ok': False, 'error': str(ex)}), 502

if __name__ == '__main__':
    app.run(debug=os.environ.get('FLASK_DEBUG', '0') == '1',
            port=int(os.environ.get('PORT', 5002)))