import sqlite3
from datetime import datetime

DATABASE = 'axis_timelapse.db'


def get_connection():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    return conn


def init_db():
    conn = get_connection()
    cursor = conn.cursor()
    
    # Cameras table
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS cameras (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            ip TEXT NOT NULL,
            port INTEGER DEFAULT 80,
            username TEXT NOT NULL,
            password TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    # Recordings table
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS recordings (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            camera_id INTEGER NOT NULL,
            recording_id TEXT,
            duration_minutes INTEGER,
            scheduled_stop TIMESTAMP,
            actual_stop TIMESTAMP,
            status TEXT DEFAULT 'recording',
            file_path TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (camera_id) REFERENCES cameras (id)
        )
    ''')
    
    conn.commit()
    conn.close()


def add_camera(name, ip, port, username, password):
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute(
        'INSERT INTO cameras (name, ip, port, username, password) VALUES (?, ?, ?, ?, ?)',
        (name, ip, port, username, password)
    )
    conn.commit()
    conn.close()


def get_cameras():
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM cameras ORDER BY id DESC')
    cameras = [dict(row) for row in cursor.fetchall()]
    conn.close()
    return cameras


def get_camera_by_id(camera_id):
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM cameras WHERE id = ?', (camera_id,))
    camera = cursor.fetchone()
    conn.close()
    return dict(camera) if camera else None


def delete_camera(camera_id):
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute('DELETE FROM cameras WHERE id = ?', (camera_id,))
    conn.commit()
    conn.close()


def add_recording(camera_id, recording_id, duration_minutes, scheduled_stop):
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute(
        '''INSERT INTO recordings (camera_id, recording_id, duration_minutes, scheduled_stop, status)
           VALUES (?, ?, ?, ?, 'recording')''',
        (camera_id, recording_id, duration_minutes, scheduled_stop)
    )
    recording_id = cursor.lastrowid
    conn.commit()
    conn.close()
    return recording_id


def get_recordings():
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute('''
        SELECT r.*, c.name as camera_name, c.ip as camera_ip
        FROM recordings r
        JOIN cameras c ON r.camera_id = c.id
        ORDER BY r.created_at DESC
    ''')
    recordings = [dict(row) for row in cursor.fetchall()]
    conn.close()
    return recordings


def get_recording_by_id(recording_id):
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM recordings WHERE id = ?', (recording_id,))
    recording = cursor.fetchone()
    conn.close()
    return dict(recording) if recording else None


def update_recording_status(recording_id, status, file_path=None):
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute(
        '''UPDATE recordings 
           SET status = ?, actual_stop = CURRENT_TIMESTAMP, file_path = ?
           WHERE id = ?''',
        (status, file_path, recording_id)
    )
    conn.commit()
    conn.close()


def delete_recording(recording_id):
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute('DELETE FROM recordings WHERE id = ?', (recording_id,))
    conn.commit()
    conn.close()