#!/usr/bin/env python3
"""
SwiftShip Express — database seeder.

Creates swiftship.db (shipments, tracking_events, quotes, contact_messages),
seeds 12 demo shipments with full tracking histories, then exports:
  * site/data/tracking-fallback.json     -> static fallback for the Track page
  * downloads/sample-shipments-export.csv -> sample data export

Usage:  python3 seed.py
"""
import csv
import json
import os
import sqlite3

BASE = os.path.dirname(os.path.abspath(__file__))
DB = os.path.join(BASE, "swiftship.db")
DATA_DIR = os.path.join(BASE, "site", "data")
DL_DIR = os.path.join(BASE, "downloads")

SCHEMA = """
DROP TABLE IF EXISTS shipments;
DROP TABLE IF EXISTS tracking_events;
DROP TABLE IF EXISTS quotes;
DROP TABLE IF EXISTS contact_messages;

CREATE TABLE shipments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tracking_number  TEXT UNIQUE NOT NULL,
    sender_name      TEXT, sender_city TEXT, sender_country TEXT,
    recipient_name   TEXT, recipient_city TEXT, recipient_country TEXT,
    service_type     TEXT,
    weight_kg        REAL,
    pieces           INTEGER,
    status           TEXT,
    current_location TEXT,
    estimated_delivery TEXT,
    created_at       TEXT
);

CREATE TABLE tracking_events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    shipment_id INTEGER NOT NULL REFERENCES shipments(id),
    event_time  TEXT NOT NULL,
    status      TEXT NOT NULL,
    location    TEXT,
    description TEXT
);

CREATE TABLE quotes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ref_number TEXT UNIQUE,
    name TEXT, email TEXT, phone TEXT,
    origin TEXT, destination TEXT,
    weight_kg REAL, service_type TEXT,
    notes TEXT, created_at TEXT
);

CREATE TABLE contact_messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticket TEXT UNIQUE,
    name TEXT, email TEXT,
    subject TEXT, message TEXT, created_at TEXT
);
"""

