# =============================================================================
# PROJECT        : Controlled Programmatic Load into Snowflake
# AUTHOR         : Gopikrishnan
# DESCRIPTION    :
#   This script demonstrates a controlled file ingestion process
#   using Python + Snowflake Connector.
#
# FEATURES:
#   ✅ Read Excel File
#   ✅ Convert Excel to CSV
#   ✅ Validate Input Data
#   ✅ Upload File to Snowflake Internal Stage
#   ✅ Controlled COPY INTO Load
#   ✅ Row Count Validation
#   ✅ Audit Logging
#   ✅ Exception Handling
#   ✅ File Archival
#
# =============================================================================

# =============================================================================
# IMPORT REQUIRED LIBRARIES
# =============================================================================

import snowflake.connector
import pandas as pd
import os
import shutil
from datetime import datetime

# =============================================================================
# STEP 1 - READ EXCEL FILE
# =============================================================================

print("\n===================================================")
print("STEP 1 - READING EXCEL FILE")
print("===================================================\n")

excel_file = "cobra_employees.xlsx"
csv_file = "cobra_employees.csv"

df = pd.read_excel(excel_file)

print(f"Excel File Loaded Successfully")
print(f"Total Source Rows : {len(df)}")

# =============================================================================
# STEP 2 - VALIDATE SOURCE DATA
# =============================================================================

print("\n===================================================")
print("STEP 2 - VALIDATING SOURCE DATA")
print("===================================================\n")

# Remove unwanted spaces from column names
df.columns = df.columns.str.strip()

if df.empty:
    raise Exception("ERROR : Input Excel File is Empty")

required_columns = [
    "GID",
    "NAME",
    "TYPE",
    "MANAGER",
    "JOINING_DATE"
]

missing_columns = [
    col for col in required_columns if col not in df.columns
]

if missing_columns:
    raise Exception(
        f"ERROR : Missing Columns -> {missing_columns}"
    )

print("Validation Successful")
print("Mandatory Columns Available")

# =============================================================================
# STEP 3 - CONVERT EXCEL TO CSV
# =============================================================================

print("\n===================================================")
print("STEP 3 - CONVERTING EXCEL TO CSV")
print("===================================================\n")

df.to_csv(csv_file, index=False)

print(f"CSV File Generated : {csv_file}")

# =============================================================================
# STEP 4 - CONNECT TO SNOWFLAKE
# =============================================================================

print("\n===================================================")
print("STEP 4 - CONNECTING TO SNOWFLAKE")
print("===================================================\n")

conn = snowflake.connector.connect(
    user='YOUR_SNOWFLAKE_USER',
    password='YOUR_SNOWFLAKE_PASSWORD',
    account='YOUR_SNOWFLAKE_ACCOUNT',
    warehouse='COMPUTE_WH',
    database='PRD_COBRA_PLANNING_DB',
    schema='PRD_COBRA_PLANNING_DB_SCH'
)

cursor = conn.cursor()

print("Connected to Snowflake Successfully")

# =============================================================================
# STEP 5 - CREATE AUDIT TABLE
# =============================================================================

print("\n===================================================")
print("STEP 5 - CREATING AUDIT TABLE")
print("===================================================\n")

create_audit_table_sql = """
CREATE TABLE IF NOT EXISTS LOAD_AUDIT (
    FILE_NAME            STRING,
    LOAD_TIME            TIMESTAMP,
    STATUS               STRING,
    SOURCE_ROW_COUNT     NUMBER,
    TARGET_ROW_COUNT     NUMBER,
    ERROR_MESSAGE        STRING
)
"""

cursor.execute(create_audit_table_sql)

print("Audit Table Ready")

# =============================================================================
# STEP 6 - UPLOAD FILE TO INTERNAL STAGE
# =============================================================================

print("\n===================================================")
print("STEP 6 - UPLOADING FILE TO INTERNAL STAGE")
print("===================================================\n")

csv_file_path = os.path.abspath(csv_file)

put_command = f"""
PUT 'file://{csv_file_path}'
@%COBRA_EMPLOYEES
AUTO_COMPRESS=TRUE
OVERWRITE=TRUE;
"""

