#!/usr/bin/env python3
# database.py

import sqlite3
from config import DB_FILE

def get_db_connection():
    """Создаёт соединение с БД"""
    conn = sqlite3.connect(DB_FILE, check_same_thread=False)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    """Инициализация базы данных"""
    conn = get_db_connection()
    cursor = conn.cursor()
    
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        tz TEXT,
        gender TEXT,
        name TEXT,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP
    )''')
    
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS pills (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER,
        name TEXT,
        dose TEXT,
        interval_days INTEGER DEFAULT 1,
        weekdays TEXT DEFAULT NULL,
        course_days INTEGER DEFAULT 0,
        start_date TEXT,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    )''')
    
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS pill_times (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        pill_id INTEGER,
        time TEXT,
        last_sent TEXT DEFAULT NULL,
        last_confirmed TEXT DEFAULT NULL,
        FOREIGN KEY (pill_id) REFERENCES pills(id) ON DELETE CASCADE
    )''')
    
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS messages (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        gender TEXT,
        text_message TEXT,
        sticker_id TEXT
    )''')
    
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS history (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        pill_id INTEGER,
        user_id INTEGER,
        ts TEXT,
        taken INTEGER,
        FOREIGN KEY (pill_id) REFERENCES pills(id) ON DELETE CASCADE,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    )''')
    
    # Индексы
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_pills_user_id ON pills(user_id)')
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_pill_times_pill_id ON pill_times(pill_id)')
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_history_user_pill ON history(user_id, pill_id)')
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_history_ts ON history(ts)')
    
    # Default messages
    cursor.execute("SELECT COUNT(*) FROM messages")
    if cursor.fetchone()[0] == 0:
        msg_rows = [
            ("Я мужчина", "Пора принять {pill} ({dose}).", None),
            ("Я мужчина", "Напоминание: {pill} — {dose}.", None),
            ("Я женщина", "Пора принять {pill} ({dose}).", None),
            ("Я женщина", "Напоминание: {pill} — {dose}.", None),
        ]
        cursor.executemany("INSERT INTO messages (gender, text_message, sticker_id) VALUES (?, ?, ?)", msg_rows)
    
    conn.commit()
    conn.close()