-- Rent Management App - Database Schema
-- Import this file via phpMyAdmin (cPanel > phpMyAdmin > Import)

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    role ENUM('owner','manager') NOT NULL DEFAULT 'manager',
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS rooms (
    id INT AUTO_INCREMENT PRIMARY KEY,
    room_number VARCHAR(20) UNIQUE NOT NULL,
    room_type VARCHAR(50) DEFAULT NULL,
    rent_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    status ENUM('occupied','vacant') NOT NULL DEFAULT 'vacant',
    current_tenant_id INT DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS tenants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    room_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(30) DEFAULT NULL,
    nid VARCHAR(50) DEFAULT NULL,
    move_in_date DATE DEFAULT NULL,
    move_out_date DATE DEFAULT NULL,
    due_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (room_id) REFERENCES rooms(id)
);

CREATE TABLE IF NOT EXISTS payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    room_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    type ENUM('rent_payment','rent_charge','adjustment') NOT NULL DEFAULT 'rent_payment',
    note VARCHAR(255) DEFAULT NULL,
    collected_by INT DEFAULT NULL,
    receipt_no VARCHAR(30) DEFAULT NULL,
    payment_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id),
    FOREIGN KEY (room_id) REFERENCES rooms(id),
    FOREIGN KEY (collected_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS tenant_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    room_id INT NOT NULL,
    name VARCHAR(100),
    phone VARCHAR(30),
    move_in_date DATE,
    move_out_date DATE,
    final_due DECIMAL(10,2),
    closed_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Default owner login: username = owner / password = owner123
-- CHANGE THIS PASSWORD after first login (see README)
INSERT INTO users (username, password_hash, full_name, role) VALUES
('owner', '$2b$10$nKGnOQA4I.w0fAmg2K0d1uQL0h1m1.UUCubTa1W3GY/PgzltPlAF.', 'Owner', 'owner');