
CREATE TABLE IF NOT EXISTS properties (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  location VARCHAR(200),
  units INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS units (
  id INT AUTO_INCREMENT PRIMARY KEY,
  property_id INT NOT NULL,
  name VARCHAR(50) NOT NULL,
  CONSTRAINT fk_units_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS caretaker_properties (
  user_id INT NOT NULL,
  property_id INT NOT NULL,
  PRIMARY KEY (user_id, property_id),
  CONSTRAINT fk_cp_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_cp_prop FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tenants (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(190),
  phone VARCHAR(60),
  property_id INT,
  unit VARCHAR(50),
  monthly_rent DECIMAL(12,2) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_tenant_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS invoices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  issue_date DATE NOT NULL,
  due_date DATE NOT NULL,
  amount_due DECIMAL(12,2) NOT NULL DEFAULT 0,
  amount_paid DECIMAL(12,2) NOT NULL DEFAULT 0,
  status ENUM('unpaid','partial','paid') NOT NULL DEFAULT 'unpaid',
  notes VARCHAR(255),
  water_fee DECIMAL(12,2) NOT NULL DEFAULT 0,
  garbage_fee DECIMAL(12,2) NOT NULL DEFAULT 0,
  extra_fee DECIMAL(12,2) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_invoice_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_id INT NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  paid_at DATETIME NOT NULL,
  method VARCHAR(40),
  ref VARCHAR(80),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_payment_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Demo data
INSERT INTO properties (name,location,units) VALUES
('Sunset Apartments','Umoja, Nairobi',20),
('Bee Centre Flats','Umoja Opp. Bee Centre',12);

INSERT INTO units (property_id,name) VALUES (1,'A-1'),(1,'A-2'),(2,'B-1'),(2,'B-2');

INSERT INTO tenants (name,email,phone,property_id,unit,monthly_rent) VALUES
('John Doe','john@example.com','0712345678',1,'A-2',15000.00),
('Mary Wambui','mary@example.com','0722333444',2,'B-1',12000.00);

INSERT INTO invoices (tenant_id, issue_date, due_date, amount_due, amount_paid, status, notes, water_fee, garbage_fee, extra_fee) VALUES
(1, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 7 DAY), 15000.00, 0.00, 'unpaid', 'September Rent', 0, 0, 0),
(2, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 7 DAY), 12000.00, 0.00, 'unpaid', 'September Rent', 0, 0, 0);
