-- PropertyFlow V5: Unit Inspections, Meter Readings and QR Registry
-- Apply after propertyflow_v4.sql.

CREATE TABLE IF NOT EXISTS inspection_templates (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  template_name VARCHAR(160) NOT NULL,
  inspection_type VARCHAR(80) DEFAULT 'Routine',
  checklist_json JSON NULL,
  is_active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_template_tenant (tenant_id)
);

CREATE TABLE IF NOT EXISTS unit_inspections (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  building_id INT NULL,
  unit_id INT NULL,
  inspection_type VARCHAR(80) NOT NULL,
  inspection_date DATE NOT NULL,
  inspector_name VARCHAR(160),
  condition_rating VARCHAR(40) DEFAULT 'Good',
  findings TEXT,
  photos_required TINYINT(1) DEFAULT 1,
  tenant_signature_status VARCHAR(60) DEFAULT 'Not captured',
  status VARCHAR(60) DEFAULT 'Draft',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_unit_inspections_tenant (tenant_id),
  INDEX idx_unit_inspections_unit (unit_id, tenant_id),
  INDEX idx_unit_inspections_building (building_id, tenant_id)
);

CREATE TABLE IF NOT EXISTS inspection_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  inspection_id INT NOT NULL,
  area_name VARCHAR(120) NOT NULL,
  item_name VARCHAR(160) NOT NULL,
  condition_status VARCHAR(60) DEFAULT 'OK',
  note TEXT,
  photo_path VARCHAR(255),
  repair_required TINYINT(1) DEFAULT 0,
  maintenance_job_id INT NULL,
  INDEX idx_items_inspection (inspection_id, tenant_id)
);

CREATE TABLE IF NOT EXISTS meters (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  qr_code VARCHAR(80) UNIQUE,
  building_id INT NULL,
  unit_id INT NULL,
  asset_id INT NULL,
  meter_type VARCHAR(40) NOT NULL,
  meter_number VARCHAR(120),
  location_note VARCHAR(255),
  status VARCHAR(60) DEFAULT 'Active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_meters_tenant (tenant_id),
  INDEX idx_meters_unit (unit_id, tenant_id),
  INDEX idx_meters_building (building_id, tenant_id)
);

CREATE TABLE IF NOT EXISTS meter_readings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  meter_id INT NOT NULL,
  reading_date DATE NOT NULL,
  reading_value DECIMAL(14,3) NOT NULL,
  captured_by VARCHAR(160),
  photo_path VARCHAR(255),
  note TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_readings_meter (meter_id, tenant_id),
  INDEX idx_readings_date (reading_date)
);

CREATE TABLE IF NOT EXISTS qr_registry (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  qr_code VARCHAR(80) UNIQUE NOT NULL,
  entity_type VARCHAR(40) NOT NULL,
  entity_id INT NULL,
  label VARCHAR(180),
  scan_route VARCHAR(255),
  status VARCHAR(60) DEFAULT 'Active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_qr_tenant (tenant_id),
  INDEX idx_qr_entity (entity_type, entity_id)
);

INSERT INTO inspection_templates (tenant_id, template_name, inspection_type, checklist_json) VALUES
(1, 'Standard Residential Entry Inspection', 'Entry', JSON_ARRAY('Walls','Floors','Ceilings','Windows','Doors','Electrical','Plumbing','Kitchen','Bathroom','Keys/remotes')),
(1, 'Routine Rental Inspection', 'Routine', JSON_ARRAY('General condition','Leaks/damp','Safety','Tenant concerns','Maintenance required'));

INSERT INTO qr_registry (tenant_id, qr_code, entity_type, entity_id, label, scan_route, status) VALUES
(1, 'PF-BUILDING-000001', 'BUILDING', 1, 'Demo Block A', '/scan?code=PF-BUILDING-000001', 'Active'),
(1, 'PF-UNIT-000001', 'UNIT', 1, 'Demo Unit 1', '/scan?code=PF-UNIT-000001', 'Active');