-- ============================================
-- Notifier Initial Schema
-- Version: 1.0.0
-- ============================================

-- ============================================
-- Categories
-- ============================================
CREATE TABLE categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    color TEXT DEFAULT '#6366f1'
        CHECK (color GLOB '#[0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F]'),
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ============================================
-- Main notifications
-- ============================================
CREATE TABLE notifications (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    message TEXT NOT NULL CHECK (length(message) <= 4096),
    context TEXT,
    category_id INTEGER,
    timezone TEXT NOT NULL DEFAULT 'America/Mexico_City',
    image_path TEXT,
    status TEXT NOT NULL DEFAULT 'active'
        CHECK (status IN ('active', 'paused', 'completed')),
    schedule_type TEXT NOT NULL
        CHECK (schedule_type IN ('dates', 'recurrence')),
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);

-- Trigger to auto-update updated_at
CREATE TRIGGER notifications_updated_at
AFTER UPDATE ON notifications
FOR EACH ROW
BEGIN
    UPDATE notifications SET updated_at = datetime('now') WHERE id = OLD.id;
END;

-- ============================================
-- Specific dates (for schedule_type = 'dates')
-- ============================================
CREATE TABLE notification_dates (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    notification_id INTEGER NOT NULL,
    scheduled_at TEXT NOT NULL,
    UNIQUE (notification_id, scheduled_at),
    FOREIGN KEY (notification_id) REFERENCES notifications(id) ON DELETE CASCADE
);

-- ============================================
-- Recurrence rules (for schedule_type = 'recurrence')
-- ============================================
CREATE TABLE notification_recurrence (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    notification_id INTEGER NOT NULL UNIQUE,
    type TEXT NOT NULL
        CHECK (type IN ('specific_months', 'monthly_date', 'monthly_weekday', 'weekly', 'daily')),
    months TEXT
        CHECK (months IS NULL OR json_valid(months)),
    day_of_month INTEGER
        CHECK (day_of_month IS NULL OR (day_of_month >= 1 AND day_of_month <= 31)),
    week_number TEXT
        CHECK (week_number IS NULL OR week_number IN ('1', '2', '3', '4', 'last')),
    weekday INTEGER
        CHECK (weekday IS NULL OR (weekday >= 0 AND weekday <= 6)),
    weekdays TEXT
        CHECK (weekdays IS NULL OR json_valid(weekdays)),
    interval_days INTEGER
        CHECK (interval_days IS NULL OR interval_days >= 1),
    anchor_date TEXT,
    time_of_day TEXT NOT NULL
        CHECK (
            time_of_day GLOB '[0-1][0-9]:[0-5][0-9]' OR
            time_of_day GLOB '2[0-3]:[0-5][0-9]'
        ),
    end_date TEXT,
    next_trigger_at TEXT,
    FOREIGN KEY (notification_id) REFERENCES notifications(id) ON DELETE CASCADE
);

-- ============================================
-- Subsequent (follow-up) notification definitions
-- ============================================
CREATE TABLE notification_subsequents (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    notification_id INTEGER NOT NULL,
    offset_minutes INTEGER NOT NULL DEFAULT 0
        CHECK (offset_minutes >= 0),
    offset_hours INTEGER NOT NULL DEFAULT 0
        CHECK (offset_hours >= 0),
    offset_days INTEGER NOT NULL DEFAULT 0
        CHECK (offset_days >= 0 AND offset_days <= 30),
    message TEXT NOT NULL CHECK (length(message) <= 4096),
    sort_order INTEGER NOT NULL DEFAULT 0,
    UNIQUE (notification_id, sort_order),
    FOREIGN KEY (notification_id) REFERENCES notifications(id) ON DELETE CASCADE
);

-- ============================================
-- Unified send jobs (THE scheduler reads this)
-- ============================================
CREATE TABLE send_jobs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    notification_id INTEGER NOT NULL,
    job_type TEXT NOT NULL
        CHECK (job_type IN ('main_date', 'main_recurrence', 'subsequent')),
    source_id INTEGER,
    source_history_id INTEGER,
    scheduled_for TEXT NOT NULL,
    next_attempt_at TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending', 'sending', 'sent', 'retrying', 'cancelled', 'failed')),
    attempt_count INTEGER NOT NULL DEFAULT 0,
    last_error TEXT,
    cancelled_reason TEXT
        CHECK (cancelled_reason IS NULL OR cancelled_reason IN (
            'callback_received', 'notification_paused',
            'notification_edited', 'notification_deleted', 'manual'
        )),
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now')),
    sent_at TEXT,
    telegram_message_id INTEGER,
    history_id INTEGER,
    FOREIGN KEY (notification_id) REFERENCES notifications(id) ON DELETE CASCADE
);

-- Prevent duplicate jobs for same occurrence
CREATE UNIQUE INDEX ux_send_jobs_unique
ON send_jobs(job_type, notification_id, COALESCE(source_id, 0), scheduled_for)
WHERE status NOT IN ('sent', 'cancelled', 'failed');

-- Trigger to auto-update updated_at
CREATE TRIGGER send_jobs_updated_at
AFTER UPDATE ON send_jobs
FOR EACH ROW
BEGIN
    UPDATE send_jobs SET updated_at = datetime('now') WHERE id = OLD.id;
END;

-- ============================================
-- History of sent messages (minimal, for UI)
-- ============================================
CREATE TABLE notification_history (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    notification_id INTEGER NOT NULL,
    send_job_id INTEGER,
    telegram_message_id INTEGER,
    sent_at TEXT NOT NULL DEFAULT (datetime('now')),
    is_subsequent INTEGER NOT NULL DEFAULT 0,
    subsequent_index INTEGER,
    FOREIGN KEY (notification_id) REFERENCES notifications(id) ON DELETE CASCADE,
    FOREIGN KEY (send_job_id) REFERENCES send_jobs(id) ON DELETE SET NULL
);

-- ============================================
-- Schema migrations tracking
-- ============================================
CREATE TABLE schema_migrations (
    version TEXT PRIMARY KEY,
    applied_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ============================================
-- Indexes (optimized for actual query patterns)
-- ============================================

-- Notifications
CREATE INDEX idx_notifications_status ON notifications(status);
CREATE INDEX idx_notifications_category ON notifications(category_id);

-- Dates
CREATE INDEX idx_dates_notification ON notification_dates(notification_id);
CREATE INDEX idx_dates_scheduled ON notification_dates(scheduled_at);

-- Recurrence
CREATE INDEX idx_recurrence_next ON notification_recurrence(next_trigger_at)
WHERE next_trigger_at IS NOT NULL;

-- Send jobs
CREATE INDEX idx_jobs_due ON send_jobs(status, next_attempt_at)
WHERE status IN ('pending', 'retrying');

CREATE INDEX idx_jobs_notification ON send_jobs(notification_id, status);

CREATE INDEX idx_jobs_source_history ON send_jobs(source_history_id)
WHERE job_type = 'subsequent';

-- History
CREATE INDEX idx_history_notification ON notification_history(notification_id, sent_at);
CREATE INDEX idx_history_sent_at ON notification_history(sent_at);
CREATE INDEX idx_history_telegram_msg ON notification_history(telegram_message_id)
WHERE telegram_message_id IS NOT NULL;
