-- ============================================================
-- Larspeenghar Pedigree — MySQL / MariaDB schema
-- Designed for ~3,000,000 animals and ~3,000 users.
--
-- Engine: InnoDB (row-level locking → real concurrency, FKs, crash-safe)
-- Charset: utf8mb4 (full Unicode incl. Pashto/Dari)
--
-- Import once:  mysql -u USER -p DBNAME < schema.sql
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------------------------------------------
-- OWNERS  (unified: herds + individual owners, distinguished by `type`)
-- ~ thousands of rows. BIGINT surrogate key.
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS owners (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  type         ENUM('herd','individual') NOT NULL DEFAULT 'herd',
  code         VARCHAR(40)  DEFAULT NULL,           -- herd code, e.g. LK-HRD-001
  herd_name    VARCHAR(180) DEFAULT NULL,
  name         VARCHAR(180) DEFAULT NULL,           -- contact / owner name
  breed_focus  VARCHAR(40)  DEFAULT NULL,
  phone        VARCHAR(40)  DEFAULT NULL,
  email        VARCHAR(160) DEFAULT NULL,
  province     VARCHAR(80)  DEFAULT NULL,
  district     VARCHAR(80)  DEFAULT NULL,
  address      VARCHAR(255) DEFAULT NULL,
  notes        TEXT         DEFAULT NULL,
  established  DATE         DEFAULT NULL,
  created_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_owner_code (code),
  KEY ix_owner_type (type),
  KEY ix_owner_province (province, district),
  KEY ix_owner_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------
-- USERS  (~3,000 rows). String PK keeps the historic "admin" id.
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
  id            VARCHAR(64)  NOT NULL,
  username      VARCHAR(64)  NOT NULL,
  password_hash VARCHAR(255) NOT NULL,             -- bcrypt / argon2
  role          ENUM('admin','owner') NOT NULL DEFAULT 'owner',
  owner_id      BIGINT UNSIGNED DEFAULT NULL,      -- which herd/owner this login sees
  name          VARCHAR(180) DEFAULT NULL,
  created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_login    TIMESTAMP    NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_username (username),
  KEY ix_user_owner (owner_id),
  CONSTRAINT fk_user_owner FOREIGN KEY (owner_id) REFERENCES owners (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------
-- ANIMALS  (~3,000,000 rows). This is the hot table — index heavily.
--   * BIGINT surrogate PK (small, fast for joins/indexes at scale)
--   * sire_id / dam_id self-reference linked parents (indexed → pedigree walks)
--   * manual ancestor name/reg columns for unlinked external ancestry
--   * photo stored as a FILE PATH, never base64 in the row
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS animals (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  reg_id       VARCHAR(64)  DEFAULT NULL,          -- registration id (business key)
  ear_tag      VARCHAR(64)  DEFAULT NULL,
  name         VARCHAR(180) DEFAULT NULL,
  breed        VARCHAR(40)  DEFAULT NULL,
  sex          ENUM('Male','Female') DEFAULT NULL,
  role         VARCHAR(20)  DEFAULT NULL,          -- Sire / Son / Cow
  dob          DATE         DEFAULT NULL,
  color        VARCHAR(80)  DEFAULT NULL,
  lactation    SMALLINT     DEFAULT NULL,
  owner_id     BIGINT UNSIGNED DEFAULT NULL,
  province     VARCHAR(80)  DEFAULT NULL,
  district     VARCHAR(80)  DEFAULT NULL,

  -- Linked parents (FK to animals.id) — drive pedigree + offspring queries
  sire_id      BIGINT UNSIGNED DEFAULT NULL,
  dam_id       BIGINT UNSIGNED DEFAULT NULL,

  -- Manual / external ancestry (when a parent is not a registered animal)
  sire_name VARCHAR(180) DEFAULT NULL, sire_reg VARCHAR(64) DEFAULT NULL, sire_breed VARCHAR(40) DEFAULT NULL,
  dam_name  VARCHAR(180) DEFAULT NULL, dam_reg  VARCHAR(64) DEFAULT NULL, dam_breed  VARCHAR(40) DEFAULT NULL,
  ss_name VARCHAR(180) DEFAULT NULL, ss_reg VARCHAR(64) DEFAULT NULL,  -- paternal grandsire
  sd_name VARCHAR(180) DEFAULT NULL, sd_reg VARCHAR(64) DEFAULT NULL,  -- paternal granddam
  ds_name VARCHAR(180) DEFAULT NULL, ds_reg VARCHAR(64) DEFAULT NULL,  -- maternal grandsire
  dd_name VARCHAR(180) DEFAULT NULL, dd_reg VARCHAR(64) DEFAULT NULL,  -- maternal granddam

  -- Production (305-day)
  milk_305  DECIMAL(7,1) DEFAULT NULL,
  fat_pct   DECIMAL(4,2) DEFAULT NULL,
  prot_pct  DECIMAL(4,2) DEFAULT NULL,
  peak      DECIMAL(5,1) DEFAULT NULL,
  dim       SMALLINT     DEFAULT NULL,
  scs       DECIMAL(4,1) DEFAULT NULL,

  photo        VARCHAR(255) DEFAULT NULL,          -- e.g. uploads/animals/1234.jpg
  breed_comp   JSON         DEFAULT NULL,          -- optional composition override
  inbreeding_f DECIMAL(5,2) DEFAULT NULL,          -- cached Wright's F % (for herd report at scale)
  avk          DECIMAL(5,2) DEFAULT NULL,          -- cached ancestor-loss / diversity %
  pending_status VARCHAR(20) DEFAULT NULL,
  notes        TEXT         DEFAULT NULL,
  extra        JSON         DEFAULT NULL,          -- future-proof bag for new fields

  created_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (id),
  KEY ix_animal_owner   (owner_id),               -- per-owner scoping (3000 users)
  KEY ix_animal_reg     (reg_id),
  KEY ix_animal_eartag  (ear_tag),
  KEY ix_animal_sire    (sire_id),                -- offspring-of-sire, pedigree
  KEY ix_animal_dam     (dam_id),
  KEY ix_animal_sexrole (sex, role),
  KEY ix_animal_region  (province, district),
  KEY ix_animal_name    (name(40)),               -- prefix sort/search
  KEY ix_animal_updated (updated_at),
  KEY ix_animal_f       (inbreeding_f),            -- herd inbreeding report sort
  FULLTEXT KEY ft_animal_search (name, reg_id, ear_tag),  -- fast text search
  CONSTRAINT fk_animal_owner FOREIGN KEY (owner_id) REFERENCES owners (id) ON DELETE SET NULL,
  CONSTRAINT fk_animal_sire  FOREIGN KEY (sire_id)  REFERENCES animals (id) ON DELETE SET NULL,
  CONSTRAINT fk_animal_dam   FOREIGN KEY (dam_id)   REFERENCES animals (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------
-- MESSAGES  (owner ↔ admin inbox / pedigree requests)
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS messages (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  from_user     VARCHAR(64)  DEFAULT NULL,
  to_user       VARCHAR(64)  DEFAULT NULL,
  from_name     VARCHAR(180) DEFAULT NULL,
  type          VARCHAR(40)  DEFAULT NULL,
  subject       VARCHAR(255) DEFAULT NULL,
  body          TEXT         DEFAULT NULL,
  is_read       TINYINT(1)   NOT NULL DEFAULT 0,
  status        VARCHAR(30)  DEFAULT NULL,
  linked_animal_id BIGINT UNSIGNED DEFAULT NULL,
  request_data  JSON         DEFAULT NULL,
  created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY ix_msg_to   (to_user, is_read),
  KEY ix_msg_from (from_user),
  KEY ix_msg_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------
-- SETTINGS  (key → JSON value): cert settings, i18n overrides,
-- breed averages, logo path, etc. Tiny table.
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS settings (
  k          VARCHAR(64) NOT NULL,
  v          JSON        DEFAULT NULL,
  updated_at TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (k)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- The default admin (username: admin / password: larspeenghar2024) is created
-- automatically by api_db.php on first run with a freshly generated bcrypt
-- hash — so no password hash is hard-coded here. Change it after first login.

-- ----------------------------------------------------------------
-- TAG COUNTERS — atomic running serial per region+breed (e.g. "1MB" → 42).
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS tag_counters (
  k VARCHAR(40) NOT NULL,
  n BIGINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (k)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ----------------------------------------------------------------
-- Upgrading an existing animals table? Run these once:
--   ALTER TABLE animals
--     ADD COLUMN inbreeding_f DECIMAL(5,2) DEFAULT NULL,
--     ADD COLUMN avk          DECIMAL(5,2) DEFAULT NULL,
--     ADD KEY ix_animal_f (inbreeding_f);
-- ----------------------------------------------------------------