try:

    put_result = cursor.execute(put_command).fetchall()

    print("PUT Command Executed Successfully")

    for row in put_result:
        print(row)

except Exception as e:

    raise Exception(f"PUT Command Failed : {e}")

# =============================================================================
# STEP 7 - VALIDATE FILE BEFORE LOADING
# =============================================================================

print("\n===================================================")
print("STEP 7 - VALIDATING FILE BEFORE LOAD")
print("===================================================\n")

validation_sql = """
COPY INTO COBRA_EMPLOYEES
FROM @%COBRA_EMPLOYEES
FILE_FORMAT = (
    TYPE = CSV
    SKIP_HEADER = 1
    FIELD_OPTIONALLY_ENCLOSED_BY = '"'
)
VALIDATION_MODE = RETURN_ERRORS;
"""

validation_result = cursor.execute(validation_sql).fetchall()

print("Validation Completed")

for row in validation_result:
    print(row)

# =============================================================================
# STEP 8 - LOAD DATA INTO TARGET TABLE
# =============================================================================

print("\n===================================================")
print("STEP 8 - LOADING DATA INTO TARGET TABLE")
print("===================================================\n")

copy_sql = """
COPY INTO COBRA_EMPLOYEES
FROM @%COBRA_EMPLOYEES
FILE_FORMAT = (
    TYPE = CSV
    SKIP_HEADER = 1
    FIELD_OPTIONALLY_ENCLOSED_BY = '"'
)
ON_ERROR = 'CONTINUE'
FORCE = FALSE;
"""

try:

    copy_result = cursor.execute(copy_sql).fetchall()

    conn.commit()

    print("COPY INTO Executed Successfully")

    for row in copy_result:
        print(row)

except Exception as e:

    conn.rollback()

    raise Exception(f"COPY INTO Failed : {e}")

# =============================================================================
# STEP 9 - VALIDATE TARGET ROW COUNT
# =============================================================================

print("\n===================================================")
print("STEP 9 - VALIDATING TARGET ROW COUNT")
print("===================================================\n")

source_row_count = len(df)

target_count_sql = """
SELECT COUNT(*) FROM COBRA_EMPLOYEES
"""

target_row_count = cursor.execute(
    target_count_sql
).fetchone()[0]

print(f"Source Row Count : {source_row_count}")
print(f"Target Row Count : {target_row_count}")

# =============================================================================
# STEP 10 - INSERT AUDIT LOG
# =============================================================================

print("\n===================================================")
print("STEP 10 - INSERTING AUDIT LOG")
print("===================================================\n")

audit_insert_sql = f"""
INSERT INTO LOAD_AUDIT
VALUES (
    '{csv_file}',
    CURRENT_TIMESTAMP(),
    'SUCCESS',
    {source_row_count},
    {target_row_count},
    NULL
)
"""

cursor.execute(audit_insert_sql)

conn.commit()

print("Audit Record Inserted Successfully")

# =============================================================================
# STEP 11 - ARCHIVE FILE
# =============================================================================

print("\n===================================================")
print("STEP 11 - ARCHIVING FILE")
print("===================================================\n")

archive_folder = "archive"

os.makedirs(archive_folder, exist_ok=True)

timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")

archive_file_name = (
    f"cobra_employees_{timestamp}.csv"
)

archive_path = os.path.join(
    archive_folder,
    archive_file_name
)

shutil.move(csv_file, archive_path)

print(f"File Archived Successfully")
print(f"Archive Location : {archive_path}")

# =============================================================================
# STEP 12 - CLOSE CONNECTIONS
# =============================================================================

print("\n===================================================")
print("STEP 12 - CLOSING CONNECTIONS")
print("===================================================\n")

cursor.close()
conn.close()

print("Snowflake Connection Closed Successfully")

# =============================================================================
# FINAL SUCCESS MESSAGE
# =============================================================================

print("\n===================================================")
print("CONTROLLED PROGRAMMATIC LOAD COMPLETED SUCCESSFULLY")
print("===================================================\n")
