create_tables.sql

-- Create users table
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    salt VARCHAR(255) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    wallet_address VARCHAR(42),
    token_balance NUMERIC(20, 4) DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Create meter_readings table
CREATE TABLE IF NOT EXISTS meter_readings (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    ts TIMESTAMP NOT NULL,
    consumption_kwh NUMERIC(10, 4) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Create index on meter_readings for faster queries
CREATE INDEX IF NOT EXISTS idx_meter_readings_user_id ON meter_readings(user_id);
CREATE INDEX IF NOT EXISTS idx_meter_readings_ts ON meter_readings(ts);

-- Insert a default user for testing
INSERT INTO users (email, password_hash, salt, first_name, last_name)
VALUES ('user@example.com', 
        'pbkdf2:sha256:260000$7NEZyz9tHOgvrvTy$352238d0c2f656a156e8a4f9e6b8c0c0a5a5a5a5a5a5a5a5a5a5a5a5a5a5a5a5', 
        'test_salt', 'Test', 'User')
ON CONFLICT (email) DO NOTHING;