-- Arti Atolye ERP - Full Integrated CSV Module v1
-- Safe migration: does not drop existing tables.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE IF NOT EXISTS csv_import_rows (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    csv_import_log_id INT UNSIGNED NULL,
    last_seen_import_log_id INT UNSIGNED NULL,
    store_id INT UNSIGNED NOT NULL,
    uploaded_by INT UNSIGNED NULL,

    transaction_id VARCHAR(64) NOT NULL,
    etsy_order_id VARCHAR(64) NULL,

    sale_date DATETIME NULL,
    paid_date DATETIME NULL,
    ship_by_date DATE NULL,
    shipped_date DATE NULL,

    buyer_name VARCHAR(190) NULL,
    buyer_username VARCHAR(190) NULL,

    ship_name VARCHAR(190) NULL,
    ship_address1 VARCHAR(255) NULL,
    ship_address2 VARCHAR(255) NULL,
    ship_city VARCHAR(120) NULL,
    ship_state VARCHAR(120) NULL,
    ship_zip VARCHAR(50) NULL,
    ship_country VARCHAR(120) NULL,

    sku VARCHAR(100) NULL,
    listing_id VARCHAR(80) NULL,
    listing_title TEXT NULL,

    quantity INT UNSIGNED NOT NULL DEFAULT 1,
    price DECIMAL(12,2) NULL,
    currency VARCHAR(10) NULL,
    order_total DECIMAL(12,2) NULL,
    coupon_code VARCHAR(100) NULL,
    discount_amount DECIMAL(12,2) NULL,
    shipping_discount DECIMAL(12,2) NULL,
    order_shipping DECIMAL(12,2) NULL,
    order_sales_tax DECIMAL(12,2) NULL,

    variations_text TEXT NULL,
    personalization TEXT NULL,
    parsed_karat VARCHAR(40) NULL,
    parsed_color VARCHAR(60) NULL,
    parsed_size VARCHAR(60) NULL,
    parsed_chain_length VARCHAR(60) NULL,
    carrier VARCHAR(80) NULL,

    product_id INT UNSIGNED NULL,
    product_store_sku_id INT UNSIGNED NULL,
    product_card_missing TINYINT(1) NOT NULL DEFAULT 0,
    product_category VARCHAR(40) NULL,
    product_image_path VARCHAR(255) NULL,
    stone_code VARCHAR(10) NULL,
    stone_description VARCHAR(190) NULL,

    flagged_customer_id INT UNSIGNED NULL,
    has_flag_warning TINYINT(1) NOT NULL DEFAULT 0,
    flag_public_text VARCHAR(255) NULL,
    flag_admin_detail TEXT NULL,

    store_decision VARCHAR(30) NOT NULL DEFAULT 'pending',
    store_decided_by INT UNSIGNED NULL,
    store_decided_at DATETIME NULL,
    store_note TEXT NULL,

    admin_decision VARCHAR(30) NOT NULL DEFAULT 'pending',
    admin_decided_by INT UNSIGNED NULL,
    admin_decided_at DATETIME NULL,
    admin_note TEXT NULL,
    admin_rejection_reason TEXT NULL,

    row_status VARCHAR(40) NOT NULL DEFAULT 'store_pending',
    seen_count INT UNSIGNED NOT NULL DEFAULT 1,
    last_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    converted_order_item_id BIGINT UNSIGNED NULL,
    raw_csv_json LONGTEXT NULL,

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

    UNIQUE KEY uq_csv_import_rows_transaction_id (transaction_id),
    INDEX idx_csv_rows_store (store_id),
    INDEX idx_csv_rows_status (row_status),
    INDEX idx_csv_rows_admin_pending (row_status, store_id),
    INDEX idx_csv_rows_product_missing (product_card_missing),
    INDEX idx_csv_rows_flag_warning (has_flag_warning),
    INDEX idx_csv_rows_last_seen (last_seen_at),

    CONSTRAINT fk_csv_rows_import_log
        FOREIGN KEY (csv_import_log_id) REFERENCES csv_import_logs(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_csv_rows_last_import_log
        FOREIGN KEY (last_seen_import_log_id) REFERENCES csv_import_logs(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_csv_rows_store
        FOREIGN KEY (store_id) REFERENCES stores(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_csv_rows_uploaded_by
        FOREIGN KEY (uploaded_by) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_csv_rows_store_decided_by
        FOREIGN KEY (store_decided_by) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_csv_rows_admin_decided_by
        FOREIGN KEY (admin_decided_by) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_csv_rows_product
        FOREIGN KEY (product_id) REFERENCES products(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_csv_rows_product_sku
        FOREIGN KEY (product_store_sku_id) REFERENCES product_store_skus(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_csv_rows_flagged_customer
        FOREIGN KEY (flagged_customer_id) REFERENCES flagged_customers(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_csv_rows_converted_order
        FOREIGN KEY (converted_order_item_id) REFERENCES order_items(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add missing order_items columns used by the approval flow.
SET @col_exists := (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'order_items'
      AND COLUMN_NAME = 'shipped_date'
);
SET @sql := IF(@col_exists = 0,
    'ALTER TABLE order_items ADD COLUMN shipped_date DATE NULL AFTER ship_by_date',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'order_items'
      AND COLUMN_NAME = 'store_note'
);
SET @sql := IF(@col_exists = 0,
    'ALTER TABLE order_items ADD COLUMN store_note TEXT NULL AFTER raw_csv_json',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'order_items'
      AND COLUMN_NAME = 'admin_note'
);
SET @sql := IF(@col_exists = 0,
    'ALTER TABLE order_items ADD COLUMN admin_note TEXT NULL AFTER store_note',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- End.
