#!/usr/bin/env python3
"""Create an Ops Intake table in Airtable NMC-DB if it does not exist."""
import json
import os
import sys
import urllib.request
import urllib.error

BASE_ID = "applisWTLgZJOnYtj"
API_KEY_PATH = os.path.expanduser("~/.openclaw/secrets/airtable-api-key.txt")
META_BASE_URL = f"https://api.airtable.com/v0/meta/bases/{BASE_ID}/tables"
API_BASE_URL = f"https://api.airtable.com/v0/{BASE_ID}"
TABLE_NAME = "Ops Intake"


def api_key():
    return open(API_KEY_PATH).read().strip()


def request(url, method="GET", data=None):
    headers = {
        "Authorization": f"Bearer {api_key()}",
        "Content-Type": "application/json",
    }
    body = None if data is None else json.dumps(data).encode()
    req = urllib.request.Request(url, data=body, headers=headers, method=method)
    with urllib.request.urlopen(req, timeout=30) as resp:
        return json.load(resp)


def get_tables():
    return request(META_BASE_URL).get("tables", [])


def field(name, type_, options=None, description=None):
    out = {"name": name, "type": type_}
    if options is not None:
        out["options"] = options
    if description:
        out["description"] = description
    return out


def single_select(name, choices, description=None):
    return field(
        name,
        "singleSelect",
        {"choices": [{"name": c} for c in choices]},
        description,
    )


def linked(name, table_id, description=None):
    return field(
        name,
        "multipleRecordLinks",
        {"linkedTableId": table_id, "prefersSingleRecordLink": True},
        description,
    )


def main():
    tables = get_tables()
    by_name = {t["name"]: t for t in tables}
    if TABLE_NAME in by_name:
        print(f"Table already exists: {TABLE_NAME} ({by_name[TABLE_NAME]['id']})")
        return 0

    required = ["Customers", "Bookings", "Contractors", "Leads", "Complaints"]
    missing = [name for name in required if name not in by_name]
    if missing:
        print(f"Missing required tables: {', '.join(missing)}", file=sys.stderr)
        return 1

    payload = {
        "name": TABLE_NAME,
        "description": "Central intake queue for leads, customer requests, cleaner issues, scheduling exceptions, and internal ops follow-ups.",
        "fields": [
            field("Title", "singleLineText", description="Short label for the item."),
            single_select("Type", ["Lead", "Customer", "Cleaner", "Scheduling", "Internal"], "What kind of work item this is."),
            single_select("Source", ["Website", "Text", "Call", "Slack", "Email", "Manual"], "Where the request came from."),
            single_select("Status", ["New", "Triage", "In Progress", "Waiting", "Done"], "Current stage in the queue."),
            single_select("Priority", ["Low", "Medium", "High", "Urgent"], "How quickly this needs attention."),
            field("Owner", "singleLineText", description="Who owns the next action."),
            field("Next Action", "multilineText", description="Clear next step."),
            field("Due Date", "date", description="When this should be handled by."),
            linked("Customer", by_name["Customers"]["id"], "Linked customer record, if relevant."),
            linked("Booking", by_name["Bookings"]["id"], "Linked booking record, if relevant."),
            linked("Contractor", by_name["Contractors"]["id"], "Linked cleaner / contractor record, if relevant."),
            linked("Lead", by_name["Leads"]["id"], "Linked lead record, if relevant."),
            linked("Complaint", by_name["Complaints"]["id"], "Linked complaint record, if relevant."),
            field("Contact Info", "singleLineText", description="Phone or email when not already linked."),
            field("Notes", "multilineText", description="Conversation snippets or working notes."),
            field("AI Managed", "checkbox", {"color": "greenBright", "icon": "robot"}, "Checked when AI is expected to help manage the item."),
            field("Created Time", "createdTime"),
            field("Last Modified", "lastModifiedTime"),
        ],
    }

    created = request(META_BASE_URL, method="POST", data=payload)
    print(f"Created table: {created['name']} ({created['id']})")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())