

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