CREATE TABLE tenants_master (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_name VARCHAR(160) NOT NULL,
  contact_name VARCHAR(120),
  email VARCHAR(160),
  phone VARCHAR(60),
  estimated_units INT DEFAULT 0,
  plan VARCHAR(80) DEFAULT 'PROPERTYFLOW_PREMIUM',
  status ENUM('PENDING','ACTIVE','SUSPENDED') DEFAULT 'PENDING',
  onboarding_step VARCHAR(60) DEFAULT 'REGISTERED',
  activated_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(160) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('SUPER_ADMIN','ADMIN','PROPERTY_MANAGER','MAINTENANCE','FINANCE','VIEWER') DEFAULT 'ADMIN',
  status ENUM('ACTIVE','INACTIVE') DEFAULT 'ACTIVE',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX(tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE onboarding_requests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  notes TEXT,
  status VARCHAR(60) DEFAULT 'PENDING_PAYMENT',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX(tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE payment_proofs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  file_name VARCHAR(255),
  file_path VARCHAR(255),
  status VARCHAR(60) DEFAULT 'SUBMITTED',
  admin_note TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX(tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE properties (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  name VARCHAR(180) NOT NULL,
  property_type VARCHAR(60),
  address TEXT,
  city VARCHAR(100),
  status VARCHAR(40) DEFAULT 'ACTIVE',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX(tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE units (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  property_id INT NOT NULL,
  unit_number VARCHAR(60) NOT NULL,
  bedrooms INT DEFAULT 0,
  bathrooms DECIMAL(3,1) DEFAULT 0,
  rent_amount DECIMAL(12,2) DEFAULT 0,
  status VARCHAR(40) DEFAULT 'VACANT',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX(tenant_id), INDEX(property_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE tenants (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  full_name VARCHAR(160) NOT NULL,
  email VARCHAR(160),
  phone VARCHAR(60),
  id_number VARCHAR(40),
  status VARCHAR(40) DEFAULT 'ACTIVE',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX(tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE leases (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  unit_id INT NOT NULL,
  person_id INT NOT NULL,
  start_date DATE,
  end_date DATE,
  monthly_rent DECIMAL(12,2) DEFAULT 0,
  deposit_amount DECIMAL(12,2) DEFAULT 0,
  status VARCHAR(40) DEFAULT 'ACTIVE',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX(tenant_id), INDEX(unit_id), INDEX(person_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE maintenance_jobs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  property_id INT NOT NULL,
  unit_id INT NULL,
  title VARCHAR(180) NOT NULL,
  priority VARCHAR(30) DEFAULT 'MEDIUM',
  status VARCHAR(40) DEFAULT 'OPEN',
  description TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX(tenant_id), INDEX(property_id), INDEX(unit_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE documents (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  entity_type VARCHAR(60),
  entity_id INT,
  document_type VARCHAR(80),
  file_name VARCHAR(255),
  file_path VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX(tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE audit_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT,
  user_id INT,
  action VARCHAR(120),
  details TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX(tenant_id), INDEX(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO tenants_master (id, company_name, contact_name, email, phone, estimated_units, plan, status, onboarding_step, activated_at) VALUES
(1, 'FixxWorx Control Account', 'FixxWorx Admin', 'admin@propertyflow.local', '', 0, 'INTERNAL', 'ACTIVE', 'ACTIVE', NOW()),
(2, 'Eastview Concept Development', 'Demo Client', 'client@propertyflow.local', '', 90, 'PROPERTYFLOW_PREMIUM', 'PENDING', 'PROOF_SUBMITTED', NULL);

INSERT INTO users (tenant_id, name, email, password_hash, role, status) VALUES
(1, 'FixxWorx Super Admin', 'admin@propertyflow.local', '$2y$12$2TyuyQDpqROujxQPus29xeqBQra2xHNuPtv7egjt..3A3GD3UYz92', 'SUPER_ADMIN', 'ACTIVE');

INSERT INTO onboarding_requests (tenant_id, notes, status) VALUES
(2, 'Demo request: viability study for roughly 90 one and two bedroom flatlets.', 'PENDING_ACTIVATION');

INSERT INTO properties (tenant_id, name, property_type, address, city, status) VALUES
(1, 'Internal Demo Portfolio', 'Building', 'Mbombela', 'Mbombela', 'PLANNING');

INSERT INTO units (tenant_id, property_id, unit_number, bedrooms, bathrooms, rent_amount, status) VALUES
(1, 1, 'A101', 1, 1, 6500.00, 'VACANT'),
(1, 1, 'A102', 2, 1, 8200.00, 'VACANT');


CREATE TABLE tenant_settings (
  tenant_id INT PRIMARY KEY,
  country_code VARCHAR(8) DEFAULT 'ZA',
  province VARCHAR(80),
  municipality VARCHAR(160),
  currency VARCHAR(8) DEFAULT 'ZAR',
  date_format VARCHAR(30) DEFAULT 'Y-m-d',
  language VARCHAR(20) DEFAULT 'en-ZA',
  legal_region VARCHAR(80) DEFAULT 'ZA',
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE legal_templates (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  template_name VARCHAR(180) NOT NULL,
  document_type VARCHAR(80) NOT NULL,
  language VARCHAR(20) DEFAULT 'en-ZA',
  region VARCHAR(80) DEFAULT 'ZA',
  version VARCHAR(30) DEFAULT '1.0',
  status ENUM('DRAFT','LEGAL_REVIEW','APPROVED','ARCHIVED') DEFAULT 'DRAFT',
  body MEDIUMTEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX(tenant_id), INDEX(document_type), INDEX(region)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE legal_register (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  region VARCHAR(80) NOT NULL,
  law_type VARCHAR(80) NOT NULL,
  title VARCHAR(220) NOT NULL,
  authority VARCHAR(160),
  summary TEXT,
  source_url VARCHAR(500),
  review_frequency VARCHAR(40) DEFAULT 'ANNUALLY',
  next_review_date DATE NULL,
  status ENUM('WATCH','ACTIVE','NEEDS_REVIEW','ARCHIVED') DEFAULT 'WATCH',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX(tenant_id), INDEX(region), INDEX(law_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE contractors (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  business_name VARCHAR(180) NOT NULL,
  contact_name VARCHAR(140),
  email VARCHAR(160),
  phone VARCHAR(60),
  trade VARCHAR(80),
  service_area VARCHAR(160),
  compliance_status VARCHAR(60) DEFAULT 'PENDING_DOCS',
  rating DECIMAL(3,2) DEFAULT 0,
  status VARCHAR(40) DEFAULT 'ACTIVE',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX(tenant_id), INDEX(trade), INDEX(service_area)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE contractor_documents (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  contractor_id INT NOT NULL,
  document_type VARCHAR(80),
  file_name VARCHAR(255),
  file_path VARCHAR(255),
  expiry_date DATE NULL,
  status VARCHAR(40) DEFAULT 'VALID',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX(tenant_id), INDEX(contractor_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE maintenance_assignments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  job_id INT NOT NULL,
  contractor_id INT NOT NULL,
  scope TEXT,
  status VARCHAR(60) DEFAULT 'ASSIGNED',
  quoted_amount DECIMAL(12,2) DEFAULT 0,
  approved_amount DECIMAL(12,2) DEFAULT 0,
  assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  completed_at DATETIME NULL,
  INDEX(tenant_id), INDEX(job_id), INDEX(contractor_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO tenant_settings (tenant_id,country_code,province,municipality,currency,date_format,language,legal_region) VALUES
(1,'ZA','Mpumalanga','City of Mbombela','ZAR','Y-m-d','en-ZA','ZA-MP-Mbombela');

INSERT INTO legal_register (tenant_id,region,law_type,title,authority,summary,source_url,review_frequency,next_review_date,status) VALUES
(1,'ZA','RENTAL_HOUSING','Rental Housing Act and written lease requirements','Department of Human Settlements','Track residential lease terms, deposits, inspections, maintenance obligations and tribunal process. Verify current amendments before use.','','QUARTERLY','2026-09-30','WATCH'),
(1,'ZA','SECTIONAL_TITLE','CSOS and Sectional Title scheme rules','CSOS','Track community scheme governance, conduct rules, levies, disputes and compliance directives.','','QUARTERLY','2026-09-30','WATCH'),
(1,'ZA','BUILDING_REGULATION','National Building Regulations / SANS 10400','Local Authority / SABS','Track safe building work, fire, occupancy, plan approvals and alterations.','','ANNUALLY','2027-01-31','WATCH'),
(1,'ZA-MP-Mbombela','MUNICIPAL_BYLAW','City of Mbombela policies and by-laws','City of Mbombela','Track local by-laws, land use, building control and property-rate related requirements.','','QUARTERLY','2026-09-30','WATCH');

INSERT INTO legal_templates (tenant_id,template_name,document_type,language,region,version,status,body) VALUES
(1,'Residential Lease Starter - SA Review Required','RESIDENTIAL_LEASE','en-ZA','ZA','0.1','DRAFT','This starter template is not legal advice. Landlord: {{landlord}}. Tenant: {{tenant}}. Property: {{property}} Unit: {{unit}}. Monthly rent: {{rent}}. Deposit: {{deposit}}. Start: {{start_date}} End: {{end_date}}. Include deposit handling, inspection records, maintenance obligations, breach process, POPIA consent and municipal/service rules after legal review.'),
(1,'Maintenance Notice - Access Request','MAINTENANCE_NOTICE','en-ZA','ZA','0.1','DRAFT','Notice to tenant {{tenant}}: Access is requested for maintenance at {{property}} {{unit}} on {{date}} for {{scope}}. Contractor: {{contractor}}. Contact: {{manager}}.');

INSERT INTO contractors (tenant_id,business_name,contact_name,email,phone,trade,service_area,compliance_status,rating,status) VALUES
(1,'Demo Plumbing Artisan','Demo Contact','plumber@example.local','000 000 0000','Plumbing','Mbombela','PENDING_DOCS',0,'ACTIVE');