# server.py
import os
import json
import decimal
import pyodbc
from flask import Flask, request, jsonify
from flask_cors import CORS

# Optionally load .env if present
try:
    from dotenv import load_dotenv
    load_dotenv()  # loads variables from a .env file into environment
except Exception:
    # dotenv is optional; env vars may already be set by the environment
    pass

# Read DB config from environment
ODBC_DRIVER = os.getenv('ODBC_DRIVER', 'ODBC Driver 17 for SQL Server')
SQL_SERVER  = os.getenv('SQL_SERVER')         # e.g. "localhost" or "myserver.database.windows.net"
SQL_PORT    = os.getenv('SQL_PORT', '1433')   # optional; default 1433
SQL_DATABASE= os.getenv('SQL_DATABASE', 'VITU')
SQL_USERNAME= os.getenv('SQL_USERNAME')       # DB user (if using SQL Auth)
SQL_PASSWORD= os.getenv('SQL_PASSWORD')       # DB password (if using SQL Auth)
# Optional: if you prefer Windows Integrated Auth, set TRUSTED_CONNECTION=yes and omit UID/PWD

if not SQL_SERVER:
    raise SystemExit("ERROR: SQL_SERVER env var not set. Please set in your environment or .env file.")

# Build connection string for pyodbc
def build_conn_str():
    # If SQL_USERNAME is not set, try trusted connection (Windows auth)
    if SQL_USERNAME and SQL_PASSWORD:
        conn_str = (
            f"DRIVER={{{ODBC_DRIVER}}};"
            f"SERVER={SQL_SERVER},{SQL_PORT};"
            f"DATABASE={SQL_DATABASE};"
            f"UID={SQL_USERNAME};PWD={SQL_PASSWORD};"
        )
    else:
        # Attempt trusted connection
        conn_str = (
            f"DRIVER={{{ODBC_DRIVER}}};"
            f"SERVER={SQL_SERVER},{SQL_PORT};"
            f"DATABASE={SQL_DATABASE};"
            f"Trusted_Connection=yes;"
        )
    return conn_str

def get_conn():
    conn_str = build_conn_str()
    return pyodbc.connect(conn_str, autocommit=True)

# Flask app
app = Flask(__name__)
CORS(app, supports_credentials=True)

def init_db():
    """Create states table if not exists (SQL Server dialect)."""
    create_states_sql = """
    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[states]') AND type in (N'U'))
    BEGIN
        CREATE TABLE dbo.states (
            id INT IDENTITY(1,1) PRIMARY KEY,
            state_name NVARCHAR(100) NOT NULL UNIQUE,
            client_name NVARCHAR(100) DEFAULT 'Unknown',
            created_at DATETIME DEFAULT GETDATE()
        );
    END
    """
    conn = get_conn()
    try:
        cur = conn.cursor()
        cur.execute(create_states_sql)
    finally:
        conn.close()

def decimal_to_py(v):
    """Helper: pyodbc may return Decimal for SCOPE_IDENTITY; convert to int if possible."""
    if v is None:
        return None
    if isinstance(v, decimal.Decimal):
        return int(v)
    try:
        return int(v)
    except Exception:
        return v

def get_all_states_payload():
    """
    Return states in the shape the front-end expects:
    {
      "state-1": { "id": 1, "client_name": "...", "vendor_name": "StateName", "accounts": [], "custom_rules": [] },
      ...
    }
    """
    conn = get_conn()
    try:
        cur = conn.cursor()
        cur.execute("SELECT id, client_name, state_name FROM dbo.states ORDER BY created_at DESC")
        rows = cur.fetchall()
        payload = {}
        for r in rows:
            state_id = int(r.id)
            key = f"state-{state_id}"
            payload[key] = {
                "id": state_id,
                "client_name": (r.client_name if r.client_name is not None else "Unknown"),
                "vendor_name": r.state_name,
                "accounts": [],
                "custom_rules": []
            }
        return payload
    finally:
        conn.close()

@app.route('/api/data', methods=['GET'])
def api_get_data():
    try:
        payload = get_all_states_payload()
        return jsonify(payload), 200
    except Exception as e:
        app.logger.exception("Failed to fetch data")
        return jsonify({"detail": str(e)}), 500

@app.route('/api/states', methods=['POST'])
def api_create_state():
    """
    Expects JSON body:
    { "state_name": "Karnataka", "client_name": "Optional" }
    Returns created state object with numeric id.
    """
    body = request.get_json(silent=True) or {}
    state_name = (body.get('state_name') or body.get('vendor_name') or '').strip()
    client_name = (body.get('client_name') or 'Unknown').strip()

    if not state_name:
        return jsonify({"detail": "state_name is required"}), 400

    conn = get_conn()
    try:
        cur = conn.cursor()
        try:
            # Insert the state
            cur.execute("INSERT INTO dbo.states (state_name, client_name) VALUES (?, ?)", (state_name, client_name))
            # Get the last identity value (SCOPE_IDENTITY)
            cur.execute("SELECT SCOPE_IDENTITY() AS id")
            row = cur.fetchone()
            new_id = decimal_to_py(row.id) if row else None
        except pyodbc.IntegrityError as ie:
            # Likely UNIQUE constraint violation -> duplicate state
            return jsonify({"detail": "State already exists"}), 409

        created = {
            "id": new_id,
            "client_name": client_name,
            "vendor_name": state_name,
            "accounts": [],
            "custom_rules": []
        }
        return jsonify(created), 201
    except Exception as e:
        app.logger.exception("Failed to create state")
        return jsonify({"detail": str(e)}), 500
    finally:
        conn.close()

if __name__ == '__main__':
    init_db()
    host = os.getenv('APP_HOST', '0.0.0.0')
    port = int(os.getenv('APP_PORT', '8000'))
    debug = os.getenv('FLASK_DEBUG', 'true').lower() in ('1', 'true', 'yes')
    app.run(host=host, port=port, debug=debug)
