-- Table utilisateurs
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table rêves
CREATE TABLE dreams (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    date_time TIMESTAMP NOT NULL,
    title VARCHAR(255),
    story TEXT NOT NULL,
    emotions TEXT,
    symbols TEXT,
    interpretation TEXT,
    prayer_action TEXT,
    followup TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Index
CREATE INDEX idx_dreams_user_date ON dreams(user_id, date_time);
CREATE INDEX idx_dreams_user_title ON dreams(user_id, title);