import sqlite3
from datetime import datetime

DB_PATH = 'timelapse.db'

def init_db():
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute('''
        CREATE TABLE IF NOT EXISTS projects (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT UNIQUE NOT NULL,
            ip_address TEXT NOT NULL,
            port INTEGER NOT NULL,
            period_minutes INTEGER NOT NULL,
            username TEXT,
            password TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            last_capture TIMESTAMP,
            active INTEGER DEFAULT 1
        )
    ''')
    conn.commit()
    conn.close()

def add_project(name, ip_address, port, period_minutes, username=None, password=None):
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    try:
        c.execute('''
            INSERT INTO projects (name, ip_address, port, period_minutes, username, password)
            VALUES (?, ?, ?, ?, ?, ?)
        ''', (name, ip_address, port, period_minutes, username, password))
        conn.commit()
        return c.lastrowid
    except sqlite3.IntegrityError:
        return None
    finally:
        conn.close()

def get_all_projects():
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute('SELECT * FROM projects WHERE active = 1')
    projects = c.fetchall()
    conn.close()
    return projects

def get_project(project_id):
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute('SELECT * FROM projects WHERE id = ?', (project_id,))
    project = c.fetchone()
    conn.close()
    return project

def update_last_capture(project_id):
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute('''
        UPDATE projects SET last_capture = CURRENT_TIMESTAMP
        WHERE id = ?
    ''', (project_id,))
    conn.commit()
    conn.close()

def delete_project(project_id):
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute('UPDATE projects SET active = 0 WHERE id = ?', (project_id,))
    conn.commit()
    conn.close()