#!/usr/bin/env python3
"""
Excel & CSV Spreadsheet Reader CLI Utility
Part of the @goobit/ai excel-reader skill.

Extracts sheets, slices ranges, searches rows, and formats data into Markdown or JSON.
Strictly adheres to the No-Emoji policy.
"""

import sys
import os
import argparse
import json
import csv
import re
from datetime import datetime, date

def format_cell_value(val, max_len=100):
    if val is None:
        return ""
    if isinstance(val, (datetime, date)):
        formatted = val.isoformat()
    elif isinstance(val, float):
        if val.is_integer():
            formatted = str(int(val))
        else:
            formatted = f"{val:.4f}".rstrip('0').rstrip('.')
    else:
        formatted = str(val).strip()
    
    # Replace newlines with spaces for single-line table cells
    formatted = re.sub(r'[\r\n]+', ' ', formatted)
    
    if len(formatted) > max_len:
        return formatted[:max_len - 3] + "..."
    return formatted

def to_markdown_table(headers, rows, max_cols=20):
    if not headers and not rows:
        return "[Empty Data]"
    
    total_cols = max(len(headers), max((len(r) for r in rows), default=0))
    truncated = False
    if total_cols > max_cols:
        total_cols = max_cols
        truncated = True
    
    padded_headers = [str(h) for h in headers[:total_cols]]
    while len(padded_headers) < total_cols:
        padded_headers.append(f"Col_{len(padded_headers)+1}")
        
    padded_rows = []
    for row in rows:
        r = [str(c) for c in row[:total_cols]]
        while len(r) < total_cols:
            r.append("")
        padded_rows.append(r)
        
    col_widths = [len(h) for h in padded_headers]
    for r in padded_rows:
        for i, val in enumerate(r):
            if i < len(col_widths):
                col_widths[i] = max(col_widths[i], len(val))
            else:
                col_widths.append(len(val))
    
    # Floor min width at 3
    col_widths = [max(w, 3) for w in col_widths]
    
    header_line = "| " + " | ".join(h.ljust(col_widths[i]) for i, h in enumerate(padded_headers)) + " |"
    sep_line = "|-" + "-|-".join("-" * col_widths[i] for i in range(len(padded_headers))) + "-|"
    
    lines = [header_line, sep_line]
    for r in padded_rows:
        row_line = "| " + " | ".join(r[i].ljust(col_widths[i]) for i in range(len(padded_headers))) + " |"
        lines.append(row_line)
        
    if truncated:
        lines.append(f"\n[Note: Truncated to {max_cols} columns out of original width]")
    return "\n".join(lines)

def read_csv_file(file_path):
    rows = []
    try:
        with open(file_path, "r", encoding="utf-8-sig") as f:
            reader = csv.reader(f)
            for row in reader:
                rows.append(row)
    except UnicodeDecodeError:
        with open(file_path, "r", encoding="cp949") as f:
            reader = csv.reader(f)
            for row in reader:
                rows.append(row)
    return {"Sheet1": rows}

def read_excel_file(file_path, data_only=True):
    try:
        import openpyxl
    except ImportError:
        try:
            import pandas as pd
            excel_file = pd.ExcelFile(file_path)
            sheets_data = {}
            for sheet_name in excel_file.sheet_names:
                df = pd.read_excel(file_path, sheet_name=sheet_name, header=None)
                # Convert NaNs to None
                sheets_data[sheet_name] = df.where(pd.notnull(df), None).values.tolist()
            return sheets_data
        except ImportError:
            sys.stderr.write("[ERROR] Neither 'openpyxl' nor 'pandas' is installed.\n")
            sys.stderr.write("Please install openpyxl via: pip install openpyxl\n")
            sys.exit(1)
            
    wb = openpyxl.load_workbook(file_path, read_only=True, data_only=data_only)
    sheets_data = {}
    for sheet_name in wb.sheetnames:
        ws = wb[sheet_name]
        rows = []
        for row in ws.iter_rows(values_only=True):
            rows.append(list(row))
        sheets_data[sheet_name] = rows
    wb.close()
    return sheets_data

def parse_range(range_str):
    match = re.match(r"^([A-Za-z]+)(\d+):([A-Za-z]+)(\d+)$", range_str.strip())
    if not match:
        raise ValueError(f"Invalid range format: '{range_str}'. Expected format like 'A1:D20'.")
    col1_str, row1_str, col2_str, row2_str = match.groups()
    
    def col2num(col):
        num = 0
        for char in col.upper():
            num = num * 26 + (ord(char) - ord('A') + 1)
        return num - 1
    
    return {
        "start_row": int(row1_str) - 1,
        "end_row": int(row2_str),
        "start_col": col2num(col1_str),
        "end_col": col2num(col2_str) + 1
    }

