import sqlite3

conn = sqlite3.connect(":memory:")   # temporary in-memory database for this demo
cursor = conn.cursor()
cursor.execute("CREATE TABLE employees (id INTEGER, name TEXT, department TEXT)")
cursor.executemany(
    "INSERT INTO employees VALUES (?, ?, ?)",
    [(1, "Priya Sharma", "IT"), (2, "Rahul Mehta", "IT"), (3, "Anita Patel", "Finance")]
)
conn.commit()

def search_safe(name_query):
    """Search by name using a parameterised query — always safe."""
    cursor.execute("SELECT * FROM employees WHERE name = ?", (name_query,))
    return cursor.fetchall()

def search_unsafe(name_query):
    """DELIBERATELY VULNERABLE — for demonstration only, never use this pattern."""
    query = f"SELECT * FROM employees WHERE name = '{name_query}'"
    print(f"  [unsafe query sent to DB]: {query}")
    cursor.execute(query)
    return cursor.fetchall()

# Normal search — both work identically
print("Normal search for 'Priya Sharma':")
print(" ", search_safe("Priya Sharma"))

# Malicious-style input — classic SQL injection payload
malicious_input = "x' OR '1'='1"

print(f"\nSearching with malicious input: {malicious_input!r}")
print("Safe version result:", search_safe(malicious_input))   # correctly finds nothing
print("Unsafe version result:", search_unsafe(malicious_input))  # leaks ALL rows!

import sqlite3
import csv
import io

# Simulated CSV content (in practice this would be open("employees.csv"))
csv_content = """name,department,salary
Priya Sharma,IT,85000
Rahul Mehta,IT,92000
Anita Patel,Finance,78000
Suresh Kumar,IT,67000"""

def load_csv_to_db(csv_file, conn):
    """Read employee rows from a CSV file object and bulk-insert them."""
    reader = csv.DictReader(csv_file)
    rows = [(row["name"], row["department"], int(row["salary"])) for row in reader]

    cursor = conn.cursor()
    cursor.executemany(
        "INSERT INTO employees (name, department, salary) VALUES (?, ?, ?)",
        rows
    )
    conn.commit()
    return len(rows)

# ── Run ──
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE employees (name TEXT, department TEXT, salary INTEGER)")

csv_file = io.StringIO(csv_content)    # simulates an open file object
inserted = load_csv_to_db(csv_file, conn)
print(f"Inserted {inserted} rows")

cursor = conn.cursor()
cursor.execute("SELECT department, COUNT(*), AVG(salary) FROM employees GROUP BY department")
for dept, count, avg_sal in cursor.fetchall():
    print(f"  {dept:10} {count} employees, avg ₹{avg_sal:,.0f}")

import sqlite3

class InsufficientFundsError(Exception):
    """Raised when an account doesn't have enough balance for a transfer."""
    pass

def get_balance(conn, account_id):
    cursor = conn.cursor()
    cursor.execute("SELECT balance FROM accounts WHERE id = ?", (account_id,))
    return cursor.fetchone()[0]

def transfer(conn, from_id, to_id, amount):
    """Transfer funds atomically. Raises InsufficientFundsError without writing anything."""
    cursor = conn.cursor()
    try:
        current_balance = get_balance(conn, from_id)
        if current_balance < amount:
            raise InsufficientFundsError(
                f"Account {from_id} has ₹{current_balance}, cannot send ₹{amount}"
            )
        cursor.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", (amount, from_id))
        cursor.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", (amount, to_id))
        conn.commit()
        print(f"✓ Transferred ₹{amount} from {from_id} to {to_id}")
    except InsufficientFundsError as e:
        conn.rollback()
        print(f"✗ Transfer blocked: {e}")

# ── Run ──
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE accounts (id INTEGER, balance INTEGER)")
conn.execute("INSERT INTO accounts VALUES (101, 10000), (102, 2000)")
conn.commit()

transfer(conn, 101, 102, 3000)     # succeeds — 101 has enough
transfer(conn, 102, 101, 50000)    # fails — 102 doesn't have enough, nothing changes

print(f"\nFinal balances: 101=₹{get_balance(conn, 101)}  102=₹{get_balance(conn, 102)}")

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base, sessionmaker

Base = declarative_base()

class Employee(Base):
    __tablename__ = "employees"
    id         = Column(Integer, primary_key=True)
    name       = Column(String)
    department = Column(String)
    salary     = Column(Integer)

    def __repr__(self):
        return f"{self.name} ({self.department}): ₹{self.salary:,}"

engine  = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

# CREATE
session.add_all([
    Employee(name="Priya Sharma", department="IT", salary=85000),
    Employee(name="Rahul Mehta",  department="IT", salary=92000),
    Employee(name="Anita Patel",  department="Finance", salary=78000),
])
session.commit()

# READ
print("All employees:")
for emp in session.query(Employee).all():
    print(f"  {emp}")

print("\nIT department only:")
for emp in session.query(Employee).filter(Employee.department == "IT").all():
    print(f"  {emp}")

# UPDATE
priya = session.query(Employee).filter(Employee.name == "Priya Sharma").first()
priya.salary = 90000
session.commit()
print(f"\nAfter raise: {priya}")

# DELETE
suresh = session.query(Employee).filter(Employee.name == "Anita Patel").first()
session.delete(suresh)
session.commit()
print(f"\nRemaining count: {session.query(Employee).count()}")

import sqlite3

class ReportError(Exception):
    """Raised when the salary report cannot be generated."""
    pass

def generate_department_report(db_path, min_avg_salary=0):
    """
    Connects to the database, runs a grouped salary report, and returns
    departments whose average salary is at least min_avg_salary.

    Raises:
        ReportError: wraps any underlying database failure with a clear message.
    """
    try:
        with sqlite3.connect(db_path) as conn:
            cursor = conn.cursor()
            cursor.execute(
                """SELECT department, COUNT(*) as headcount, AVG(salary) as avg_salary
                   FROM employees
                   GROUP BY department
                   HAVING AVG(salary) >= ?
                   ORDER BY avg_salary DESC""",
                (min_avg_salary,)
            )
            return cursor.fetchall()
    except sqlite3.OperationalError as e:
        raise ReportError(f"Could not generate report — database issue: {e}") from e

# ── Set up demo data, then run the report ──
conn = sqlite3.connect("report_demo.db")
conn.execute("DROP TABLE IF EXISTS employees")
conn.execute("CREATE TABLE employees (name TEXT, department TEXT, salary INTEGER)")
conn.executemany(
    "INSERT INTO employees VALUES (?, ?, ?)",
    [("Priya", "IT", 85000), ("Rahul", "IT", 92000),
     ("Anita", "Finance", 78000), ("Meera", "Finance", 95000),
     ("Kiran", "HR", 55000)]
)
conn.commit()
conn.close()

try:
    results = generate_department_report("report_demo.db", min_avg_salary=70000)
    print("Departments with average salary ≥ ₹70,000:\n")
    for dept, headcount, avg_sal in results:
        print(f"  {dept:10} {headcount} employees, avg ₹{avg_sal:,.0f}")
except ReportError as e:
    print(f"Report generation failed: {e}")