schema.sql

-- Drop existing tables for a clean slate during development
DROP TABLE IF EXISTS vacate_notices CASCADE;
DROP TABLE IF EXISTS maintenance_requests CASCADE;
DROP TABLE IF EXISTS payments CASCADE;
DROP TABLE IF EXISTS ussd_sessions CASCADE;
DROP TABLE IF EXISTS units CASCADE;
DROP TABLE IF EXISTS properties CASCADE;
DROP TABLE IF EXISTS users CASCADE;

-- Users table
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    full_name VARCHAR(100),
    phone_number VARCHAR(20) UNIQUE NOT NULL,
    pin VARCHAR(10) NOT NULL,
    role VARCHAR(30) NOT NULL CHECK (role IN ('tenant','landlord','manager','admin')),
    balance NUMERIC DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Properties table
CREATE TABLE IF NOT EXISTS properties (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    landlord_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Units table
CREATE TABLE IF NOT EXISTS units (
    id SERIAL PRIMARY KEY,
    property_id INTEGER REFERENCES properties(id) ON DELETE CASCADE,
    unit_number VARCHAR(20) NOT NULL,
    rent_amount NUMERIC NOT NULL,
    tenant_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    is_occupied BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- USSD sessions table (tracks active USSD dialogs)
CREATE TABLE IF NOT EXISTS ussd_sessions (
    id SERIAL PRIMARY KEY,
    session_id VARCHAR(100) UNIQUE,
    phone_number VARCHAR(20),
    current_step VARCHAR(100),
    data JSONB,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Payments table (now tracks which unit the payment is for)
CREATE TABLE IF NOT EXISTS payments (
    id SERIAL PRIMARY KEY,
    tenant_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    unit_id INTEGER REFERENCES units(id) ON DELETE SET NULL,
    amount NUMERIC NOT NULL,
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending','completed','failed')),
    transaction_code VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Maintenance requests table
CREATE TABLE IF NOT EXISTS maintenance_requests (
    id SERIAL PRIMARY KEY,
    tenant_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    unit_id INTEGER REFERENCES units(id) ON DELETE SET NULL,
    issue_type VARCHAR(50) NOT NULL,
    description TEXT,
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending','in_progress','resolved')),
    resolved_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Vacate notices table
CREATE TABLE IF NOT EXISTS vacate_notices (
    id SERIAL PRIMARY KEY,
    tenant_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    notice_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expected_vacate_date TIMESTAMP,
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending','approved','rejected'))
);

-- ─── SEED DATA ──────────────────────────────────────────────────────────────

-- Seed users (PIN: 1234 for all seed users)
INSERT INTO users (full_name, phone_number, pin, role, balance)
VALUES
  ('John Tenant',   '+254712345678', '1234', 'tenant',   15000),
  ('Jane Tenant',   '+254711111111', '1234', 'tenant',   0),
  ('Alice Landlord','+254722222222', '1234', 'landlord', 0),
  ('Bob Manager',   '+254733333333', '1234', 'manager',  0),
  ('Admin Super',   '+254744444444', '1234', 'admin',    0)
ON CONFLICT (phone_number) DO NOTHING;

-- Seed properties (owned by Alice Landlord = id 3)
INSERT INTO properties (name, landlord_id) VALUES
  ('Sunset Apartments', 3),
  ('Sunrise Villas',    3);

-- Seed units
INSERT INTO units (property_id, unit_number, rent_amount, tenant_id, is_occupied) VALUES
  (1, 'A1', 15000, 1, true),   -- John Tenant
  (1, 'A2', 15000, 2, true),   -- Jane Tenant
  (1, 'A3', 15000, NULL, false),
  (2, 'V1', 25000, NULL, false);

-- Seed payments (unit_id added)
INSERT INTO payments (tenant_id, unit_id, amount, status) VALUES
  (1, 1, 5000,  'completed'),
  (2, 2, 15000, 'completed');

-- Seed maintenance request
INSERT INTO maintenance_requests (tenant_id, unit_id, issue_type, description, status) VALUES
  (1, 1, 'Plumbing', 'Kitchen sink is leaking badly', 'pending');