#!/usr/bin/env python3
import sqlite3
import csv
import urllib.request
import re
import sys

# --- CONFIGURATION ---

# 1. PASTE YOUR GOOGLE SHEET CSV URL HERE:
google_sheet_url = "PASTE_YOUR_GOOGLE_SHEET_CSV_URL_HERE"

# 2. DEFINE THE DATABASE FILENAME
db_file = 'membership/members.db'

# 3. DEFINE THE TABLE NAME
table_name = 'Members'

# --- END OF CONFIGURATION ---


def sanitize_column_name(header):
    """
    Sanitizes a string to be a valid SQL column name.
    Replaces spaces with underscores and removes non-alphanumeric characters.
    """
    # Replace spaces and dashes with underscores
    header = header.replace(' ', '_').replace('-', '_')
    # Remove all characters that are not letters, numbers, or underscores
    return re.sub(r'[^a-zA-Z0-9_]', '', header)

def main():
    """Main function to run the import process."""
    print("--- Starting Google Sheet to SQLite Import ---")

    try:
        # --- 1. VALIDATE CONFIGURATION ---
        if google_sheet_url == "PASTE_YOUR_GOOGLE_SHEET_CSV_URL_HERE" or not google_sheet_url.startswith('http'):
            print("\n[ERROR] Please paste a valid Google Sheet CSV URL into the 'google_sheet_url' variable.")
            sys.exit(1) # Exit the script with an error code

        # --- 2. FETCH THE CSV DATA ---
        print(f"Attempting to fetch data from Google Sheets...")
        # Use a request object to set a user-agent, which can prevent being blocked.
        req = urllib.request.Request(google_sheet_url, headers={'User-Agent': 'Python-Importer/1.0'})
        with urllib.request.urlopen(req) as response:
            if response.status != 200:
                raise ConnectionError(f"Failed to download. Status code: {response.status}")
            
            # Read and decode the content as UTF-8 text
            lines = [line.decode('utf-8') for line in response.readlines()]
            
        print("Data fetched successfully.")

        # --- 3. PARSE THE CSV DATA ---
        # Use csv.reader to handle parsing correctly
        csv_reader = csv.reader(lines)
        
        # The first line is the header row.
        headers = next(csv_reader)
        sanitized_headers = [sanitize_column_name(h) for h in headers]
        
        # The rest of the lines are data rows.
        data_rows = list(csv_reader)
        
        print(f"CSV data parsed. Found {len(headers)} columns and {len(data_rows)} data rows.")

        # --- 4. CONNECT TO SQLITE AND PREPARE THE DATABASE ---
        print(f"Connecting to SQLite database '{db_file}'...")
        # This will create the database file if it doesn't exist.
        conn = sqlite3.connect(db_file)
        cursor = conn.cursor()
        print("Database connection successful.")

        # Drop the table if it already exists for a fresh import.
        cursor.execute(f"DROP TABLE IF EXISTS {table_name}")
        print(f"Dropped existing table '{table_name}' (if it existed).")

        # --- 5. CREATE THE NEW TABLE ---
        # Build the CREATE TABLE query dynamically. All columns are TEXT for simplicity.
        column_definitions = ', '.join([f'"{header}" TEXT' for header in sanitized_headers])
        create_table_sql = f"CREATE TABLE {table_name} ({column_definitions});"
        
        cursor.execute(create_table_sql)
        print(f"Successfully created new table '{table_name}'.")

        # --- 6. INSERT THE DATA ---
        print("Starting data import...")
        # Prepare the INSERT statement for efficiency.
        placeholders = ', '.join(['?'] * len(sanitized_headers))
        insert_sql = f"INSERT INTO {table_name} VALUES ({placeholders});"
        
        cursor.executemany(insert_sql, data_rows)
        
        # Commit the changes to the database
        conn.commit()
        
        print(f"\n[SUCCESS] Import complete! Inserted {len(data_rows)} rows.")

    except Exception as e:
        print(f"\n[ERROR] An error occurred: {e}")
        # If an error occurs, roll back any changes
        if 'conn' in locals() and conn:
            conn.rollback()
        sys.exit(1)

    finally:
        # Always close the connection
        if 'conn' in locals() and conn:
            conn.close()
            print("Database connection closed.")

# This makes the script runnable from the command line
if __name__ == "__main__":
    main()