water/server/plugin/saiadmin/db/20260909_water_business.sql

-- 云泉智配：独立供水业务表，不覆盖旧商户、租赁订单或资金数据。
CREATE TABLE IF NOT EXISTS sa_water_enterprise (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(120) NOT NULL, contact VARCHAR(60) NOT NULL, phone VARCHAR(30) NOT NULL,
 region VARCHAR(80) NOT NULL DEFAULT '', address VARCHAR(255) NOT NULL,
 agent_id BIGINT UNSIGNED NOT NULL DEFAULT 0, status TINYINT NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS sa_water_sku (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL,
 liters DECIMAL(8,2) NOT NULL, available INT UNSIGNED NOT NULL DEFAULT 0,
 locked INT UNSIGNED NOT NULL DEFAULT 0, transit INT UNSIGNED NOT NULL DEFAULT 0,
 empty_qty INT UNSIGNED NOT NULL DEFAULT 0, safety_stock INT UNSIGNED NOT NULL DEFAULT 0,
 status TINYINT NOT NULL DEFAULT 1, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS sa_water_package (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL,
 sku_id BIGINT UNSIGNED NOT NULL, buckets INT UNSIGNED NOT NULL, price_cents BIGINT UNSIGNED NOT NULL,
 status TINYINT NOT NULL DEFAULT 1, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS sa_water_account (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, enterprise_id BIGINT UNSIGNED NOT NULL,
 sku_id BIGINT UNSIGNED NOT NULL, entitlement INT UNSIGNED NOT NULL DEFAULT 0,
 frozen INT UNSIGNED NOT NULL DEFAULT 0, full_qty INT UNSIGNED NOT NULL DEFAULT 0,
 empty_qty INT UNSIGNED NOT NULL DEFAULT 0, threshold_qty INT UNSIGNED NOT NULL DEFAULT 2,
 daily_liters DECIMAL(10,2) NULL, updated_at DATETIME NOT NULL,
 UNIQUE KEY enterprise_sku (enterprise_id,sku_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS sa_water_order (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, reference VARCHAR(100) NOT NULL,
 enterprise_id BIGINT UNSIGNED NOT NULL, package_id BIGINT UNSIGNED NOT NULL,
 sku_id BIGINT UNSIGNED NOT NULL, buckets INT UNSIGNED NOT NULL, amount_cents BIGINT UNSIGNED NOT NULL,
 package_name VARCHAR(120) NOT NULL, created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL,
 UNIQUE KEY receipt (reference)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS sa_water_delivery (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, enterprise_id BIGINT UNSIGNED NOT NULL,
 sku_id BIGINT UNSIGNED NOT NULL, quantity INT UNSIGNED NOT NULL,
 address VARCHAR(255) NOT NULL, region VARCHAR(80) NOT NULL DEFAULT '',
 status VARCHAR(20) NOT NULL DEFAULT 'pending', driver VARCHAR(60) NOT NULL DEFAULT '',
 driver_phone VARCHAR(30) NOT NULL DEFAULT '', empty_returned INT UNSIGNED NOT NULL DEFAULT 0,
 active_key VARCHAR(100) NULL, created_by BIGINT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, completed_at DATETIME NULL,
 UNIQUE KEY active_task (active_key), KEY delivery_status (status,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS sa_water_ledger (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, enterprise_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
 sku_id BIGINT UNSIGNED NOT NULL DEFAULT 0, kind VARCHAR(30) NOT NULL,
 quantity INT NOT NULL DEFAULT 0, amount_cents BIGINT NOT NULL DEFAULT 0,
 reference VARCHAR(100) NOT NULL, note VARCHAR(255) NOT NULL DEFAULT '',
 created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL,
 KEY ledger_kind (kind,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS sa_water_device (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, sn VARCHAR(80) NOT NULL,
 enterprise_id BIGINT UNSIGNED NOT NULL, sku_id BIGINT UNSIGNED NOT NULL,
 name VARCHAR(120) NOT NULL, firmware VARCHAR(40) NOT NULL DEFAULT '',
 last_seen DATETIME NULL, today_liters DECIMAL(10,2) NULL, telemetry_date DATE NULL,
 fault VARCHAR(255) NOT NULL DEFAULT '', created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL,
 UNIQUE KEY device_sn (sn)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS sa_water_content (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(120) NOT NULL,
 image_url VARCHAR(500) NOT NULL DEFAULT '', target_url VARCHAR(500) NOT NULL DEFAULT '',
 audience VARCHAR(30) NOT NULL DEFAULT 'all', region VARCHAR(80) NOT NULL DEFAULT '',
 starts_at DATETIME NULL, ends_at DATETIME NULL, sort INT NOT NULL DEFAULT 0,
 status TINYINT NOT NULL DEFAULT 1, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;