-- ============================================================
-- Farm Manager API - MySQL 8.0 Schema
-- Run this in phpMyAdmin or MySQL CLI
-- ============================================================

CREATE DATABASE IF NOT EXISTS `farm_manager`
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE `farm_manager`;

-- --------------------------------------------------------
-- Table: units
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `units` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `unit_name`   VARCHAR(100)    NOT NULL,
    `unit_code`   VARCHAR(100)    NOT NULL,
    `location`    VARCHAR(255)    NULL,
    `qr_code`     VARCHAR(255)    NULL,
    `qr_data`     VARCHAR(255)    NULL,
    `status`      ENUM('active','inactive') NOT NULL DEFAULT 'active',
    `notes`       TEXT            NULL,
    `created_by`  BIGINT UNSIGNED NULL,
    `created_at`  TIMESTAMP       NULL,
    `updated_at`  TIMESTAMP       NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `units_unit_code_unique` (`unit_code`),
    KEY `units_created_by_foreign` (`created_by`),
    CONSTRAINT `units_created_by_foreign`
        FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table: checklist_configs
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `checklist_configs` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `field_name`  VARCHAR(150)    NOT NULL,
    `field_key`   VARCHAR(100)    NOT NULL,
    `field_type`  ENUM('text','number','boolean','select') NOT NULL,
    `options`     JSON            NULL,
    `is_required` TINYINT(1)      NOT NULL DEFAULT 0,
    `is_active`   TINYINT(1)      NOT NULL DEFAULT 1,
    `sort_order`  INT             NOT NULL DEFAULT 0,
    `created_by`  BIGINT UNSIGNED NULL,
    `created_at`  TIMESTAMP       NULL,
    `updated_at`  TIMESTAMP       NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `checklist_configs_field_key_unique` (`field_key`),
    KEY `checklist_configs_created_by_foreign` (`created_by`),
    CONSTRAINT `checklist_configs_created_by_foreign`
        FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table: checklist_entries
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `checklist_entries` (
    `id`                   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `unit_id`              BIGINT UNSIGNED NOT NULL,
    `entered_by`           BIGINT UNSIGNED NOT NULL,
    `entry_date`           DATE            NOT NULL,
    `entry_time`           TIME            NOT NULL,
    `condition_status`     ENUM('good','warning','critical') NOT NULL DEFAULT 'good',
    `issue_found`          TINYINT(1)      NOT NULL DEFAULT 0,
    `maintenance_required` TINYINT(1)      NOT NULL DEFAULT 0,
    `custom_fields`        JSON            NULL,
    `notes`                TEXT            NULL,
    `image`                VARCHAR(255)    NULL,
    `last_edited_by`       BIGINT UNSIGNED NULL,
    `last_edited_at`       TIMESTAMP       NULL,
    `created_at`           TIMESTAMP       NULL,
    `updated_at`           TIMESTAMP       NULL,
    PRIMARY KEY (`id`),
    KEY `checklist_entries_unit_id_foreign` (`unit_id`),
    KEY `checklist_entries_entered_by_foreign` (`entered_by`),
    KEY `checklist_entries_last_edited_by_foreign` (`last_edited_by`),
    KEY `checklist_entries_entry_date_index` (`entry_date`),
    CONSTRAINT `checklist_entries_unit_id_foreign`
        FOREIGN KEY (`unit_id`) REFERENCES `units` (`id`) ON DELETE CASCADE,
    CONSTRAINT `checklist_entries_entered_by_foreign`
        FOREIGN KEY (`entered_by`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `checklist_entries_last_edited_by_foreign`
        FOREIGN KEY (`last_edited_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
