CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    google_email TEXT UNIQUE NOT NULL,
    griito_email TEXT,
    griito_token TEXT,
    griito_refresh_token TEXT,
    token_expires_at INTEGER,
    phone TEXT,
    advance_minutes INTEGER DEFAULT 30,
    message_template TEXT DEFAULT 'Le recordamos su cita de {evento} a las {hora}',
    calendars TEXT DEFAULT '[]',
    default_audio_id INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS scheduled_calls (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    event_id TEXT NOT NULL,
    calendar_id TEXT NOT NULL,
    event_title TEXT,
    event_start DATETIME NOT NULL,
    call_at DATETIME NOT NULL,
    recipient_phone TEXT NOT NULL,
    audio_id INTEGER,
    griito_call_id INTEGER,
    status TEXT DEFAULT 'scheduled' CHECK(status IN ('scheduled','executed','cancelled','failed')),
    error_message TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    UNIQUE(user_id, event_id)
);

CREATE INDEX IF NOT EXISTS idx_calls_status ON scheduled_calls(status);
CREATE INDEX IF NOT EXISTS idx_calls_call_at ON scheduled_calls(call_at);
CREATE INDEX IF NOT EXISTS idx_calls_event ON scheduled_calls(user_id, event_id);