def main():
    parser = argparse.ArgumentParser(
        description="Extract and inspect data from Excel (.xlsx, .xlsm) and CSV spreadsheets.",
        formatter_class=argparse.RawDescriptionHelpFormatter
    )
    parser.add_argument("file", help="Path to the spreadsheet file (.xlsx, .xlsm, .csv, .tsv)")
    parser.add_argument("--sheets", action="store_true", help="List all sheets with row/column counts")
    parser.add_argument("--sheet", default=None, help="Sheet name or 0-based index (default: first sheet)")
    parser.add_argument("--head", type=int, default=None, help="Preview the first N rows")
    parser.add_argument("--tail", type=int, default=None, help="Preview the last N rows")
    parser.add_argument("--range", default=None, help="Extract specific cell range (e.g., A1:F30)")
    parser.add_argument("--search", default=None, help="Case-insensitive text search across rows")
    parser.add_argument("--summary", action="store_true", help="Display column headers, data types, and non-empty counts")
    parser.add_argument("--format", choices=["md", "json", "csv"], default="md", help="Output format (default: md)")
    parser.add_argument("--max-cols", type=int, default=20, help="Maximum columns to display in table (default: 20)")
    parser.add_argument("--max-cell-len", type=int, default=100, help="Maximum characters per cell (default: 100)")
    parser.add_argument("--all-rows", action="store_true", help="Output all rows without default head limit")
    parser.add_argument("--raw-formulas", action="store_true", help="Show formulas instead of calculated values")
    
    args = parser.parse_args()
    
    if not os.path.exists(args.file):
        sys.stderr.write(f"[ERROR] File not found: {args.file}\n")
        sys.exit(1)
        
    ext = os.path.splitext(args.file)[1].lower()
    
    if ext in [".csv", ".tsv"]:
        sheets_data = read_csv_file(args.file)
    elif ext in [".xlsx", ".xlsm", ".xltx", ".xltm"]:
        sheets_data = read_excel_file(args.file, data_only=not args.raw_formulas)
    else:
        # Try excel first, then csv
        try:
            sheets_data = read_excel_file(args.file, data_only=not args.raw_formulas)
        except Exception:
            sheets_data = read_csv_file(args.file)
            
    sheet_names = list(sheets_data.keys())
    if not sheet_names:
        sys.stderr.write("[ERROR] Workbook contains no sheets or readable data.\n")
        sys.exit(1)
        
    # Handle --sheets listing
    if args.sheets:
        info = []
        for name in sheet_names:
            rows = sheets_data[name]
            row_count = len(rows)
            col_count = max((len(r) for r in rows), default=0)
            info.append({"sheet": name, "rows": row_count, "columns": col_count})
        if args.format == "json":
            print(json.dumps(info, ensure_ascii=False, indent=2))
        else:
            headers = ["Sheet Name", "Total Rows", "Total Columns"]
            table_rows = [[item["sheet"], str(item["rows"]), str(item["columns"])] for item in info]
            print(to_markdown_table(headers, table_rows, max_cols=args.max_cols))
        return

    # Select target sheet
    selected_sheet = sheet_names[0]
    if args.sheet is not None:
        if args.sheet in sheets_data:
            selected_sheet = args.sheet
        else:
            try:
                idx = int(args.sheet)
                if 0 <= idx < len(sheet_names):
                    selected_sheet = sheet_names[idx]
                else:
                    sys.stderr.write(f"[ERROR] Sheet index {idx} out of range (0-{len(sheet_names)-1}).\n")
                    sys.exit(1)
            except ValueError:
                sys.stderr.write(f"[ERROR] Sheet '{args.sheet}' not found in workbook: {sheet_names}\n")
                sys.exit(1)

    raw_rows = sheets_data[selected_sheet]
    
    # Strip trailing completely empty rows
    while raw_rows and all(c is None or str(c).strip() == "" for c in raw_rows[-1]):
        raw_rows.pop()

    if not raw_rows:
        print(f"[INFO] Sheet '{selected_sheet}' is empty.")
        return

    # Handle --summary
    if args.summary:
        headers = raw_rows[0] if raw_rows else []
        data_rows = raw_rows[1:] if len(raw_rows) > 1 else []
        col_count = max(len(headers), max((len(r) for r in data_rows), default=0))
        
        col_summaries = []
        for c_idx in range(col_count):
            header_name = format_cell_value(headers[c_idx] if c_idx < len(headers) else None, args.max_cell_len)
            if not header_name:
                header_name = f"Column_{c_idx + 1}"
            
            non_null_count = 0
            sample_values = []
            types = set()
            
            for r in data_rows:
                val = r[c_idx] if c_idx < len(r) else None
                if val is not None and str(val).strip() != "":
                    non_null_count += 1
                    types.add(type(val).__name__)
                    if len(sample_values) < 3:
                        sample_values.append(format_cell_value(val, 30))
                        
            type_str = "/".join(sorted(types)) if types else "empty"
            sample_str = ", ".join(sample_values)
            col_summaries.append({
                "col_index": c_idx + 1,
                "header": header_name,
                "type": type_str,
                "non_null": non_null_count,
                "total": len(data_rows),
                "samples": sample_str
            })
            
        if args.format == "json":
            print(json.dumps({
                "sheet": selected_sheet,
                "total_rows": len(raw_rows),
                "columns": col_summaries
            }, ensure_ascii=False, indent=2))
        else:
            print(f"### Sheet Summary: {selected_sheet} (Rows: {len(raw_rows)}, Cols: {col_count})\n")
            headers = ["#", "Column Name", "Inferred Type", "Non-Empty", "Samples"]
            rows = [[str(s["col_index"]), s["header"], s["type"], f"{s['non_null']}/{s['total']}", s["samples"]] for s in col_summaries]
            print(to_markdown_table(headers, rows, max_cols=args.max_cols))
        return

    # Handle --range
    if args.range:
        bounds = parse_range(args.range)
        sliced_rows = []
        for r_idx in range(bounds["start_row"], min(bounds["end_row"], len(raw_rows))):
            row = raw_rows[r_idx]
            sliced_row = row[bounds["start_col"]:bounds["end_col"]]
            sliced_rows.append([format_cell_value(c, args.max_cell_len) for c in sliced_row])
            
        if args.format == "json":
            print(json.dumps({"range": args.range, "data": sliced_rows}, ensure_ascii=False, indent=2))
        elif args.format == "csv":
            writer = csv.writer(sys.stdout)
            for r in sliced_rows:
                writer.writerow(r)
        else:
            headers = sliced_rows[0] if sliced_rows else []
            body = sliced_rows[1:] if len(sliced_rows) > 1 else []
            print(f"### Range: {args.range} in '{selected_sheet}'\n")
            print(to_markdown_table(headers, body, max_cols=args.max_cols))
        return

    # Handle --search
    if args.search:
        query = args.search.lower()
        matched_rows = []
        header = [format_cell_value(c, args.max_cell_len) for c in raw_rows[0]]
        for idx, row in enumerate(raw_rows[1:], start=2):
            row_formatted = [format_cell_value(c, args.max_cell_len) for c in row]
            if any(query in val.lower() for val in row_formatted):
                matched_rows.append([f"Row {idx}"] + row_formatted)
                
        if args.format == "json":
            print(json.dumps({"query": args.search, "matches": matched_rows}, ensure_ascii=False, indent=2))
        elif args.format == "csv":
            writer = csv.writer(sys.stdout)
            writer.writerow(["Row_Number"] + header)
            for r in matched_rows:
                writer.writerow(r)
        else:
            print(f"### Search Results for '{args.search}' in '{selected_sheet}' (Matches: {len(matched_rows)})\n")
            if matched_rows:
                print(to_markdown_table(["Row #"] + header, matched_rows, max_cols=args.max_cols))
            else:
                print("[INFO] No matching rows found.")
        return

    # Normal read / slice
    header_row = [format_cell_value(c, args.max_cell_len) for c in raw_rows[0]]
    data_rows = [[format_cell_value(c, args.max_cell_len) for c in r] for r in raw_rows[1:]]

    limit = None
    if args.head is not None:
        limit = args.head
        data_rows = data_rows[:limit]
    elif args.tail is not None:
        limit = args.tail
        data_rows = data_rows[-limit:]
    elif not args.all_rows:
        # Default head limit to 15 rows to prevent token exhaustion
        if len(data_rows) > 15:
            limit = 15
            data_rows = data_rows[:15]

    if args.format == "json":
        result = {
            "sheet": selected_sheet,
            "total_rows": len(raw_rows),
            "displayed_rows": len(data_rows),
            "headers": header_row,
            "rows": data_rows
        }
        print(json.dumps(result, ensure_ascii=False, indent=2))
    elif args.format == "csv":
        writer = csv.writer(sys.stdout)
        writer.writerow(header_row)
        for r in data_rows:
            writer.writerow(r)
    else:
        print(f"### Sheet: {selected_sheet} (Displaying {len(data_rows)} of {len(raw_rows)-1} data rows)\n")
        print(to_markdown_table(header_row, data_rows, max_cols=args.max_cols))
        if limit and len(raw_rows) - 1 > limit:
            print(f"\n[Note: Showing first {limit} rows. Use '--all-rows', '--head N', or '--range' to see more]")

if __name__ == "__main__":
    main()