package org.vibeerp.demo

import org.slf4j.LoggerFactory
import org.springframework.boot.ApplicationArguments
import org.springframework.boot.ApplicationRunner
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate
import org.springframework.stereotype.Component
import java.math.BigDecimal
import java.sql.Timestamp
import java.time.Instant
import java.time.LocalDate
import java.util.UUID

/**
 * Populates the database with comprehensive demo data on first boot.
 *
 * Activated by `vibeerp.demo.seed=true` (set in application-dev.yaml).
 * The runner is **idempotent**: it checks whether catalog items already
 * exist and skips seeding entirely if they do. This means you can restart
 * the dev server without duplicating rows.
 *
 * All inserts go through [NamedParameterJdbcTemplate] rather than the
 * service layer so we bypass permission checks, event publishing, and
 * other side effects that would fire during normal runtime. The demo
 * seed is infrastructure, not a business operation.
 *
 * Data seeded:
 *   - 5 catalog items (goods + services)
 *   - 4 partners (customers + suppliers)
 *   - 3 inventory locations (warehouse + quarantine)
 *   - 3 opening stock balances with movement ledger entries
 *   - 2 sales orders with lines
 *   - 1 purchase order with lines
 *   - 1 work order with BOM inputs
 *   - 4 user-created metadata rows (custom field, form, rule, list view)
 */
