-- SQL script to create the necessary tables in your PostgreSQL database

-- Users table to store login information
CREATE TABLE users (
    id SERIAL PRIMARY KEY,            -- Unique identifier for each user
    username VARCHAR(255) UNIQUE NOT NULL, -- Unique username for login
    password_hash VARCHAR(255) NOT NULL, -- Hashed password
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP -- Timestamp of user creation
);

-- Todos table to store todo items
CREATE TABLE todos (
    id SERIAL PRIMARY KEY,            -- Unique identifier for each todo
    user_id INTEGER NOT NULL,         -- Foreign key referencing the users table
    task TEXT NOT NULL,               -- The content of the todo item
    is_completed BOOLEAN DEFAULT FALSE, -- Status of the todo item
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, -- Timestamp of todo creation
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE -- Ensure referential integrity
);

-- Optional: Add an index for faster lookups by user_id in the todos table
CREATE INDEX idx_todos_user_id ON todos(user_id);

-- Optional: Add an index for faster lookups by username in the users table
CREATE INDEX idx_users_username ON users(username);