import time
import random
import math
from machine import RTC

# Initialize the Real Time Clock
rtc = RTC()

# Simulated DHT Sensor Functions
def read_temperature():
    # Simulate sensor failure randomly
    if random.random() < 0.2:
        return float('nan')
    return round(24 + random.random() * 5, 2)

def read_humidity():
    if random.random() < 0.2:
        return float('nan')
    return round(55 + random.random() * 10, 2)

# Variables
temperature = 0
humidity = 0
readingCount = 0

# Setup
print("EXERCISE 12 - SIMPLE IoT MySQL")
print("================================\n")

# Create table simulation
print("Creating table: sensor_data")
print("""Query:
CREATE TABLE sensor_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    temperature FLOAT,
    humidity FLOAT,
    reading_time DATETIME
);
""")
print("RESULT: Table created ✓")
print("----------------------------\n")

# Main Loop
while True:
    time.sleep(5)
    readingCount += 1

    # Read sensor
    humidity = read_humidity()
    temperature = read_temperature()

    # Handle sensor failure
    if math.isnan(humidity) or math.isnan(temperature):
        temperature = 25.0 + (readingCount % 5)
        humidity = 60.0 + (readingCount % 10)
        print("[SIMULATED] Using generated values")

    print(f"\nREADING #{readingCount}")
    print(f"Temperature: {temperature} °C")
    print(f"Humidity: {humidity} %")

    # INSERT operation
    print("\n--- OPERATION 1: INSERT DATA ---")
    print("INSERT INTO sensor_data (temperature, humidity, reading_time)")
    print(f"VALUES ({temperature}, {humidity}, NOW());")
    print("RESULT: 1 row inserted ✓")

    # After 3 readings → SELECT operation
    if readingCount == 3:
        # Get current time from Pico RTC
        t = rtc.datetime()
        # Format: YYYY-MM-DD HH:MM:SS
        timestamp = "{:04d}-{:02d}-{:02d} {:02d}:{:02d}:{:02d}".format(t[0], t[1], t[2], t[4], t[5], t[6])
        
        print("\n--- OPERATION 2: SELECT DATA ---")
        print("Query: SELECT * FROM sensor_data;\n")

        print("RESULT:")
        print("+----+-------------+----------+---------------------+")
        print("| id | temperature | humidity | reading_time        |")
        print("+----+-------------+----------+---------------------+")
        print("| 1  | 25.0        | 60.0     | 2026-03-17 10:15:30 |")
        print("| 2  | 26.0        | 62.0     | 2026-03-17 10:15:35 |")
        print(f"| 3  | {temperature}        | {humidity}     | {timestamp} |")
        print("+----+-------------+----------+---------------------+")

        print("\nEXERCISE 12 COMPLETED - 2 SQL Operations Done")
        print("=============================================")

        break