# --------------------------------------------------------------------------
# Demo data — events are (event_time, status, location, description)
# --------------------------------------------------------------------------
SHIPMENTS = [
    dict(
        tracking_number="SSX884120565",
        sender_name="Atelier Blanc Ltd", sender_city="London", sender_country="United Kingdom",
        recipient_name="M. Rodriguez", recipient_city="New York, NY", recipient_country="United States",
        service_type="Express International", weight_kg=2.4, pieces=1,
        status="Delivered", current_location="New York, NY — delivered",
        estimated_delivery="2026-09-24",
        events=[
            ("2026-09-19T09:12:00Z", "Label Created", "London, United Kingdom", "Shipping label created by shipper"),
            ("2026-09-19T15:47:00Z", "Picked Up", "London, United Kingdom", "Parcel collected by SwiftShip courier"),
            ("2026-09-20T06:05:00Z", "In Transit", "London Heathrow (LHR), United Kingdom", "Departed origin — export scan"),
            ("2026-09-21T19:30:00Z", "Customs Clearance", "Newark, NJ, United States", "Import clearance completed"),
            ("2026-09-23T08:14:00Z", "Out for Delivery", "New York, NY, United States", "On vehicle for final delivery"),
            ("2026-09-24T10:41:00Z", "Delivered", "New York, NY, United States", "Delivered — signed by M. RODRIGUEZ"),
        ],
    ),
    dict(
        tracking_number="SSX215478930",
        sender_name="Shenzhen OptoTech Co.", sender_city="Shenzhen", sender_country="China",
        recipient_name="Van der Berg Logistics", recipient_city="Rotterdam", recipient_country="Netherlands",
        service_type="Ocean FCL (40ft)", weight_kg=18400, pieces=1,
        status="In Transit", current_location="Suez Canal, Egypt",
        estimated_delivery="2026-10-11",
        events=[
            ("2026-09-24T08:00:00Z", "Label Created", "Shenzhen, China", "Booking confirmed — container SXXU-4471203"),
            ("2026-09-26T11:20:00Z", "Picked Up", "Yantian Port, Shenzhen, China", "Container loaded on vessel SWIFT ECHO V.026E"),
            ("2026-09-27T22:05:00Z", "In Transit", "South China Sea", "Vessel departed Yantian"),
            ("2026-10-01T04:30:00Z", "In Transit", "Port of Singapore", "Transshipment complete"),
            ("2026-10-02T07:55:00Z", "In Transit", "Suez Canal, Egypt", "Vessel in transit — convoy northbound"),
        ],
    ),
    dict(
        tracking_number="SSX663209847",
        sender_name="Great Lakes Medical", sender_city="Chicago, IL", sender_country="United States",
        recipient_name="Northline Clinic", recipient_city="Toronto, ON", recipient_country="Canada",
        service_type="Express International", weight_kg=8.6, pieces=3,
        status="Out for Delivery", current_location="Toronto, ON, Canada",
        estimated_delivery="2026-10-02",
        events=[
            ("2026-09-29T10:03:00Z", "Picked Up", "Chicago, IL, United States", "Parcel collected from shipper"),
            ("2026-09-29T17:45:00Z", "In Transit", "Chicago O'Hare (ORD), United States", "Departed origin hub"),
            ("2026-10-01T09:10:00Z", "Customs Clearance", "Toronto Pearson (YYZ), Canada", "Import clearance completed"),
            ("2026-10-02T08:22:00Z", "Out for Delivery", "Toronto, ON, Canada", "On vehicle for final delivery — courier J. Tan"),
        ],
    ),
    dict(
        tracking_number="SSX109283746",
        sender_name="Pharma India Ltd", sender_city="Mumbai", sender_country="India",
        recipient_name="Gulf Med Supplies", recipient_city="Dubai", recipient_country="United Arab Emirates",
        service_type="Express International", weight_kg=12.2, pieces=2,
        status="Customs Clearance", current_location="Dubai (DXB), United Arab Emirates",
        estimated_delivery="2026-10-03",
        events=[
            ("2026-09-29T12:40:00Z", "Picked Up", "Mumbai, India", "Parcel collected from shipper"),
            ("2026-09-30T03:15:00Z", "In Transit", "Mumbai (BOM), India", "Departed origin — export scan"),
            ("2026-10-01T13:05:00Z", "In Transit", "Dubai World Central, UAE", "Arrived destination hub"),
            ("2026-10-01T16:48:00Z", "Customs Clearance", "Dubai (DXB), United Arab Emirates", "Documents submitted to customs — clearance in progress"),
        ],
    ),
    dict(
        tracking_number="SSX503829164",
        sender_name="Cafe Paulista", sender_city="Sao Paulo", sender_country="Brazil",
        recipient_name="Lisbon Roastery", recipient_city="Lisbon", recipient_country="Portugal",
        service_type="International Economy Air", weight_kg=3.1, pieces=1,
        status="Exception — Delayed", current_location="Lisbon, Portugal",
        estimated_delivery="2026-10-05",
        events=[
            ("2026-09-26T14:30:00Z", "Picked Up", "Sao Paulo, Brazil", "Parcel collected from shipper"),
            ("2026-09-27T05:50:00Z", "In Transit", "Sao Paulo Guarulhos (GRU), Brazil", "Departed origin — export scan"),
            ("2026-09-30T18:12:00Z", "In Transit", "Lisbon Humberto Delgado (LIS), Portugal", "Arrived destination country"),
            ("2026-10-01T07:35:00Z", "Exception — Delayed", "Lisbon, Portugal", "Weather disruption at Lisbon hub — revised delivery 5 Oct"),
        ],
    ),
    dict(
        tracking_number="SSX748291035",
        sender_name="Berlin Klarheit GmbH", sender_city="Berlin", sender_country="Germany",
        recipient_name="Harbour Design Studio", recipient_city="Sydney", recipient_country="Australia",
        service_type="Express International", weight_kg=5.8, pieces=1,
        status="Label Created", current_location="Berlin, Germany",
        estimated_delivery="2026-10-09",
        events=[
            ("2026-10-02T06:20:00Z", "Label Created", "Berlin, Germany", "Shipping label created — awaiting first scan"),
        ],
    ),
    dict(
        tracking_number="SSX901274568",
        sender_name="Pacific Frame Co.", sender_city="Los Angeles, CA", sender_country="United States",
        recipient_name="TexArts Gallery", recipient_city="Austin, TX", recipient_country="United States",
        service_type="Express Domestic", weight_kg=1.2, pieces=1,
        status="Delivered", current_location="Austin, TX — delivered",
        estimated_delivery="2026-09-30",
        events=[
            ("2026-09-28T09:05:00Z", "Picked Up", "Los Angeles, CA, United States", "Parcel collected from shipper"),
            ("2026-09-28T19:40:00Z", "In Transit", "Los Angeles (LAX), United States", "Departed origin hub"),
            ("2026-09-29T14:25:00Z", "In Transit", "Dallas Fort Worth (DFW), United States", "Arrived regional hub"),
            ("2026-09-30T09:30:00Z", "Out for Delivery", "Austin, TX, United States", "On vehicle for final delivery"),
            ("2026-09-30T11:58:00Z", "Delivered", "Austin, TX, United States", "Delivered — left at reception"),
        ],
    ),
    dict(
        tracking_number="SSX327609514",
        sender_name="Lion City Sensors", sender_city="Singapore", sender_country="Singapore",
        recipient_name="Osaka Precision KK", recipient_city="Tokyo", recipient_country="Japan",
        service_type="Express International", weight_kg=15.7, pieces=4,
        status="In Transit", current_location="Tokyo (NRT), Japan",
        estimated_delivery="2026-10-04",
        events=[
            ("2026-09-30T10:15:00Z", "Picked Up", "Singapore", "Parcel collected from shipper"),
            ("2026-10-01T01:22:00Z", "In Transit", "Singapore Changi (SIN)", "Departed origin hub"),
            ("2026-10-01T19:47:00Z", "In Transit", "Tokyo Narita (NRT), Japan", "Arrived destination hub"),
            ("2026-10-02T05:36:00Z", "In Transit", "Tokyo (NRT), Japan", "Import clearance completed — outbound sort"),
        ],
    ),
    dict(
        tracking_number="SSX554810293",
        sender_name="Maison Lumiere", sender_city="Paris", sender_country="France",
        recipient_name="Ines Cortes", recipient_city="Madrid", recipient_country="Spain",
        service_type="International Economy Air", weight_kg=0.8, pieces=1,
        status="Delivered", current_location="Madrid, Spain — delivered",
        estimated_delivery="2026-09-28",
        events=[
            ("2026-09-25T11:12:00Z", "Picked Up", "Paris, France", "Parcel collected from shipper"),
            ("2026-09-26T07:38:00Z", "In Transit", "Paris Charles de Gaulle (CDG), France", "Departed origin hub"),
            ("2026-09-27T16:05:00Z", "In Transit", "Madrid Barajas (MAD), Spain", "Arrived destination hub"),
            ("2026-09-28T10:19:00Z", "Out for Delivery", "Madrid, Spain", "On vehicle for final delivery"),
            ("2026-09-28T12:44:00Z", "Delivered", "Madrid, Spain", "Delivered — handed to resident"),
        ],
    ),
    dict(
        tracking_number="SSX615492870",
        sender_name="Cape Estates Winery", sender_city="Cape Town", sender_country="South Africa",
        recipient_name="Albion Imports", recipient_city="London", recipient_country="United Kingdom",
        service_type="International Economy Air", weight_kg=22.5, pieces=2,
        status="Picked Up", current_location="Cape Town, South Africa",
        estimated_delivery="2026-10-07",
        events=[
            ("2026-10-01T13:45:00Z", "Label Created", "Cape Town, South Africa", "Shipping label created by shipper"),
            ("2026-10-01T16:28:00Z", "Picked Up", "Cape Town, South Africa", "Parcel collected by SwiftShip courier"),
        ],
    ),
    dict(
        tracking_number="SSX235891476",
        sender_name="Brooklyn Bean Co.", sender_city="New York, NY", sender_country="United States",
        recipient_name="Palermo Gastronomia", recipient_city="Buenos Aires", recipient_country="Argentina",
        service_type="Express International", weight_kg=9.9, pieces=2,
        status="In Transit", current_location="Buenos Aires (EZE), Argentina",
        estimated_delivery="2026-10-03",
        events=[
            ("2026-09-28T08:55:00Z", "Picked Up", "New York, NY, United States", "Parcel collected from shipper"),
            ("2026-09-28T21:30:00Z", "In Transit", "New York JFK, United States", "Departed origin hub"),
            ("2026-10-01T12:14:00Z", "In Transit", "Buenos Aires Ezeiza (EZE), Argentina", "Arrived destination hub"),
            ("2026-10-02T06:40:00Z", "In Transit", "Buenos Aires (EZE), Argentina", "Customs cleared — awaiting outbound scan"),
        ],
    ),
    dict(
        tracking_number="SSX883010257",
        sender_name="Gulf Dates Trading", sender_city="Dubai", sender_country="United Arab Emirates",
        recipient_name="Nairobi Fresh Ltd", recipient_city="Nairobi", recipient_country="Kenya",
        service_type="Express International", weight_kg=4.4, pieces=1,
        status="Delivered", current_location="Nairobi, Kenya — delivered",
        estimated_delivery="2026-10-01",
        events=[
            ("2026-09-27T09:18:00Z", "Picked Up", "Dubai, United Arab Emirates", "Parcel collected from shipper"),
            ("2026-09-28T02:10:00Z", "In Transit", "Dubai (DXB), United Arab Emirates", "Departed origin hub"),
            ("2026-09-30T15:33:00Z", "Customs Clearance", "Nairobi Jomo Kenyatta (NBO), Kenya", "Import clearance completed"),
            ("2026-10-01T10:26:00Z", "Delivered", "Nairobi, Kenya", "Delivered — signed by J. MWANGI"),
        ],
    ),
]