@Component
@ConditionalOnProperty(prefix = "vibeerp.demo", name = ["seed"], havingValue = "true")
class DemoSeedRunner(
    private val jdbc: NamedParameterJdbcTemplate,
) : ApplicationRunner {

    private val log = LoggerFactory.getLogger(DemoSeedRunner::class.java)

    override fun run(args: ApplicationArguments) {
        if (alreadySeeded()) {
            log.info("[demo-seed] Demo data already present -- skipping.")
            return
        }
        log.info("[demo-seed] Seeding demo data ...")

        val now = Timestamp.from(Instant.now())
        val principal = "__demo_seed__"

        seedUoms(now, principal)
        seedItems(now, principal)
        seedPartners(now, principal)
        val locationIds = seedLocations(now, principal)
        seedStockBalances(now, principal, locationIds)
        seedSalesOrders(now, principal)
        seedPurchaseOrders(now, principal)
        seedWorkOrders(now, principal)
        seedMetadata(now)

        log.info("[demo-seed] Demo data seeded successfully.")
    }

    // ── guard ────────────────────────────────────────────────────────────

    private fun alreadySeeded(): Boolean {
        val count = jdbc.queryForObject(
            "SELECT COUNT(*) FROM catalog__item",
            MapSqlParameterSource(),
            Long::class.java,
        ) ?: 0L
        return count > 0
    }

    // ── UoMs ─────────────────────────────────────────────────────────────
    // The Liquibase seed covers kg/g/t/m/cm/mm/km/m2/l/ml/ea/sheet/pack/h/min.
    // We need a few more for the demo items: PCS and LTR are covered by
    // existing 'ea' and 'l'. We map demo UOM codes to the canonical ones
    // already in catalog__uom. If any are missing we insert them.

    @Suppress("UNUSED_PARAMETER")
    private fun seedUoms(now: Timestamp, principal: String) {
        // The Liquibase seed already provides: ea, l, sheet, h.
        // We do not need additional UoMs for the demo items.
        log.debug("[demo-seed] UoMs: using Liquibase-seeded codes (sheet, l, ea, h).")
    }

    // ── Items ────────────────────────────────────────────────────────────

    private fun seedItems(now: Timestamp, principal: String) {
        data class DemoItem(
            val code: String,
            val name: String,
            val description: String,
            val itemType: String,
            val baseUomCode: String,
        )

        val items = listOf(
            DemoItem("PAPER-A3-120G", "120g A3 Coated Paper", "Premium 120gsm coated paper, A3 size, suitable for brochure printing", "GOOD", "sheet"),
            DemoItem("INK-CMYK-BLACK", "CMYK Black Ink", "High-density CMYK black process ink for offset printing", "GOOD", "l"),
            DemoItem("BINDING-PERFECT", "Perfect Binding Service", "Machine-assisted perfect (adhesive) binding for booklets and brochures", "SERVICE", "ea"),
            DemoItem("DESIGN-HOURLY", "Design Consultation", "Graphic design and layout consultation, billed per hour", "SERVICE", "h"),
            DemoItem("BROCHURE-A4", "A4 Full-Color Brochure", "Finished A4 full-color brochure, CMYK offset print on 120gsm coated stock", "GOOD", "ea"),
        )

        val sql = """
            INSERT INTO catalog__item
                (id, code, name, description, item_type, base_uom_code, active, ext,
                 created_at, created_by, updated_at, updated_by, version)
            VALUES
                (:id, :code, :name, :description, :itemType, :baseUomCode, true, '{}'::jsonb,
                 :now, :principal, :now, :principal, 0)
        """.trimIndent()

        for (item in items) {
            jdbc.update(sql, MapSqlParameterSource()
                .addValue("id", UUID.randomUUID())
                .addValue("code", item.code)
                .addValue("name", item.name)
                .addValue("description", item.description)
                .addValue("itemType", item.itemType)
                .addValue("baseUomCode", item.baseUomCode)
                .addValue("now", now)
                .addValue("principal", principal))
        }
        log.debug("[demo-seed] Seeded {} items.", items.size)
    }

    // ── Partners ─────────────────────────────────────────────────────────

    private fun seedPartners(now: Timestamp, principal: String) {
        data class DemoPartner(
            val code: String,
            val name: String,
            val type: String,
            val email: String,
            val phone: String,
        )

        val partners = listOf(
            DemoPartner("CUST-ACME", "Acme Publishing Inc.", "CUSTOMER", "orders@acme-publishing.example.com", "+1-555-0101"),
            DemoPartner("CUST-GLOBEX", "Globex Print Services", "CUSTOMER", "procurement@globex-print.example.com", "+1-555-0102"),
            DemoPartner("SUPP-PAPERCO", "PaperCo Wholesale", "SUPPLIER", "sales@paperco-wholesale.example.com", "+1-555-0201"),
            DemoPartner("SUPP-INKMASTER", "InkMaster Supplies", "BOTH", "info@inkmaster.example.com", "+1-555-0202"),
        )

        val sql = """
            INSERT INTO partners__partner
                (id, code, name, type, email, phone, tax_id, website, active, ext,
                 created_at, created_by, updated_at, updated_by, version)
            VALUES
                (:id, :code, :name, :type, :email, :phone, NULL, NULL, true, '{}'::jsonb,
                 :now, :principal, :now, :principal, 0)
        """.trimIndent()

        for (p in partners) {
            jdbc.update(sql, MapSqlParameterSource()
                .addValue("id", UUID.randomUUID())
                .addValue("code", p.code)
                .addValue("name", p.name)
                .addValue("type", p.type)
                .addValue("email", p.email)
                .addValue("phone", p.phone)
                .addValue("now", now)
                .addValue("principal", principal))
        }
        log.debug("[demo-seed] Seeded {} partners.", partners.size)
    }

    // ── Locations ────────────────────────────────────────────────────────

    /**
     * Returns a map of location code to location UUID for FK references
     * in stock balance rows.
     */
    private fun seedLocations(now: Timestamp, principal: String): Map<String, UUID> {
        data class DemoLocation(val code: String, val name: String, val type: String)

        val locations = listOf(
            DemoLocation("WH-RAW", "Raw Materials Warehouse", "WAREHOUSE"),
            DemoLocation("WH-FG", "Finished Goods Warehouse", "WAREHOUSE"),
            DemoLocation("WH-STAGING", "Staging Area", "VIRTUAL"),
        )

        val sql = """
            INSERT INTO inventory__location
                (id, code, name, type, active, ext,
                 created_at, created_by, updated_at, updated_by, version)
            VALUES
                (:id, :code, :name, :type, true, '{}'::jsonb,
                 :now, :principal, :now, :principal, 0)
        """.trimIndent()

        val ids = mutableMapOf<String, UUID>()
        for (loc in locations) {
            val id = UUID.randomUUID()
            ids[loc.code] = id
            jdbc.update(sql, MapSqlParameterSource()
                .addValue("id", id)
                .addValue("code", loc.code)
                .addValue("name", loc.name)
                .addValue("type", loc.type)
                .addValue("now", now)
                .addValue("principal", principal))
        }
        log.debug("[demo-seed] Seeded {} locations.", locations.size)
        return ids
    }

    // ── Stock balances + movement ledger ─────────────────────────────────

    private fun seedStockBalances(
        now: Timestamp,
        principal: String,
        locationIds: Map<String, UUID>,
    ) {
        data class DemoBalance(
            val itemCode: String,
            val locationCode: String,
            val quantity: BigDecimal,
        )

        val balances = listOf(
            DemoBalance("PAPER-A3-120G", "WH-RAW", BigDecimal("10000.0000")),
            DemoBalance("INK-CMYK-BLACK", "WH-RAW", BigDecimal("1000.0000")),
            DemoBalance("BROCHURE-A4", "WH-FG", BigDecimal("5000.0000")),
        )

        val balanceSql = """
            INSERT INTO inventory__stock_balance
                (id, item_code, location_id, quantity,
                 created_at, created_by, updated_at, updated_by, version)
            VALUES
                (:id, :itemCode, :locationId, :quantity,
                 :now, :principal, :now, :principal, 0)
        """.trimIndent()

        val movementSql = """
            INSERT INTO inventory__stock_movement
                (id, item_code, location_id, delta, reason, reference, occurred_at,
                 created_at, created_by, updated_at, updated_by, version)
            VALUES
                (:id, :itemCode, :locationId, :delta, :reason, :reference, :occurredAt,
                 :now, :principal, :now, :principal, 0)
        """.trimIndent()

        for (b in balances) {
            val locationId = locationIds[b.locationCode]
                ?: error("Location ${b.locationCode} not found in seeded locations")

            // Insert the balance row
            jdbc.update(balanceSql, MapSqlParameterSource()
                .addValue("id", UUID.randomUUID())
                .addValue("itemCode", b.itemCode)
                .addValue("locationId", locationId)
                .addValue("quantity", b.quantity)
                .addValue("now", now)
                .addValue("principal", principal))

            // Insert a corresponding OPENING_BALANCE movement ledger entry
            jdbc.update(movementSql, MapSqlParameterSource()
                .addValue("id", UUID.randomUUID())
                .addValue("itemCode", b.itemCode)
                .addValue("locationId", locationId)
                .addValue("delta", b.quantity)
                .addValue("reason", "ADJUSTMENT")
                .addValue("reference", "DEMO-OPENING-BALANCE")
                .addValue("occurredAt", now)
                .addValue("now", now)
                .addValue("principal", principal))
        }
        log.debug("[demo-seed] Seeded {} stock balances with movement ledger entries.", balances.size)
    }

    // ── Sales orders ─────────────────────────────────────────────────────

    private fun seedSalesOrders(now: Timestamp, principal: String) {
        val orderDate = LocalDate.now()

        // --- SO-001: Acme Publishing, 2 lines ---
        val so1Id = UUID.randomUUID()
        val so1Total = BigDecimal("900.0000") // 1000 * 0.50 + 500 * 0.80
        insertSalesOrder(so1Id, "DEMO-SO-001", "CUST-ACME", orderDate, "USD", so1Total, now, principal)

        insertSalesOrderLine(so1Id, 1, "BROCHURE-A4",
            BigDecimal("1000.0000"), BigDecimal("0.5000"), "USD", now, principal)
        insertSalesOrderLine(so1Id, 2, "BROCHURE-A4",
            BigDecimal("500.0000"), BigDecimal("0.8000"), "USD", now, principal)

        // --- SO-002: Globex Print, 1 line ---
        val so2Id = UUID.randomUUID()
        val so2Total = BigDecimal("225.0000") // 500 * 0.45
        insertSalesOrder(so2Id, "DEMO-SO-002", "CUST-GLOBEX", orderDate, "USD", so2Total, now, principal)

        insertSalesOrderLine(so2Id, 1, "BROCHURE-A4",
            BigDecimal("500.0000"), BigDecimal("0.4500"), "USD", now, principal)

        log.debug("[demo-seed] Seeded 2 sales orders with 3 lines total.")
    }

    private fun insertSalesOrder(
        id: UUID, code: String, partnerCode: String, orderDate: LocalDate,
        currencyCode: String, totalAmount: BigDecimal, now: Timestamp, principal: String,
    ) {
        jdbc.update("""
            INSERT INTO orders_sales__sales_order
                (id, code, partner_code, status, order_date, currency_code, total_amount, ext,
                 created_at, created_by, updated_at, updated_by, version)
            VALUES
                (:id, :code, :partnerCode, 'DRAFT', :orderDate, :currencyCode, :totalAmount, '{}'::jsonb,
                 :now, :principal, :now, :principal, 0)
        """.trimIndent(), MapSqlParameterSource()
            .addValue("id", id)
            .addValue("code", code)
            .addValue("partnerCode", partnerCode)
            .addValue("orderDate", java.sql.Date.valueOf(orderDate))
            .addValue("currencyCode", currencyCode)
            .addValue("totalAmount", totalAmount)
            .addValue("now", now)
            .addValue("principal", principal))
    }

    private fun insertSalesOrderLine(
        orderId: UUID, lineNo: Int, itemCode: String,
        quantity: BigDecimal, unitPrice: BigDecimal, currencyCode: String,
        now: Timestamp, principal: String,
    ) {
        jdbc.update("""
            INSERT INTO orders_sales__sales_order_line
                (id, sales_order_id, line_no, item_code, quantity, unit_price, currency_code,
                 created_at, created_by, updated_at, updated_by, version)
            VALUES
                (:id, :orderId, :lineNo, :itemCode, :quantity, :unitPrice, :currencyCode,
                 :now, :principal, :now, :principal, 0)
        """.trimIndent(), MapSqlParameterSource()
            .addValue("id", UUID.randomUUID())
            .addValue("orderId", orderId)
            .addValue("lineNo", lineNo)
            .addValue("itemCode", itemCode)
            .addValue("quantity", quantity)
            .addValue("unitPrice", unitPrice)
            .addValue("currencyCode", currencyCode)
            .addValue("now", now)
            .addValue("principal", principal))
    }

    // ── Purchase orders ──────────────────────────────────────────────────

    private fun seedPurchaseOrders(now: Timestamp, principal: String) {
        val orderDate = LocalDate.now()

        val po1Id = UUID.randomUUID()
        // 10000 * 0.02 + 50 * 15 = 200 + 750 = 950
        val po1Total = BigDecimal("950.0000")
        insertPurchaseOrder(po1Id, "DEMO-PO-001", "SUPP-PAPERCO", orderDate, "USD", po1Total, now, principal)

        insertPurchaseOrderLine(po1Id, 1, "PAPER-A3-120G",
            BigDecimal("10000.0000"), BigDecimal("0.0200"), "USD", now, principal)
        insertPurchaseOrderLine(po1Id, 2, "INK-CMYK-BLACK",
            BigDecimal("50.0000"), BigDecimal("15.0000"), "USD", now, principal)

        log.debug("[demo-seed] Seeded 1 purchase order with 2 lines.")
    }

    private fun insertPurchaseOrder(
        id: UUID, code: String, partnerCode: String, orderDate: LocalDate,
        currencyCode: String, totalAmount: BigDecimal, now: Timestamp, principal: String,
    ) {
        jdbc.update("""
            INSERT INTO orders_purchase__purchase_order
                (id, code, partner_code, status, order_date, expected_date, currency_code, total_amount, ext,
                 created_at, created_by, updated_at, updated_by, version)
            VALUES
                (:id, :code, :partnerCode, 'DRAFT', :orderDate, :expectedDate, :currencyCode, :totalAmount, '{}'::jsonb,
                 :now, :principal, :now, :principal, 0)
        """.trimIndent(), MapSqlParameterSource()
            .addValue("id", id)
            .addValue("code", code)
            .addValue("partnerCode", partnerCode)
            .addValue("orderDate", java.sql.Date.valueOf(orderDate))
            .addValue("expectedDate", java.sql.Date.valueOf(orderDate.plusDays(14)))
            .addValue("currencyCode", currencyCode)
            .addValue("totalAmount", totalAmount)
            .addValue("now", now)
            .addValue("principal", principal))
    }

    private fun insertPurchaseOrderLine(
        orderId: UUID, lineNo: Int, itemCode: String,
        quantity: BigDecimal, unitPrice: BigDecimal, currencyCode: String,
        now: Timestamp, principal: String,
    ) {
        jdbc.update("""
            INSERT INTO orders_purchase__purchase_order_line
                (id, purchase_order_id, line_no, item_code, quantity, unit_price, currency_code,
                 created_at, created_by, updated_at, updated_by, version)
            VALUES
                (:id, :orderId, :lineNo, :itemCode, :quantity, :unitPrice, :currencyCode,
                 :now, :principal, :now, :principal, 0)
        """.trimIndent(), MapSqlParameterSource()
            .addValue("id", UUID.randomUUID())
            .addValue("orderId", orderId)
            .addValue("lineNo", lineNo)
            .addValue("itemCode", itemCode)
            .addValue("quantity", quantity)
            .addValue("unitPrice", unitPrice)
            .addValue("currencyCode", currencyCode)
            .addValue("now", now)
            .addValue("principal", principal))
    }

    // ── Work orders ──────────────────────────────────────────────────────

    private fun seedWorkOrders(now: Timestamp, principal: String) {
        val woId = UUID.randomUUID()

        jdbc.update("""
            INSERT INTO production__work_order
                (id, code, output_item_code, output_quantity, status, due_date,
                 source_sales_order_code, ext,
                 created_at, created_by, updated_at, updated_by, version)
            VALUES
                (:id, :code, :outputItemCode, :outputQuantity, 'DRAFT', :dueDate,
                 :sourceSoCode, '{}'::jsonb,
                 :now, :principal, :now, :principal, 0)
        """.trimIndent(), MapSqlParameterSource()
            .addValue("id", woId)
            .addValue("code", "DEMO-WO-001")
            .addValue("outputItemCode", "BROCHURE-A4")
            .addValue("outputQuantity", BigDecimal("1000.0000"))
            .addValue("dueDate", java.sql.Date.valueOf(LocalDate.now().plusDays(7)))
            .addValue("sourceSoCode", "DEMO-SO-001")
            .addValue("now", now)
            .addValue("principal", principal))

        // BOM inputs: 2 sheets of paper per brochure, 0.5 liters of ink per brochure
        insertWorkOrderInput(woId, 1, "PAPER-A3-120G", BigDecimal("2.0000"), "WH-RAW", now, principal)
        insertWorkOrderInput(woId, 2, "INK-CMYK-BLACK", BigDecimal("0.5000"), "WH-RAW", now, principal)

        log.debug("[demo-seed] Seeded 1 work order with 2 BOM inputs.")
    }

    private fun insertWorkOrderInput(
        workOrderId: UUID, lineNo: Int, itemCode: String,
        quantityPerUnit: BigDecimal, sourceLocationCode: String,
        now: Timestamp, principal: String,
    ) {
        jdbc.update("""
            INSERT INTO production__work_order_input
                (id, work_order_id, line_no, item_code, quantity_per_unit, source_location_code,
                 created_at, created_by, updated_at, updated_by, version)
            VALUES
                (:id, :workOrderId, :lineNo, :itemCode, :quantityPerUnit, :sourceLocationCode,
                 :now, :principal, :now, :principal, 0)
        """.trimIndent(), MapSqlParameterSource()
            .addValue("id", UUID.randomUUID())
            .addValue("workOrderId", workOrderId)
            .addValue("lineNo", lineNo)
            .addValue("itemCode", itemCode)
            .addValue("quantityPerUnit", quantityPerUnit)
            .addValue("sourceLocationCode", sourceLocationCode)
            .addValue("now", now)
            .addValue("principal", principal))
    }

    // ── Metadata (Tier 1 customization showcase) ─────────────────────────

    private fun seedMetadata(now: Timestamp) {
        // 1) Custom field: user-defined priority enum on SalesOrder
        jdbc.update("""
            INSERT INTO metadata__custom_field (id, source, payload, created_at, updated_at)
            VALUES (:id, 'user', :payload::jsonb, :now, :now)
        """.trimIndent(), MapSqlParameterSource()
            .addValue("id", UUID.randomUUID())
            .addValue("payload", """
                {
                  "key": "user_priority",
                  "targetEntity": "SalesOrder",
                  "type": {
                    "kind": "enum",
                    "allowedValues": ["LOW", "NORMAL", "HIGH", "URGENT"]
                  },
                  "required": false,
                  "pii": false,
                  "labelTranslations": {
                    "en": "Priority",
                    "zh-CN": "\u4F18\u5148\u7EA7"
                  }
                }
            """.trimIndent())
            .addValue("now", now))

        // 2) Form definition: quick approval form for sales orders
        jdbc.update("""
            INSERT INTO metadata__form (id, source, payload, created_at, updated_at)
            VALUES (:id, 'user', :payload::jsonb, :now, :now)
        """.trimIndent(), MapSqlParameterSource()
            .addValue("id", UUID.randomUUID())
            .addValue("payload", """
                {
                  "slug": "demo-quick-approval",
                  "entityName": "SalesOrder",
                  "title": "Quick Order Approval",
                  "purpose": "user-task",
                  "version": 1,
                  "jsonSchema": {
                    "type": "object",
                    "required": ["approved"],
                    "properties": {
                      "orderCode": {"type": "string", "title": "Order Code", "readOnly": true},
                      "customerName": {"type": "string", "title": "Customer", "readOnly": true},
                      "totalAmount": {"type": "number", "title": "Total Amount", "readOnly": true},
                      "approved": {"type": "boolean", "title": "Approve this order?"},
                      "notes": {"type": "string", "title": "Approval Notes", "maxLength": 500}
                    }
                  },
                  "uiSchema": {
                    "ui:order": ["orderCode", "customerName", "totalAmount", "approved", "notes"],
                    "notes": {"ui:widget": "textarea"}
                  }
                }
            """.trimIndent())
            .addValue("now", now))

        // 3) Rule: high-value order alert
        jdbc.update("""
            INSERT INTO metadata__rule (id, source, payload, created_at, updated_at)
            VALUES (:id, 'user', :payload::jsonb, :now, :now)
        """.trimIndent(), MapSqlParameterSource()
            .addValue("id", UUID.randomUUID())
            .addValue("payload", """
                {
                  "slug": "high-value-order-alert",
                  "name": "High Value Order Alert",
                  "description": "Logs an alert when a sales order over $500 is confirmed",
                  "enabled": true,
                  "triggerEvent": "SalesOrderConfirmedEvent",
                  "conditionLogic": "AND",
                  "conditions": [
                    {"field": "totalAmount", "operator": "gt", "value": "500"}
                  ],
                  "actions": [
                    {"type": "log", "config": {"message": "HIGH VALUE ORDER: {orderCode} for ${"\$"}{totalAmount} from {partnerCode}"}}
                  ],
                  "version": 1
                }
            """.trimIndent())
            .addValue("now", now))

        // 4) List view: custom sales orders view
        jdbc.update("""
            INSERT INTO metadata__list_view (id, source, payload, created_at, updated_at)
            VALUES (:id, 'user', :payload::jsonb, :now, :now)
        """.trimIndent(), MapSqlParameterSource()
            .addValue("id", UUID.randomUUID())
            .addValue("payload", """
                {
                  "slug": "demo-sales-orders-view",
                  "entityName": "SalesOrder",
                  "title": "Sales Orders (Custom View)",
                  "columns": [
                    {"field": "code", "label": "Order #", "sortable": true, "format": "link"},
                    {"field": "partnerCode", "label": "Customer", "sortable": true},
                    {"field": "status", "label": "Status", "sortable": true, "format": "status-badge"},
                    {"field": "totalAmount", "label": "Total", "sortable": true, "format": "money"}
                  ],
                  "defaultSort": {"field": "code", "direction": "desc"},
                  "filters": [
                    {"field": "status", "operator": "eq", "label": "Status"}
                  ],
                  "pageSize": 25,
                  "version": 1
                }
            """.trimIndent())
            .addValue("now", now))

        log.debug("[demo-seed] Seeded 4 user-created metadata rows (custom field, form, rule, list view).")
    }
}