CREATE DATABASE IF NOT EXISTS lesotho_livestock CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE aia_lscensus;

CREATE TABLE districts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL UNIQUE
);

CREATE TABLE villages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  district_id INT NOT NULL,
  name VARCHAR(160) NOT NULL,
  UNIQUE KEY uq_village (district_id, name),
  FOREIGN KEY (district_id) REFERENCES districts(id)
);

CREATE TABLE animal_types (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL UNIQUE,
  icon VARCHAR(40) DEFAULT NULL
);

CREATE TABLE owners (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  server_uuid CHAR(36) NOT NULL UNIQUE,
  client_uuid CHAR(36) NULL,
  owner_name VARCHAR(180) NOT NULL,
  national_id VARCHAR(80) NULL,
  contact_number VARCHAR(60) NULL,
  district_id INT NOT NULL,
  village_id INT NULL,
  village_name VARCHAR(160) NULL,
  gps_lat DECIMAL(10,7) NOT NULL,
  gps_lng DECIMAL(10,7) NOT NULL,
  gps_accuracy DECIMAL(10,2) NULL,
  notes TEXT NULL,
  collected_by INT NOT NULL,
  collected_at DATETIME NOT NULL,
  sync_status ENUM('synced','duplicate','review') DEFAULT 'synced',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_district (district_id),
  INDEX idx_location (gps_lat, gps_lng),
  INDEX idx_client_uuid (client_uuid),
  FOREIGN KEY (district_id) REFERENCES districts(id),
  FOREIGN KEY (village_id) REFERENCES villages(id),
  FOREIGN KEY (collected_by) REFERENCES users(id)
);

CREATE TABLE owner_animals (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  owner_id BIGINT NOT NULL,
  animal_type_id INT NOT NULL,
  quantity INT NOT NULL DEFAULT 0,
  UNIQUE KEY uq_owner_animal (owner_id, animal_type_id),
  FOREIGN KEY (owner_id) REFERENCES owners(id) ON DELETE CASCADE,
  FOREIGN KEY (animal_type_id) REFERENCES animal_types(id)
);

CREATE TABLE sync_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  device_id VARCHAR(120) NULL,
  request_uuid CHAR(36) NULL,
  records_received INT DEFAULT 0,
  records_saved INT DEFAULT 0,
  records_failed INT DEFAULT 0,
  payload_bytes INT DEFAULT 0,
  ip_address VARCHAR(64) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO districts (name) VALUES
('Berea'),('Butha-Buthe'),('Leribe'),('Mafeteng'),('Maseru'),('Mohale''s Hoek'),('Mokhotlong'),('Qacha''s Nek'),('Quthing'),('Thaba-Tseka')
ON DUPLICATE KEY UPDATE name=VALUES(name);

INSERT INTO animal_types (name, icon) VALUES
('Cattle','cow'),('Sheep','sheep'),('Goats','goat'),('Horses','horse'),('Donkeys','donkey'),('Other','other')
ON DUPLICATE KEY UPDATE icon=VALUES(icon);

INSERT INTO villages (district_id, name)
SELECT d.id, v.name FROM districts d JOIN (
  SELECT 'Maseru' district, 'Ha-Mphaki' name UNION ALL SELECT 'Maseru','Roma' UNION ALL SELECT 'Maseru','Mazenod' UNION ALL
  SELECT 'Leribe','Hlotse' UNION ALL SELECT 'Butha-Buthe','Butha-Buthe Town' UNION ALL SELECT 'Mafeteng','Mafeteng Town' UNION ALL
  SELECT 'Quthing','Quthing Town' UNION ALL SELECT 'Thaba-Tseka','Thaba-Tseka Town'
) v ON v.district=d.name ON DUPLICATE KEY UPDATE name=VALUES(name);
-- Default password for demo admin: Admin@12345
