-- Meter Reading App Database Schema

CREATE DATABASE IF NOT EXISTS meter_reading_db;
USE meter_reading_db;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    employee_id VARCHAR(50) UNIQUE NOT NULL,
    mobile VARCHAR(15) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT DEFAULT NULL,
    device_id VARCHAR(255) UNIQUE NOT NULL,
    token TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

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)
);

-- Sample user (password: 123456)
INSERT INTO users (name, employee_id, mobile, password) VALUES
('Vishnu', 'EMP001', '9876543210', '$2y$10$rGZ5B5lTcdZyI57zk6ZLaeycf/728nt0r4vugel2BS/fLzv14rHLK');