import csv
import os
import sqlite3
import zipfile
import requests
from io import StringIO

# Constants
CSV_URL = "https://storage.googleapis.com/play_public/supported_devices.csv"
DB_PATH = "library/src/main/res/raw/android-devices.db"
ZIP_PATH = "database/android-devices.zip"

# Ensure output directory exists
os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
os.makedirs(os.path.dirname(ZIP_PATH), exist_ok=True)

def download_devices_csv(url=CSV_URL):
    print("Downloading CSV from Google Play...")
    response = requests.get(url)
    response.encoding = "utf-16"
    if response.status_code != 200:
        raise Exception(f"Failed to fetch CSV. Status code: {response.status_code}")
    return response.text

def parse_devices(csv_data):
    print("Parsing CSV...")
    reader = csv.reader(StringIO(csv_data))
    next(reader)  # Skip header
    devices = []
    for row in reader:
        if len(row) == 4:
            manufacturer, name, codename, model = row
            devices.append((manufacturer, name, codename, model))
    print(f"Parsed {len(devices)} devices.")
    return devices

def create_database(devices):
    print("Creating SQLite database...")
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    # Drop old tables
    cursor.execute("DROP TABLE IF EXISTS devices;")
    cursor.execute("DROP TABLE IF EXISTS android_metadata;")

    # Create android_metadata table
    cursor.execute("""
        CREATE TABLE android_metadata (
            locale TEXT DEFAULT 'en_US'
        );
    """)
    cursor.execute("INSERT INTO android_metadata (locale) VALUES ('en_US');")

    # Create devices table
    cursor.execute("""
        CREATE TABLE devices (
            _id INTEGER PRIMARY KEY,
            manufacturer TEXT,
            name TEXT,
            codename TEXT,
            model TEXT
        );
    """)

    # Insert all device entries
    cursor.executemany("""
        INSERT INTO devices (manufacturer, name, codename, model)
        VALUES (?, ?, ?, ?);
    """, devices)

    conn.commit()
    conn.close()
    print("Database populated successfully.")

def zip_database():
    print(f"Zipping database to {ZIP_PATH}...")
    with zipfile.ZipFile(ZIP_PATH, 'w', zipfile.ZIP_DEFLATED) as zipf:
        zipf.write(DB_PATH, arcname=os.path.basename(DB_PATH))
    print("Zip archive created.")

def main():
    csv_data = download_devices_csv()
    devices = parse_devices(csv_data)
    create_database(devices)
    zip_database()
    print("Done.")

if __name__ == "__main__":
    main()