-- Table for jokes
CREATE TABLE jokes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    joke_text TEXT NOT NULL,
    type TEXT CHECK(type IN ('dad', 'knockknock', 'one-liner')) NOT NULL,
    active INTEGER DEFAULT 1
);

-- Table for scheduled calls
CREATE TABLE scheduled_calls (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    phone_number TEXT NOT NULL,
    joke_id INTEGER NOT NULL,
    scheduled_for DATETIME NOT NULL,
    processed INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Table for calls
CREATE TABLE calls (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    phone_number TEXT,
    call_sid TEXT,
    start_time DATETIME,
    end_time DATETIME,
    duration INTEGER,
    audio_file TEXT,
    transcript TEXT,
    status TEXT,
    direction TEXT CHECK(direction IN ('inbound', 'outbound'))
);

-- Sample jokes
INSERT INTO jokes (joke_text, type, active) VALUES 
('Why did the chicken cross the road? To get to the other side!', 'dad', 1),
('Knock knock. Who’s there? Lettuce. Lettuce who? Lettuce in, it’s cold out here!', 'knockknock', 1),
('I told my wife she was drawing her eyebrows too high. She looked surprised.', 'one-liner', 1);

-- Sample scheduled call (for testing timezone)
INSERT INTO scheduled_calls (phone_number, joke_id, scheduled_for) VALUES 
('+15555550123', 1, '2025-07-23 12:00:00'),
('+15555550124', 2, '2025-07-23 13:00:00');

-- Sample call log
INSERT INTO calls (phone_number, call_sid, start_time, end_time, duration, audio_file, transcript, status, direction) VALUES 
('+15555550123', 'CA123456789', '2025-07-22 12:00:00', '2025-07-22 12:01:00', 60, 'call1.mp3', 'Why did the chicken...', 'completed', 'outbound');

