-- ============================================================
-- 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: users
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name`       VARCHAR(100)    NOT NULL,
    `email`      VARCHAR(150)    NOT NULL,
    `password`   VARCHAR(255)    NOT NULL,
    `role`       ENUM('super_admin','admin','user') NOT NULL DEFAULT 'user',
    `is_frozen`  TINYINT(1)      NOT NULL DEFAULT 0,
    `phone`      VARCHAR(20)     NULL,
    `created_by` BIGINT UNSIGNED NULL,
    `created_at` TIMESTAMP       NULL,
    `updated_at` TIMESTAMP       NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `users_email_unique` (`email`),
    KEY `users_created_by_foreign` (`created_by`),
    CONSTRAINT `users_created_by_foreign`
        FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Super Admin (password: SuperAdmin@123)
INSERT INTO `users` (`name`, `email`, `password`, `role`, `created_at`, `updated_at`)
VALUES ('Super Admin', 'superadmin@farmmanager.com',
        '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
        'super_admin', NOW(), NOW())
ON DUPLICATE KEY UPDATE `id` = `id`;

-- Default Admin (password: Admin@123)
INSERT INTO `users` (`name`, `email`, `password`, `role`, `created_at`, `updated_at`)
VALUES ('Farm Admin', 'admin@farmmanager.com',
        '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
        'admin', NOW(), NOW())
ON DUPLICATE KEY UPDATE `id` = `id`;

-- --------------------------------------------------------
-- Table: personal_access_tokens (Sanctum)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `personal_access_tokens` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `tokenable_type` VARCHAR(255) NOT NULL,
    `tokenable_id`   BIGINT UNSIGNED NOT NULL,
    `name`        VARCHAR(255)    NOT NULL,
    `token`       VARCHAR(64)     NOT NULL,
    `abilities`   TEXT            NULL,
    `last_used_at` TIMESTAMP      NULL,
    `expires_at`  TIMESTAMP       NULL,
    `created_at`  TIMESTAMP       NULL,
    `updated_at`  TIMESTAMP       NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `personal_access_tokens_token_unique` (`token`),
    KEY `personal_access_tokens_tokenable_type_tokenable_id_index` (`tokenable_type`, `tokenable_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- 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;