def main():
    os.makedirs(DATA_DIR, exist_ok=True)
    os.makedirs(DL_DIR, exist_ok=True)

    conn = sqlite3.connect(DB)
    conn.executescript(SCHEMA)
    cur = conn.cursor()

    for s in SHIPMENTS:
        cur.execute(
            """INSERT INTO shipments
               (tracking_number, sender_name, sender_city, sender_country,
                recipient_name, recipient_city, recipient_country,
                service_type, weight_kg, pieces, status, current_location,
                estimated_delivery, created_at)
               VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)""",
            (s["tracking_number"], s["sender_name"], s["sender_city"], s["sender_country"],
             s["recipient_name"], s["recipient_city"], s["recipient_country"],
             s["service_type"], s["weight_kg"], s["pieces"], s["status"],
             s["current_location"], s["estimated_delivery"], s["events"][0][0]),
        )
        sid = cur.lastrowid
        cur.executemany(
            """INSERT INTO tracking_events (shipment_id, event_time, status, location, description)
               VALUES (?,?,?,?,?)""",
            [(sid, *e) for e in s["events"]],
        )
    conn.commit()

    # ---- export static fallback JSON (Track page works without the server) ----
    fallback = {
        "generated_at": "2026-10-02T00:00:00Z",
        "note": "Static snapshot of the SwiftShip Express tracking database. "
                "Used automatically by the Track page when the API server is not running.",
        "shipments": [
            {
                "tracking_number": s["tracking_number"],
                "sender_name": s["sender_name"], "sender_city": s["sender_city"],
                "sender_country": s["sender_country"],
                "recipient_name": s["recipient_name"], "recipient_city": s["recipient_city"],
                "recipient_country": s["recipient_country"],
                "service_type": s["service_type"], "weight_kg": s["weight_kg"],
                "pieces": s["pieces"], "status": s["status"],
                "current_location": s["current_location"],
                "estimated_delivery": s["estimated_delivery"],
                "events": [{"event_time": t, "status": st, "location": loc, "description": d}
                           for (t, st, loc, d) in s["events"]],
            }
            for s in SHIPMENTS
        ],
    }
    with open(os.path.join(DATA_DIR, "tracking-fallback.json"), "w", encoding="utf-8") as f:
        json.dump(fallback, f, indent=2, ensure_ascii=False)

    # ---- export sample CSV ----
    csv_path = os.path.join(DL_DIR, "sample-shipments-export.csv")
    with open(csv_path, "w", newline="", encoding="utf-8") as f:
        w = csv.writer(f)
        w.writerow(["tracking_number", "sender_city", "sender_country",
                    "recipient_city", "recipient_country", "service_type",
                    "weight_kg", "status", "current_location", "estimated_delivery"])
        for s in SHIPMENTS:
            w.writerow([s["tracking_number"], s["sender_city"], s["sender_country"],
                        s["recipient_city"], s["recipient_country"], s["service_type"],
                        s["weight_kg"], s["status"], s["current_location"],
                        s["estimated_delivery"]])

    n_ev = cur.execute("SELECT COUNT(*) FROM tracking_events").fetchone()[0]
    conn.close()
    print(f"Seeded {len(SHIPMENTS)} shipments / {n_ev} events -> {DB}")
    print(f"Wrote {os.path.relpath(os.path.join(DATA_DIR, 'tracking-fallback.json'), BASE)}")
    print(f"Wrote {os.path.relpath(csv_path, BASE)}")


if __name__ == "__main__":
    main()