import boto3
import json
import os
import openpyxl
from botocore.exceptions import ClientError

CONFIRM = False  # Set to True to apply changes
ENV = os.environ.get("SFTP_ENV", "").strip()  # e.g. export SFTP_ENV=prod
EXCEL = "sftp_audit_prod_20260410_071734.xlsx"

# Fixed column indices from audit_sftp_host_key_algos.py output
# Store ID(0) | Order type(1) | Connection type(2) | Domain name(3) | Port(4) | Private key file(5) | Algos offered(6) | Recommended algo(7) | Status(8)
COL_STORE_ID    = 0
COL_ORDER_TYPE  = 1
COL_RECOMMENDED = 7

def main():
    if not ENV:
        raise SystemExit("ERROR: set SFTP_ENV (e.g. export SFTP_ENV=prod) before running.")

    region = os.environ.get("AWS_REGION") or os.environ.get("AWS_DEFAULT_REGION")
    client = boto3.client("secretsmanager", region_name=region) if region else boto3.client("secretsmanager")

    wb = openpyxl.load_workbook(EXCEL)
    ws = wb.active

    if not CONFIRM:
        print("DRY-RUN — set CONFIRM = True to apply changes\n")

    for row in ws.iter_rows(min_row=2, values_only=True):
        raw_store_id = row[COL_STORE_ID]
        if raw_store_id is None:
            continue
        # Normalize float IDs (e.g. 1.0 -> "1") produced by Excel numeric cells
        if isinstance(raw_store_id, float):
            raw_store_id = int(raw_store_id)
        store_id = str(raw_store_id).strip()

        order_type = str(row[COL_ORDER_TYPE]).strip() if row[COL_ORDER_TYPE] is not None else ""
        recommended = str(row[COL_RECOMMENDED]).strip() if row[COL_RECOMMENDED] is not None else ""
        if not store_id or not order_type or not recommended:
            continue

        secret_name = f"{ENV}/direct_delivery/connection_info/{store_id}/{order_type}"
        print(f"[{store_id}/{order_type}] {secret_name}")

        try:
            response = client.get_secret_value(SecretId=secret_name)
            parsed = json.loads(response["SecretString"])
            current = parsed.get("host_key_algorithm", "(not set)")

            if not CONFIRM:
                print(f"  DRY-RUN — would set host_key_algorithm: '{current}' -> '{recommended}'")
            elif current == recommended:
                print(f"  SKIPPED — host_key_algorithm already set to '{recommended}'")
            else:
                parsed["host_key_algorithm"] = recommended
                client.put_secret_value(SecretId=secret_name, SecretString=json.dumps(parsed))
                print(f"  UPDATED — '{current}' -> '{recommended}'")

        except ClientError as e:
            print(f"  ERROR — {e.response['Error']['Code']}: {e}")


if __name__ == "__main__":
    main()