-- V4 Building Details Layer
CREATE TABLE IF NOT EXISTS buildings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  property_id INT NULL,
  name VARCHAR(160) NOT NULL,
  building_code VARCHAR(50),
  building_type VARCHAR(80) DEFAULT 'Apartment Block',
  primary_use VARCHAR(80) DEFAULT 'Residential Rental',
  street_address VARCHAR(255),
  suburb VARCHAR(120),
  municipality VARCHAR(120) DEFAULT 'Mbombela',
  province VARCHAR(80) DEFAULT 'Mpumalanga',
  erf_number VARCHAR(80),
  sg_diagram_number VARCHAR(100),
  zoning VARCHAR(100),
  year_built INT NULL,
  floor_count INT DEFAULT 0,
  unit_count INT DEFAULT 0,
  parking_bays INT DEFAULT 0,
  roof_type VARCHAR(120),
  wall_type VARCHAR(120),
  foundation_notes TEXT,
  occupancy_certificate_no VARCHAR(120),
  occupancy_certificate_date DATE NULL,
  fire_certificate_no VARCHAR(120),
  fire_certificate_expiry DATE NULL,
  electrical_coc_no VARCHAR(120),
  electrical_coc_expiry DATE NULL,
  gas_coc_no VARCHAR(120),
  gas_coc_expiry DATE NULL,
  insurance_policy_no VARCHAR(120),
  insurer VARCHAR(160),
  broker VARCHAR(160),
  insured_value DECIMAL(14,2) DEFAULT 0,
  insurance_renewal_date DATE NULL,
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_buildings_tenant (tenant_id),
  INDEX idx_buildings_property (property_id)
);

CREATE TABLE IF NOT EXISTS building_blocks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  building_id INT NOT NULL,
  block_name VARCHAR(100) NOT NULL,
  description TEXT,
  INDEX idx_blocks_building (building_id, tenant_id)
);

CREATE TABLE IF NOT EXISTS building_floors (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  building_id INT NOT NULL,
  block_id INT NULL,
  floor_label VARCHAR(50) NOT NULL,
  floor_level INT DEFAULT 0,
  notes TEXT,
  INDEX idx_floors_building (building_id, tenant_id)
);

CREATE TABLE IF NOT EXISTS building_meters (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  building_id INT NOT NULL,
  unit_id INT NULL,
  meter_type ENUM('electricity','water','gas','solar','generator','other') NOT NULL,
  meter_number VARCHAR(120),
  supplier VARCHAR(160),
  location_note VARCHAR(255),
  last_reading DECIMAL(12,2) DEFAULT 0,
  last_reading_date DATE NULL,
  INDEX idx_meters_building (building_id, tenant_id)
);

CREATE TABLE IF NOT EXISTS building_assets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  building_id INT NOT NULL,
  asset_name VARCHAR(160) NOT NULL,
  asset_type VARCHAR(100),
  serial_number VARCHAR(120),
  location_note VARCHAR(255),
  service_interval_days INT DEFAULT 180,
  last_service_date DATE NULL,
  next_service_date DATE NULL,
  contractor_id INT NULL,
  status VARCHAR(50) DEFAULT 'active',
  notes TEXT,
  INDEX idx_assets_building (building_id, tenant_id)
);

CREATE TABLE IF NOT EXISTS building_compliance (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  building_id INT NOT NULL,
  compliance_type VARCHAR(120) NOT NULL,
  reference_number VARCHAR(120),
  authority VARCHAR(160),
  issue_date DATE NULL,
  expiry_date DATE NULL,
  status ENUM('valid','expiring','expired','pending','not_applicable') DEFAULT 'pending',
  document_id INT NULL,
  notes TEXT,
  INDEX idx_compliance_building (building_id, tenant_id),
  INDEX idx_compliance_expiry (expiry_date)
);

CREATE TABLE IF NOT EXISTS building_insurance_claims (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  building_id INT NOT NULL,
  claim_number VARCHAR(120),
  incident_date DATE NULL,
  claim_type VARCHAR(120),
  description TEXT,
  claim_amount DECIMAL(12,2) DEFAULT 0,
  excess_amount DECIMAL(12,2) DEFAULT 0,
  status VARCHAR(50) DEFAULT 'open',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_claims_building (building_id, tenant_id)
);

INSERT INTO buildings (tenant_id, property_id, name, building_code, building_type, primary_use, street_address, suburb, municipality, province, erf_number, zoning, floor_count, unit_count, parking_bays, roof_type, wall_type, occupancy_certificate_no, fire_certificate_no, electrical_coc_no, insurance_policy_no, insurer, insured_value)
VALUES (1, 1, 'Demo Block A', 'BLD-A', 'Apartment Block', 'Residential Rental', 'Demo Street 1', 'Nelspruit', 'Mbombela', 'Mpumalanga', 'ERF-0001', 'Residential 3', 3, 30, 36, 'IBR Sheeting', 'Brick and plaster', 'OCC-DEMO-001', 'FIRE-DEMO-001', 'ECOC-DEMO-001', 'INS-DEMO-001', 'Demo Insurer', 12500000.00);