SET
    FOREIGN_KEY_CHECKS = 0;

-- Drop tables in reverse order of dependencies
DROP TABLE IF EXISTS `interested_farmers`;

DROP TABLE IF EXISTS `vendor_rate_cards`;

DROP TABLE IF EXISTS `documents`;

DROP TABLE IF EXISTS `document_types`;

DROP TABLE IF EXISTS `vendors`;

DROP TABLE IF EXISTS `farmers`;

DROP TABLE IF EXISTS `crops`;

DROP TABLE IF EXISTS `measure_units`;

DROP TABLE IF EXISTS `crop_categories`;

DROP TABLE IF EXISTS `addresses`;

DROP TABLE IF EXISTS `users`;

DROP TABLE IF EXISTS `roles`;

SET FOREIGN_KEY_CHECKS = 1;

-- 1. Roles Table
CREATE TABLE roles (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    alias VARCHAR(255) UNIQUE,
    is_active TINYINT(1) DEFAULT 1,
    created_by BIGINT UNSIGNED DEFAULT NULL,
    updated_by BIGINT UNSIGNED DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

-- 2. Users Table
CREATE TABLE users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    role_id BIGINT UNSIGNED DEFAULT NULL,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE,
    phone VARCHAR(15) UNIQUE,
    password VARCHAR(255),
    is_phone_verified TINYINT(1) DEFAULT 0,
    otp VARCHAR(10) DEFAULT NULL,
    otp_expires_at DATETIME DEFAULT NULL,
    is_kyc_completed TINYINT(1) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    menu_ids JSON DEFAULT NULL,
    menu_action_ids JSON DEFAULT NULL,
    remember_token VARCHAR(100) DEFAULT NULL,
    email_verified_at TIMESTAMP NULL,
    created_by BIGINT UNSIGNED DEFAULT NULL,
    updated_by BIGINT UNSIGNED DEFAULT NULL,
    deleted_by BIGINT UNSIGNED DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    PRIMARY KEY (id),
    INDEX (role_id),
    CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE
    SET
        NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

-- 3. Addresses Table
CREATE TABLE addresses (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED DEFAULT NULL,
    address_type ENUM(
        'Permanent',
        'Current',
        'Office',
        'Market',
        'Warehouse'
    ) DEFAULT 'Permanent',
    village VARCHAR(100),
    city VARCHAR(100) NOT NULL,
    taluka VARCHAR(100),
    district VARCHAR(100) NOT NULL,
    state VARCHAR(100) NOT NULL,
    pin_code CHAR(6) NOT NULL,
    latitude DECIMAL(10, 8) DEFAULT NULL,
    longitude DECIMAL(11, 8) DEFAULT NULL,
    is_default TINYINT(1) DEFAULT 0,
    created_by BIGINT UNSIGNED DEFAULT NULL,
    updated_by BIGINT UNSIGNED DEFAULT NULL,
    deleted_by BIGINT UNSIGNED DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    PRIMARY KEY (id),
    INDEX (user_id),
    CONSTRAINT fk_addresses_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

-- 4. Crop Categories
CREATE TABLE crop_categories (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    parent_id BIGINT UNSIGNED DEFAULT NULL,
    category_name VARCHAR(100) NOT NULL,
    slug VARCHAR(150) UNIQUE,
    description TEXT,
    created_by BIGINT UNSIGNED DEFAULT NULL,
    updated_by BIGINT UNSIGNED DEFAULT NULL,
    deleted_by BIGINT UNSIGNED DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_categories_parent FOREIGN KEY (parent_id) REFERENCES crop_categories(id) ON DELETE
    SET
        NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

-- 5. Measure Units
CREATE TABLE measure_units (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    unit_name VARCHAR(50) NOT NULL UNIQUE,
    short_code VARCHAR(10),
    created_by BIGINT UNSIGNED DEFAULT NULL,
    updated_by BIGINT UNSIGNED DEFAULT NULL,
    deleted_by BIGINT UNSIGNED DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

-- 6. Crops Table
CREATE TABLE crops (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    crop_name VARCHAR(100) NOT NULL,
    slug VARCHAR(150) UNIQUE,
    crop_category_id BIGINT UNSIGNED DEFAULT NULL,
    default_measure_unit_id BIGINT UNSIGNED DEFAULT NULL,
    image_url VARCHAR(255) DEFAULT NULL,
    created_by BIGINT UNSIGNED DEFAULT NULL,
    updated_by BIGINT UNSIGNED DEFAULT NULL,
    deleted_by BIGINT UNSIGNED DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_crops_category FOREIGN KEY (crop_category_id) REFERENCES crop_categories(id) ON DELETE
    SET
        NULL,
        CONSTRAINT fk_crops_unit FOREIGN KEY (default_measure_unit_id) REFERENCES measure_units(id) ON DELETE
    SET
        NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

-- 7. Farmers Table
CREATE TABLE farmers (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED DEFAULT NULL,
    first_name VARCHAR(100),
    middle_name VARCHAR(100),
    last_name VARCHAR(100),
    phone VARCHAR(15),
    date_of_birth DATE,
    gender ENUM('male', 'female', 'other') DEFAULT 'male',
    profile_image VARCHAR(255),
    land_area DECIMAL(10, 2),
    ownership_type VARCHAR(100),
    where_do_you_sell VARCHAR(255),
    aadhaar_number VARCHAR(20),
    pancard_number VARCHAR(20),
    is_active TINYINT(1) DEFAULT 1,
    status ENUM('pending', 'approved', 'rejected', 'comment') DEFAULT 'pending',
    created_by BIGINT UNSIGNED DEFAULT NULL,
    updated_by BIGINT UNSIGNED DEFAULT NULL,
    deleted_by BIGINT UNSIGNED DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_farmers_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE
    SET
        NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

-- 8. Vendors Table
CREATE TABLE vendors (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    vendor_name VARCHAR(150) NOT NULL,
    vendor_type ENUM(
        'APMC Trader',
        'Commission Agent',
        'e-NAM Trader',
        'FPO',
        'Private Aggregator'
    ) NOT NULL,
    business_name VARCHAR(200),
    contact_person VARCHAR(150),
    registration_authority ENUM(
        'State APMC',
        'e-NAM',
        'Registrar of Co-operatives',
        'Other'
    ),
    license_number VARCHAR(100),
    license_issue_date DATE,
    license_expiry_date DATE,
    gst_number VARCHAR(15),
    pan_number VARCHAR(10),
    crops_handled TEXT,
    market_type ENUM(
        'APMC Mandi',
        'e-NAM',
        'Private Market',
        'Export'
    ),
    status ENUM('pending', 'approved', 'rejected', 'comment') DEFAULT 'pending',
    is_verified TINYINT(1) DEFAULT 0,
    approved_by BIGINT UNSIGNED DEFAULT NULL,
    rejected_by BIGINT UNSIGNED DEFAULT NULL,
    created_by BIGINT UNSIGNED DEFAULT NULL,
    updated_by BIGINT UNSIGNED DEFAULT NULL,
    deleted_by BIGINT UNSIGNED DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_vendors_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

-- 9. Document Types & Documents
CREATE TABLE document_types (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    type_name VARCHAR(100) NOT NULL,
    description VARCHAR(255),
    is_active TINYINT(1) DEFAULT 1,
    created_by BIGINT UNSIGNED DEFAULT NULL,
    updated_by BIGINT UNSIGNED DEFAULT NULL,
    deleted_by BIGINT UNSIGNED DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

CREATE TABLE documents (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    document_type_id BIGINT UNSIGNED NOT NULL,
    document_url VARCHAR(255) NOT NULL,
    document_number VARCHAR(50),
    status ENUM('pending', 'verified', 'rejected') DEFAULT 'pending',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_docs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_docs_type FOREIGN KEY (document_type_id) REFERENCES document_types(id) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

-- 10. Vendor Rate Cards
CREATE TABLE vendor_rate_cards (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    vendor_id BIGINT UNSIGNED NOT NULL,
    crop_id BIGINT UNSIGNED NOT NULL,
    measure_unit_id BIGINT UNSIGNED NOT NULL,
    min_rate DECIMAL(10, 2) NOT NULL,
    max_rate DECIMAL(10, 2) NOT NULL,
    effective_date DATE NOT NULL,
    display_on_marketplace TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_rate_vendor FOREIGN KEY (vendor_id) REFERENCES vendors(id) ON DELETE CASCADE,
    CONSTRAINT fk_rate_crop FOREIGN KEY (crop_id) REFERENCES crops(id) ON DELETE CASCADE,
    CONSTRAINT fk_rate_unit FOREIGN KEY (measure_unit_id) REFERENCES measure_units(id) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

-- 11. Interested Farmers
CREATE TABLE interested_farmers (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    farmer_id BIGINT UNSIGNED NOT NULL,
    vendor_rate_card_id BIGINT UNSIGNED NOT NULL,
    quantity DECIMAL(10, 2) NOT NULL,
    measure_unit_id BIGINT UNSIGNED NOT NULL,
    status ENUM(
        'pending',
        'contacted',
        'negotiating',
        'finalized',
        'cancelled'
    ) DEFAULT 'pending',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_interest_farmer FOREIGN KEY (farmer_id) REFERENCES farmers(id) ON DELETE CASCADE,
    CONSTRAINT fk_interest_rate FOREIGN KEY (vendor_rate_card_id) REFERENCES vendor_rate_cards(id) ON DELETE CASCADE,
    CONSTRAINT fk_interest_unit FOREIGN KEY (measure_unit_id) REFERENCES measure_units(id) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;