-- Database: rhythm_db

-- Existing tables check/create
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    uuid VARCHAR(36) NOT NULL UNIQUE,
    name VARCHAR(50) NOT NULL,
    avatar VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS expenses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    uuid VARCHAR(36) NOT NULL UNIQUE,
    user_uuid VARCHAR(36) NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    category VARCHAR(50) NOT NULL,
    note VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    synced_at TIMESTAMP NULL
);

CREATE TABLE IF NOT EXISTS prayer_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    uuid VARCHAR(36) NOT NULL UNIQUE,
    lead_user_uuid VARCHAR(36) NOT NULL,
    completed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    synced_at TIMESTAMP NULL
);

CREATE TABLE IF NOT EXISTS visions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    uuid VARCHAR(36) NOT NULL UNIQUE,
    title VARCHAR(100) NOT NULL,
    target_date DATE,
    progress INT DEFAULT 0,
    image_url VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    synced_at TIMESTAMP NULL
);

-- New Table for Daily Habits Definitions
CREATE TABLE IF NOT EXISTS habits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    uuid VARCHAR(36) NOT NULL UNIQUE,
    title VARCHAR(100) NOT NULL,
    icon VARCHAR(50) DEFAULT 'fa-check-circle',
    is_active TINYINT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    synced_at TIMESTAMP NULL
);

CREATE TABLE IF NOT EXISTS daily_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    uuid VARCHAR(36) NOT NULL UNIQUE,
    habit_name VARCHAR(50) NOT NULL, -- Storing name for simplicity or uuid
    date_logged DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    synced_at TIMESTAMP NULL,
    UNIQUE KEY unique_daily_habit (habit_name, date_logged)
);

-- New Table for Budget Limits
CREATE TABLE IF NOT EXISTS budget_limits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category VARCHAR(50) NOT NULL UNIQUE,
    monthly_limit DECIMAL(10, 2) DEFAULT 0,
    synced_at TIMESTAMP NULL
);

-- Initial Data Seeding
INSERT IGNORE INTO users (uuid, name, avatar) VALUES 
('u1', 'Timothy', 'assets/avatars/tim.png'),
('u2', 'Rica', 'assets/avatars/rica.png');

INSERT IGNORE INTO habits (uuid, title, icon) VALUES 
('h1', '5km Run', 'fa-running'),
('h2', 'Weight Training', 'fa-dumbbell'),
('h3', 'Evening Reading', 'fa-book-open'),
('h4', 'Daily Devotion', 'fa-bible');

-- Default Limits
INSERT IGNORE INTO budget_limits (category, monthly_limit) VALUES 
('Food', 500.00),
('Transport', 200.00),
('Home', 1000.00),
('Date', 300.00),
('Bills', 800.00),
('Other', 200.00);
