-- ═══════════════════════════════════════════════════════════
-- Meter Reading App — Full Database Schema
-- ═══════════════════════════════════════════════════════════

CREATE DATABASE IF NOT EXISTS meter_reading_db;
USE meter_reading_db;

-- ─── ROLES ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS roles (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ─── PERMISSIONS ────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS permissions (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ─── ROLE <-> PERMISSION MAPPING ────────────────────────────
CREATE TABLE IF NOT EXISTS role_permissions (
    role_id       INT NOT NULL,
    permission_id INT NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id)       REFERENCES roles(id)       ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
);

-- ─── USERS ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS 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,
    role        VARCHAR(50)  NOT NULL DEFAULT 'EMPLOYEE',
    status      VARCHAR(20)  NOT NULL DEFAULT 'ACTIVE',
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ─── DEVICES ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS 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
);

-- ─── METER READINGS ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS 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)
);

-- ═══════════════════════════════════════════════════════════
-- SEED DATA
-- ═══════════════════════════════════════════════════════════

-- Roles (only 2)
INSERT IGNORE INTO roles (name) VALUES ('SUPER_ADMIN'), ('EMPLOYEE');

-- Permissions
INSERT IGNORE INTO permissions (name) VALUES
    ('DASHBOARD_VIEW'),
    ('USER_CREATE'),
    ('USER_EDIT'),
    ('USER_DELETE'),
    ('READING_VIEW'),
    ('REPORT_VIEW'),
    ('ROLE_MANAGE');

-- SUPER_ADMIN → all permissions
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r CROSS JOIN permissions p WHERE r.name = 'SUPER_ADMIN';

-- EMPLOYEE → readings only
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r
JOIN permissions p ON p.name = 'READING_VIEW'
WHERE r.name = 'EMPLOYEE';

-- Default users (password: 123456)
INSERT IGNORE INTO users (name, employee_id, mobile, password, role, status) VALUES
('Vishnu',   'EMP001', '9876543210', '$2y$10$rGZ5B5lTcdZyI57zk6ZLaeycf/728nt0r4vugel2BS/fLzv14rHLK', 'SUPER_ADMIN', 'ACTIVE'),
('Admin User',    'EMP002', '9876543211', '$2y$10$rGZ5B5lTcdZyI57zk6ZLaeycf/728nt0r4vugel2BS/fLzv14rHLK', 'SUPER_ADMIN', 'ACTIVE'),
('Employee User', 'EMP003', '9876543212', '$2y$10$rGZ5B5lTcdZyI57zk6ZLaeycf/728nt0r4vugel2BS/fLzv14rHLK', 'EMPLOYEE',    'ACTIVE');