import sqlite3
import os

# Define database path
DB_PATH = "crime_records.db"

# Connect to SQLite database
try:
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    # Create Crimes table
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS Crimes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        crime_type TEXT NOT NULL,
        description TEXT,
        location TEXT,
        date TEXT,
        officer_in_charge TEXT,
        status TEXT
    )
    """)

    # Create FIRs table
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS FIRs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        crime_id INTEGER,
        complainant_name TEXT,
        complainant_contact TEXT,
        filing_date TEXT,
        FOREIGN KEY (crime_id) REFERENCES Crimes(id)
    )
    """)

    # Create Users table
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS Users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT NOT NULL UNIQUE,
        password TEXT NOT NULL,
        role TEXT
    )
    """)

    # Clear existing data and insert diverse sample data for ML
    cursor.execute("DELETE FROM Crimes")
    cursor.execute("""
    INSERT INTO Crimes (crime_type, description, location, date, officer_in_charge, status)
    VALUES
        ('cyber crimes', 'phishing attack', 'new york', '2025-07-01', 'john doe', 'open'),
        ('cyber crimes', 'data breach', 'los angeles', '2025-07-02', 'jane smith', 'closed'),
        ('theft', 'shoplifting', 'chicago', '2025-07-03', 'bob johnson', 'open'),
        ('assault', 'physical altercation', 'miami', '2025-07-04', 'alice brown', 'open'),
        ('cyber crimes', 'ransomware attack', 'new york', '2025-07-05', 'john doe', 'closed'),
        ('theft', 'vehicle theft', 'los angeles', '2025-07-06', 'jane smith', 'open'),
        ('fraud', 'credit card scam', 'chicago', '2025-07-07', 'bob johnson', 'closed'),
        ('assault', 'domestic violence', 'miami', '2025-07-08', 'alice brown', 'open'),
        ('cyber crimes', 'identity theft', 'boston', '2025-07-09', 'john doe', 'open'),
        ('theft', 'burglary', 'los angeles', '2025-07-10', 'jane smith', 'closed'),
        ('fraud', 'online scam', 'new york', '2025-07-11', 'bob johnson', 'open'),
        ('assault', 'street fight', 'chicago', '2025-07-12', 'alice brown', 'closed')
    """)

    cursor.execute("DELETE FROM FIRs")
    cursor.execute("""
    INSERT INTO FIRs (crime_id, complainant_name, complainant_contact, filing_date)
    VALUES
        (1, 'alice brown', 'alice@example.com', '2025-07-01'),
        (2, 'bob green', 'bob@example.com', '2025-07-02'),
        (3, 'charlie davis', 'charlie@example.com', '2025-07-03'),
        (4, 'diana evans', 'diana@example.com', '2025-07-04'),
        (5, 'edward fox', 'edward@example.com', '2025-07-05')
    """)

    cursor.execute("DELETE FROM Users")
    cursor.execute("""
    INSERT INTO Users (username, password, role)
    VALUES
        ('admin', 'admin123', 'admin'),
        ('police1', 'pass123', 'police'),
        ('police2', 'pass456', 'police')
    """)

    conn.commit()
    print(f"SQLite database '{DB_PATH}' initialized successfully at {os.path.abspath(DB_PATH)}")
except Exception as e:
    print(f"Error initializing database: {e}")
finally:
    cursor.close()
    conn.close()