-- Meter Reading App Database Schema

CREATE DATABASE IF NOT EXISTS meter_reading_db;
USE meter_reading_db;

CREATE TABLE meter_readings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    device_id VARCHAR(255) NOT NULL,
    previous_reading DECIMAL(10,2) NOT NULL,
    current_reading DECIMAL(10,2) NOT NULL,
    units DECIMAL(10,2) NOT NULL,
    photo_path VARCHAR(500) DEFAULT NULL,
    submitted_by VARCHAR(50) NOT NULL,
    reading_date DATE NOT NULL,
    note TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);
