-- ShasPOS Pro SaaS -- Phase 1 Canonical MySQL/MariaDB Schema v0.1.0 -- 2026-09-15 SET NAMES utf8mb4; SET time_zone = '+00:00'; SET FOREIGN_KEY_CHECKS = 0; CREATE TABLE users ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, email VARCHAR(191) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, full_name VARCHAR(160) NOT NULL, phone VARCHAR(40) NULL, preferred_language VARCHAR(10) NOT NULL DEFAULT 'en', timezone VARCHAR(64) NOT NULL DEFAULT 'UTC', status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE', email_verified_at DATETIME(6) NULL, last_login_at DATETIME(6) NULL, password_changed_at DATETIME(6) NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), KEY idx_users_status(status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE email_verifications ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, token_hash CHAR(64) NOT NULL UNIQUE, expires_at DATETIME(6) NOT NULL, verified_at DATETIME(6) NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), CONSTRAINT fk_email_verify_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE password_resets ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, token_hash CHAR(64) NOT NULL UNIQUE, expires_at DATETIME(6) NOT NULL, used_at DATETIME(6) NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), CONSTRAINT fk_password_reset_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE businesses ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, business_code VARCHAR(32) NOT NULL UNIQUE, name VARCHAR(191) NOT NULL, legal_name VARCHAR(191) NULL, business_type VARCHAR(32) NOT NULL DEFAULT 'RETAIL', email VARCHAR(191) NULL, phone VARCHAR(40) NULL, country_code CHAR(2) NOT NULL DEFAULT 'KW', currency_code CHAR(3) NOT NULL DEFAULT 'KWD', currency_precision TINYINT UNSIGNED NOT NULL DEFAULT 3, timezone VARCHAR(64) NOT NULL DEFAULT 'Asia/Kuwait', locale VARCHAR(16) NOT NULL DEFAULT 'en', tax_registration_no VARCHAR(80) NULL, address_line1 VARCHAR(191) NULL, address_line2 VARCHAR(191) NULL, city VARCHAR(120) NULL, state_region VARCHAR(120) NULL, postal_code VARCHAR(32) NULL, status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE', settings_json LONGTEXT NULL, created_by_user_id BIGINT UNSIGNED NOT NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), deleted_at DATETIME(6) NULL, KEY idx_business_status(status), CONSTRAINT fk_business_creator FOREIGN KEY(created_by_user_id) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE business_users ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, business_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, staff_code VARCHAR(32) NULL, display_name VARCHAR(160) NULL, status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE', is_owner TINYINT(1) NOT NULL DEFAULT 0, web_login_enabled TINYINT(1) NOT NULL DEFAULT 1, pos_pin_hash VARCHAR(255) NULL, pos_pin_changed_at DATETIME(6) NULL, last_activity_at DATETIME(6) NULL, joined_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), UNIQUE KEY uq_business_user(business_id,user_id), UNIQUE KEY uq_staff_code(business_id,staff_code), CONSTRAINT fk_business_user_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_business_user_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE outlets ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, business_id BIGINT UNSIGNED NOT NULL, outlet_code VARCHAR(32) NOT NULL, name VARCHAR(160) NOT NULL, phone VARCHAR(40) NULL, email VARCHAR(191) NULL, timezone VARCHAR(64) NULL, address_line1 VARCHAR(191) NULL, address_line2 VARCHAR(191) NULL, city VARCHAR(120) NULL, state_region VARCHAR(120) NULL, postal_code VARCHAR(32) NULL, is_default TINYINT(1) NOT NULL DEFAULT 0, status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE', created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), deleted_at DATETIME(6) NULL, UNIQUE KEY uq_outlet_code(business_id,outlet_code), KEY idx_outlet_status(business_id,status), CONSTRAINT fk_outlet_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE outlet_users ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, outlet_id BIGINT UNSIGNED NOT NULL, business_user_id BIGINT UNSIGNED NOT NULL, is_default TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), UNIQUE KEY uq_outlet_user(outlet_id,business_user_id), CONSTRAINT fk_outlet_user_outlet FOREIGN KEY(outlet_id) REFERENCES outlets(id) ON DELETE CASCADE, CONSTRAINT fk_outlet_user_member FOREIGN KEY(business_user_id) REFERENCES business_users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE devices ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, user_id BIGINT UNSIGNED NOT NULL, business_id BIGINT UNSIGNED NULL, outlet_id BIGINT UNSIGNED NULL, device_uuid VARCHAR(191) NOT NULL, platform VARCHAR(24) NOT NULL, device_name VARCHAR(160) NULL, app_version VARCHAR(40) NULL, app_build VARCHAR(40) NULL, push_token VARCHAR(500) NULL, status VARCHAR(24) NOT NULL DEFAULT 'ACTIVE', first_seen_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), last_seen_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), revoked_at DATETIME(6) NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), UNIQUE KEY uq_device_user_uuid(user_id,device_uuid), KEY idx_device_business(business_id,status), CONSTRAINT fk_device_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_device_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE SET NULL, CONSTRAINT fk_device_outlet FOREIGN KEY(outlet_id) REFERENCES outlets(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE user_sessions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, device_id BIGINT UNSIGNED NULL, refresh_token_hash CHAR(64) NOT NULL UNIQUE, session_family CHAR(36) NOT NULL, ip_address VARCHAR(45) NULL, user_agent VARCHAR(500) NULL, issued_at DATETIME(6) NOT NULL, expires_at DATETIME(6) NOT NULL, rotated_at DATETIME(6) NULL, revoked_at DATETIME(6) NULL, revoke_reason VARCHAR(191) NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), KEY idx_session_user(user_id,revoked_at,expires_at), KEY idx_session_family(session_family), CONSTRAINT fk_session_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_session_device FOREIGN KEY(device_id) REFERENCES devices(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE permissions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, permission_key VARCHAR(96) NOT NULL UNIQUE, module_key VARCHAR(64) NOT NULL, name VARCHAR(160) NOT NULL, description VARCHAR(500) NULL, is_legacy TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), KEY idx_permission_module(module_key) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE role_templates ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, role_key VARCHAR(64) NOT NULL UNIQUE, role_name VARCHAR(120) NOT NULL, description VARCHAR(500) NULL, sort_order INT NOT NULL DEFAULT 100, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE role_template_permissions ( role_template_id BIGINT UNSIGNED NOT NULL, permission_id BIGINT UNSIGNED NOT NULL, allowed TINYINT(1) NOT NULL DEFAULT 1, PRIMARY KEY(role_template_id,permission_id), CONSTRAINT fk_rtp_role FOREIGN KEY(role_template_id) REFERENCES role_templates(id) ON DELETE CASCADE, CONSTRAINT fk_rtp_perm FOREIGN KEY(permission_id) REFERENCES permissions(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE roles ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, business_id BIGINT UNSIGNED NOT NULL, role_key VARCHAR(64) NOT NULL, role_name VARCHAR(120) NOT NULL, description VARCHAR(500) NULL, is_system TINYINT(1) NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 100, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), UNIQUE KEY uq_role_business_key(business_id,role_key), CONSTRAINT fk_role_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE role_permissions ( role_id BIGINT UNSIGNED NOT NULL, permission_id BIGINT UNSIGNED NOT NULL, allowed TINYINT(1) NOT NULL DEFAULT 1, PRIMARY KEY(role_id,permission_id), CONSTRAINT fk_role_perm_role FOREIGN KEY(role_id) REFERENCES roles(id) ON DELETE CASCADE, CONSTRAINT fk_role_perm_perm FOREIGN KEY(permission_id) REFERENCES permissions(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE business_user_roles ( business_user_id BIGINT UNSIGNED NOT NULL, role_id BIGINT UNSIGNED NOT NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY(business_user_id,role_id), CONSTRAINT fk_bur_user FOREIGN KEY(business_user_id) REFERENCES business_users(id) ON DELETE CASCADE, CONSTRAINT fk_bur_role FOREIGN KEY(role_id) REFERENCES roles(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE user_permission_overrides ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, business_user_id BIGINT UNSIGNED NOT NULL, permission_id BIGINT UNSIGNED NOT NULL, override_state TINYINT NOT NULL COMMENT '-1 deny, +1 allow', created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), UNIQUE KEY uq_user_permission_override(business_user_id,permission_id), CONSTRAINT fk_upo_user FOREIGN KEY(business_user_id) REFERENCES business_users(id) ON DELETE CASCADE, CONSTRAINT fk_upo_perm FOREIGN KEY(permission_id) REFERENCES permissions(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE approval_rules ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, business_id BIGINT UNSIGNED NOT NULL, rule_key VARCHAR(96) NOT NULL, action_permission_id BIGINT UNSIGNED NULL, approver_permission_id BIGINT UNSIGNED NULL, is_enabled TINYINT(1) NOT NULL DEFAULT 1, threshold_type VARCHAR(32) NOT NULL, threshold_value DECIMAL(19,6) NULL, config_json LONGTEXT NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), UNIQUE KEY uq_approval_rule(business_id,rule_key), CONSTRAINT fk_approval_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_approval_action_perm FOREIGN KEY(action_permission_id) REFERENCES permissions(id) ON DELETE SET NULL, CONSTRAINT fk_approval_approver_perm FOREIGN KEY(approver_permission_id) REFERENCES permissions(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE subscription_features ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, feature_key VARCHAR(96) NOT NULL UNIQUE, name VARCHAR(160) NOT NULL, value_type VARCHAR(24) NOT NULL DEFAULT 'BOOLEAN', description VARCHAR(500) NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE subscription_plans ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, plan_key VARCHAR(64) NOT NULL UNIQUE, name VARCHAR(120) NOT NULL, description VARCHAR(500) NULL, sort_order INT NOT NULL DEFAULT 100, is_active TINYINT(1) NOT NULL DEFAULT 1, is_public TINYINT(1) NOT NULL DEFAULT 1, trial_days SMALLINT UNSIGNED NOT NULL DEFAULT 14, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE subscription_plan_features ( plan_id BIGINT UNSIGNED NOT NULL, feature_id BIGINT UNSIGNED NOT NULL, feature_value VARCHAR(191) NOT NULL, PRIMARY KEY(plan_id,feature_id), CONSTRAINT fk_plan_feature_plan FOREIGN KEY(plan_id) REFERENCES subscription_plans(id) ON DELETE CASCADE, CONSTRAINT fk_plan_feature_feature FOREIGN KEY(feature_id) REFERENCES subscription_features(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE subscription_plan_prices ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, plan_id BIGINT UNSIGNED NOT NULL, billing_cycle VARCHAR(16) NOT NULL, currency_code CHAR(3) NOT NULL, country_code CHAR(2) NULL, amount DECIMAL(19,6) NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, starts_at DATETIME(6) NULL, ends_at DATETIME(6) NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), KEY idx_plan_price_lookup(plan_id,billing_cycle,currency_code,country_code,is_active), CONSTRAINT fk_plan_price_plan FOREIGN KEY(plan_id) REFERENCES subscription_plans(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE business_subscriptions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, business_id BIGINT UNSIGNED NOT NULL, plan_id BIGINT UNSIGNED NOT NULL, billing_cycle VARCHAR(16) NOT NULL, status VARCHAR(24) NOT NULL, starts_at DATETIME(6) NOT NULL, current_period_start DATETIME(6) NOT NULL, current_period_end DATETIME(6) NOT NULL, trial_ends_at DATETIME(6) NULL, grace_ends_at DATETIME(6) NULL, cancel_at_period_end TINYINT(1) NOT NULL DEFAULT 0, canceled_at DATETIME(6) NULL, ended_at DATETIME(6) NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), KEY idx_subscription_business_status(business_id,status), CONSTRAINT fk_subscription_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_subscription_plan FOREIGN KEY(plan_id) REFERENCES subscription_plans(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE subscription_invoices ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, invoice_no VARCHAR(48) NOT NULL UNIQUE, business_id BIGINT UNSIGNED NOT NULL, subscription_id BIGINT UNSIGNED NULL, status VARCHAR(24) NOT NULL DEFAULT 'DRAFT', currency_code CHAR(3) NOT NULL, subtotal DECIMAL(19,6) NOT NULL DEFAULT 0, discount_amount DECIMAL(19,6) NOT NULL DEFAULT 0, tax_amount DECIMAL(19,6) NOT NULL DEFAULT 0, total_amount DECIMAL(19,6) NOT NULL DEFAULT 0, paid_amount DECIMAL(19,6) NOT NULL DEFAULT 0, refunded_amount DECIMAL(19,6) NOT NULL DEFAULT 0, issued_at DATETIME(6) NULL, due_at DATETIME(6) NULL, paid_at DATETIME(6) NULL, voided_at DATETIME(6) NULL, metadata_json LONGTEXT NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), KEY idx_sub_invoice_business(business_id,status,due_at), CONSTRAINT fk_sub_invoice_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_sub_invoice_subscription FOREIGN KEY(subscription_id) REFERENCES business_subscriptions(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE subscription_invoice_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, invoice_id BIGINT UNSIGNED NOT NULL, line_no INT UNSIGNED NOT NULL, item_type VARCHAR(32) NOT NULL, description VARCHAR(255) NOT NULL, quantity DECIMAL(18,4) NOT NULL DEFAULT 1, unit_price DECIMAL(19,6) NOT NULL DEFAULT 0, line_total DECIMAL(19,6) NOT NULL DEFAULT 0, metadata_json LONGTEXT NULL, UNIQUE KEY uq_sub_invoice_line(invoice_id,line_no), CONSTRAINT fk_sub_invoice_item_invoice FOREIGN KEY(invoice_id) REFERENCES subscription_invoices(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE payment_gateways ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, gateway_key VARCHAR(48) NOT NULL UNIQUE, name VARCHAR(120) NOT NULL, driver_class VARCHAR(191) NOT NULL, environment VARCHAR(16) NOT NULL DEFAULT 'SANDBOX', is_enabled TINYINT(1) NOT NULL DEFAULT 0, sort_order INT NOT NULL DEFAULT 100, capabilities_json LONGTEXT NULL, encrypted_config LONGTEXT NULL, encrypted_webhook_secret LONGTEXT NULL, allowed_currencies VARCHAR(500) NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE payment_attempts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, invoice_id BIGINT UNSIGNED NOT NULL, gateway_id BIGINT UNSIGNED NOT NULL, attempt_no SMALLINT UNSIGNED NOT NULL, idempotency_key VARCHAR(191) NOT NULL UNIQUE, amount DECIMAL(19,6) NOT NULL, currency_code CHAR(3) NOT NULL, status VARCHAR(32) NOT NULL DEFAULT 'PENDING', gateway_request_no VARCHAR(191) NULL, checkout_url TEXT NULL, expires_at DATETIME(6) NULL, failure_code VARCHAR(96) NULL, failure_message VARCHAR(500) NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), UNIQUE KEY uq_payment_attempt(invoice_id,attempt_no), KEY idx_gateway_request(gateway_id,gateway_request_no), CONSTRAINT fk_payment_attempt_invoice FOREIGN KEY(invoice_id) REFERENCES subscription_invoices(id) ON DELETE CASCADE, CONSTRAINT fk_payment_attempt_gateway FOREIGN KEY(gateway_id) REFERENCES payment_gateways(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE payment_transactions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, payment_attempt_id BIGINT UNSIGNED NOT NULL, gateway_transaction_id VARCHAR(191) NULL, transaction_type VARCHAR(32) NOT NULL DEFAULT 'PAYMENT', status VARCHAR(32) NOT NULL, gross_amount DECIMAL(19,6) NOT NULL DEFAULT 0, approved_amount DECIMAL(19,6) NOT NULL DEFAULT 0, refunded_amount DECIMAL(19,6) NOT NULL DEFAULT 0, currency_code CHAR(3) NOT NULL, gateway_status VARCHAR(64) NULL, paid_at DATETIME(6) NULL, raw_summary_json LONGTEXT NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), KEY idx_payment_tx_attempt(payment_attempt_id), KEY idx_gateway_tx(gateway_transaction_id), CONSTRAINT fk_payment_tx_attempt FOREIGN KEY(payment_attempt_id) REFERENCES payment_attempts(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE payment_gateway_events ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, gateway_id BIGINT UNSIGNED NOT NULL, gateway_event_id VARCHAR(191) NOT NULL, event_type VARCHAR(96) NOT NULL, signature_valid TINYINT(1) NOT NULL DEFAULT 0, processing_status VARCHAR(24) NOT NULL DEFAULT 'RECEIVED', raw_payload LONGTEXT NOT NULL, processing_error TEXT NULL, received_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), processed_at DATETIME(6) NULL, UNIQUE KEY uq_gateway_event(gateway_id,gateway_event_id), KEY idx_gateway_event_status(processing_status,received_at), CONSTRAINT fk_gateway_event_gateway FOREIGN KEY(gateway_id) REFERENCES payment_gateways(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE payment_refunds ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, payment_transaction_id BIGINT UNSIGNED NOT NULL, gateway_refund_id VARCHAR(191) NULL, amount DECIMAL(19,6) NOT NULL, currency_code CHAR(3) NOT NULL, status VARCHAR(32) NOT NULL, reason VARCHAR(500) NULL, initiated_by_user_id BIGINT UNSIGNED NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), processed_at DATETIME(6) NULL, CONSTRAINT fk_payment_refund_tx FOREIGN KEY(payment_transaction_id) REFERENCES payment_transactions(id) ON DELETE CASCADE, CONSTRAINT fk_payment_refund_user FOREIGN KEY(initiated_by_user_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE coupons ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(64) NOT NULL UNIQUE, discount_type VARCHAR(16) NOT NULL, discount_value DECIMAL(19,6) NOT NULL, currency_code CHAR(3) NULL, max_redemptions INT UNSIGNED NULL, max_redemptions_per_business INT UNSIGNED NULL, starts_at DATETIME(6) NULL, ends_at DATETIME(6) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE coupon_redemptions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, coupon_id BIGINT UNSIGNED NOT NULL, business_id BIGINT UNSIGNED NOT NULL, invoice_id BIGINT UNSIGNED NOT NULL, discount_amount DECIMAL(19,6) NOT NULL, redeemed_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), KEY idx_coupon_redemption_coupon(coupon_id), CONSTRAINT fk_coupon_redemption_coupon FOREIGN KEY(coupon_id) REFERENCES coupons(id), CONSTRAINT fk_coupon_redemption_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_coupon_redemption_invoice FOREIGN KEY(invoice_id) REFERENCES subscription_invoices(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE subscription_change_logs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, subscription_id BIGINT UNSIGNED NOT NULL, old_plan_id BIGINT UNSIGNED NULL, new_plan_id BIGINT UNSIGNED NULL, old_status VARCHAR(24) NULL, new_status VARCHAR(24) NULL, reason VARCHAR(500) NULL, actor_user_id BIGINT UNSIGNED NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), KEY idx_sub_change(subscription_id,created_at), CONSTRAINT fk_sub_change_subscription FOREIGN KEY(subscription_id) REFERENCES business_subscriptions(id) ON DELETE CASCADE, CONSTRAINT fk_sub_change_old_plan FOREIGN KEY(old_plan_id) REFERENCES subscription_plans(id) ON DELETE SET NULL, CONSTRAINT fk_sub_change_new_plan FOREIGN KEY(new_plan_id) REFERENCES subscription_plans(id) ON DELETE SET NULL, CONSTRAINT fk_sub_change_actor FOREIGN KEY(actor_user_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE business_settings ( business_id BIGINT UNSIGNED NOT NULL, setting_key VARCHAR(96) NOT NULL, setting_value LONGTEXT NULL, updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), PRIMARY KEY(business_id,setting_key), CONSTRAINT fk_business_setting_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE document_sequences ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, business_id BIGINT UNSIGNED NOT NULL, scope_type VARCHAR(16) NOT NULL DEFAULT 'BUSINESS', scope_id BIGINT UNSIGNED NOT NULL DEFAULT 0, document_type VARCHAR(48) NOT NULL, period_key VARCHAR(16) NOT NULL DEFAULT '', prefix VARCHAR(32) NOT NULL, current_value BIGINT UNSIGNED NOT NULL DEFAULT 0, padding TINYINT UNSIGNED NOT NULL DEFAULT 6, updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), UNIQUE KEY uq_document_sequence(business_id,scope_type,scope_id,document_type,period_key), CONSTRAINT fk_document_sequence_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE units ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, business_id BIGINT UNSIGNED NOT NULL, unit_code VARCHAR(24) NOT NULL, name VARCHAR(96) NOT NULL, decimal_places TINYINT UNSIGNED NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), UNIQUE KEY uq_unit_code(business_id,unit_code), CONSTRAINT fk_unit_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE categories ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, business_id BIGINT UNSIGNED NOT NULL, parent_id BIGINT UNSIGNED NULL, category_code VARCHAR(32) NULL, name VARCHAR(120) NOT NULL, sort_order INT NOT NULL DEFAULT 100, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), deleted_at DATETIME(6) NULL, UNIQUE KEY uq_category_code(business_id,category_code), KEY idx_category_name(business_id,name), CONSTRAINT fk_category_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_category_parent FOREIGN KEY(parent_id) REFERENCES categories(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE tax_rates ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, business_id BIGINT UNSIGNED NOT NULL, tax_code VARCHAR(32) NOT NULL, name VARCHAR(96) NOT NULL, rate_percent DECIMAL(9,6) NOT NULL DEFAULT 0, is_inclusive TINYINT(1) NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), UNIQUE KEY uq_tax_code(business_id,tax_code), CONSTRAINT fk_tax_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE payment_methods ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, business_id BIGINT UNSIGNED NOT NULL, method_key VARCHAR(48) NOT NULL, name VARCHAR(96) NOT NULL, method_type VARCHAR(32) NOT NULL, is_cash TINYINT(1) NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 100, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), UNIQUE KEY uq_payment_method(business_id,method_key), CONSTRAINT fk_payment_method_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE customers ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, business_id BIGINT UNSIGNED NOT NULL, customer_code VARCHAR(32) NOT NULL, name VARCHAR(191) NOT NULL, phone VARCHAR(40) NULL, email VARCHAR(191) NULL, tax_no VARCHAR(80) NULL, address TEXT NULL, credit_limit DECIMAL(19,6) NOT NULL DEFAULT 0, opening_balance DECIMAL(19,6) NOT NULL DEFAULT 0, notes TEXT NULL, status VARCHAR(24) NOT NULL DEFAULT 'ACTIVE', version_no BIGINT UNSIGNED NOT NULL DEFAULT 1, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), deleted_at DATETIME(6) NULL, UNIQUE KEY uq_customer_code(business_id,customer_code), KEY idx_customer_search(business_id,name,phone), CONSTRAINT fk_customer_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE suppliers ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, business_id BIGINT UNSIGNED NOT NULL, supplier_code VARCHAR(32) NOT NULL, name VARCHAR(191) NOT NULL, contact_person VARCHAR(160) NULL, phone VARCHAR(40) NULL, email VARCHAR(191) NULL, tax_no VARCHAR(80) NULL, address TEXT NULL, opening_balance DECIMAL(19,6) NOT NULL DEFAULT 0, notes TEXT NULL, status VARCHAR(24) NOT NULL DEFAULT 'ACTIVE', version_no BIGINT UNSIGNED NOT NULL DEFAULT 1, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), deleted_at DATETIME(6) NULL, UNIQUE KEY uq_supplier_code(business_id,supplier_code), KEY idx_supplier_search(business_id,name,phone), CONSTRAINT fk_supplier_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE products ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, business_id BIGINT UNSIGNED NOT NULL, category_id BIGINT UNSIGNED NULL, unit_id BIGINT UNSIGNED NOT NULL, tax_rate_id BIGINT UNSIGNED NULL, product_code VARCHAR(48) NOT NULL, sku VARCHAR(96) NULL, name VARCHAR(191) NOT NULL, description TEXT NULL, product_type VARCHAR(24) NOT NULL DEFAULT 'STOCK', default_purchase_price DECIMAL(19,6) NOT NULL DEFAULT 0, default_sale_price DECIMAL(19,6) NOT NULL DEFAULT 0, reorder_level DECIMAL(18,4) NOT NULL DEFAULT 0, track_stock TINYINT(1) NOT NULL DEFAULT 1, track_batch TINYINT(1) NOT NULL DEFAULT 0, track_expiry TINYINT(1) NOT NULL DEFAULT 0, allow_negative_stock TINYINT(1) NOT NULL DEFAULT 0, image_path VARCHAR(500) NULL, status VARCHAR(24) NOT NULL DEFAULT 'ACTIVE', version_no BIGINT UNSIGNED NOT NULL DEFAULT 1, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), deleted_at DATETIME(6) NULL, UNIQUE KEY uq_product_code(business_id,product_code), UNIQUE KEY uq_product_sku(business_id,sku), KEY idx_product_search(business_id,name,status), CONSTRAINT fk_product_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_product_category FOREIGN KEY(category_id) REFERENCES categories(id) ON DELETE SET NULL, CONSTRAINT fk_product_unit FOREIGN KEY(unit_id) REFERENCES units(id), CONSTRAINT fk_product_tax FOREIGN KEY(tax_rate_id) REFERENCES tax_rates(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE product_barcodes ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, business_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NOT NULL, barcode VARCHAR(191) NOT NULL, barcode_type VARCHAR(32) NULL, is_primary TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), UNIQUE KEY uq_barcode(business_id,barcode), CONSTRAINT fk_barcode_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_barcode_product FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE product_outlet_prices ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, business_id BIGINT UNSIGNED NOT NULL, outlet_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NOT NULL, sale_price DECIMAL(19,6) NOT NULL, purchase_price DECIMAL(19,6) NULL, updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), UNIQUE KEY uq_outlet_product_price(outlet_id,product_id), CONSTRAINT fk_outlet_price_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_outlet_price_outlet FOREIGN KEY(outlet_id) REFERENCES outlets(id) ON DELETE CASCADE, CONSTRAINT fk_outlet_price_product FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE inventory_locations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, business_id BIGINT UNSIGNED NOT NULL, outlet_id BIGINT UNSIGNED NOT NULL, location_code VARCHAR(32) NOT NULL, name VARCHAR(120) NOT NULL, location_type VARCHAR(32) NOT NULL DEFAULT 'STORE', is_default TINYINT(1) NOT NULL DEFAULT 0, status VARCHAR(24) NOT NULL DEFAULT 'ACTIVE', created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), UNIQUE KEY uq_inventory_location_code(business_id,location_code), KEY idx_inventory_location_outlet(outlet_id,status), CONSTRAINT fk_inventory_location_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_inventory_location_outlet FOREIGN KEY(outlet_id) REFERENCES outlets(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE product_batches ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, business_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NOT NULL, supplier_id BIGINT UNSIGNED NULL, batch_no VARCHAR(96) NOT NULL, production_date DATE NULL, expiration_date DATE NULL, status VARCHAR(24) NOT NULL DEFAULT 'ACTIVE', notes VARCHAR(500) NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), UNIQUE KEY uq_batch(business_id,product_id,batch_no), KEY idx_batch_expiry(business_id,expiration_date,status), CONSTRAINT fk_batch_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_batch_product FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE, CONSTRAINT fk_batch_supplier FOREIGN KEY(supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE stock_balances ( business_id BIGINT UNSIGNED NOT NULL, location_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NOT NULL, quantity_on_hand DECIMAL(18,4) NOT NULL DEFAULT 0, quantity_reserved DECIMAL(18,4) NOT NULL DEFAULT 0, updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), PRIMARY KEY(location_id,product_id), KEY idx_stock_balance_product(business_id,product_id), CONSTRAINT fk_stock_balance_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_stock_balance_location FOREIGN KEY(location_id) REFERENCES inventory_locations(id) ON DELETE CASCADE, CONSTRAINT fk_stock_balance_product FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE batch_stock_balances ( business_id BIGINT UNSIGNED NOT NULL, location_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NOT NULL, batch_id BIGINT UNSIGNED NOT NULL, quantity_on_hand DECIMAL(18,4) NOT NULL DEFAULT 0, quantity_reserved DECIMAL(18,4) NOT NULL DEFAULT 0, updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), PRIMARY KEY(location_id,batch_id), KEY idx_batch_stock_product(business_id,product_id,batch_id), CONSTRAINT fk_batch_stock_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_batch_stock_location FOREIGN KEY(location_id) REFERENCES inventory_locations(id) ON DELETE CASCADE, CONSTRAINT fk_batch_stock_product FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE, CONSTRAINT fk_batch_stock_batch FOREIGN KEY(batch_id) REFERENCES product_batches(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE inventory_movements ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, business_id BIGINT UNSIGNED NOT NULL, outlet_id BIGINT UNSIGNED NOT NULL, location_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NOT NULL, batch_id BIGINT UNSIGNED NULL, movement_type VARCHAR(48) NOT NULL, direction VARCHAR(8) NOT NULL, quantity DECIMAL(18,4) NOT NULL, unit_cost DECIMAL(19,6) NULL, source_type VARCHAR(48) NOT NULL, source_public_id CHAR(26) NULL, reference_no VARCHAR(96) NULL, notes VARCHAR(500) NULL, occurred_at DATETIME(6) NOT NULL, created_by_business_user_id BIGINT UNSIGNED NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), KEY idx_inventory_move_product(business_id,product_id,occurred_at), KEY idx_inventory_move_location(location_id,occurred_at), KEY idx_inventory_move_source(source_type,source_public_id), CONSTRAINT fk_inventory_move_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_inventory_move_outlet FOREIGN KEY(outlet_id) REFERENCES outlets(id), CONSTRAINT fk_inventory_move_location FOREIGN KEY(location_id) REFERENCES inventory_locations(id), CONSTRAINT fk_inventory_move_product FOREIGN KEY(product_id) REFERENCES products(id), CONSTRAINT fk_inventory_move_batch FOREIGN KEY(batch_id) REFERENCES product_batches(id) ON DELETE SET NULL, CONSTRAINT fk_inventory_move_user FOREIGN KEY(created_by_business_user_id) REFERENCES business_users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE stock_transfers ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, transfer_no VARCHAR(64) NOT NULL, business_id BIGINT UNSIGNED NOT NULL, source_location_id BIGINT UNSIGNED NOT NULL, destination_location_id BIGINT UNSIGNED NOT NULL, status VARCHAR(24) NOT NULL DEFAULT 'DRAFT', requested_at DATETIME(6) NULL, dispatched_at DATETIME(6) NULL, received_at DATETIME(6) NULL, created_by_business_user_id BIGINT UNSIGNED NOT NULL, received_by_business_user_id BIGINT UNSIGNED NULL, notes TEXT NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), UNIQUE KEY uq_stock_transfer_no(business_id,transfer_no), CONSTRAINT fk_stock_transfer_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_stock_transfer_source FOREIGN KEY(source_location_id) REFERENCES inventory_locations(id), CONSTRAINT fk_stock_transfer_destination FOREIGN KEY(destination_location_id) REFERENCES inventory_locations(id), CONSTRAINT fk_stock_transfer_creator FOREIGN KEY(created_by_business_user_id) REFERENCES business_users(id), CONSTRAINT fk_stock_transfer_receiver FOREIGN KEY(received_by_business_user_id) REFERENCES business_users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE stock_transfer_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, transfer_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NOT NULL, batch_id BIGINT UNSIGNED NULL, requested_qty DECIMAL(18,4) NOT NULL, dispatched_qty DECIMAL(18,4) NOT NULL DEFAULT 0, received_qty DECIMAL(18,4) NOT NULL DEFAULT 0, KEY idx_stock_transfer_item(transfer_id,product_id), CONSTRAINT fk_transfer_item_transfer FOREIGN KEY(transfer_id) REFERENCES stock_transfers(id) ON DELETE CASCADE, CONSTRAINT fk_transfer_item_product FOREIGN KEY(product_id) REFERENCES products(id), CONSTRAINT fk_transfer_item_batch FOREIGN KEY(batch_id) REFERENCES product_batches(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE stock_adjustments ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, adjustment_no VARCHAR(64) NOT NULL, business_id BIGINT UNSIGNED NOT NULL, location_id BIGINT UNSIGNED NOT NULL, status VARCHAR(24) NOT NULL DEFAULT 'DRAFT', reason VARCHAR(191) NOT NULL, submitted_at DATETIME(6) NULL, approved_at DATETIME(6) NULL, posted_at DATETIME(6) NULL, created_by_business_user_id BIGINT UNSIGNED NOT NULL, approved_by_business_user_id BIGINT UNSIGNED NULL, notes TEXT NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), UNIQUE KEY uq_stock_adjustment_no(business_id,adjustment_no), CONSTRAINT fk_adjustment_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_adjustment_location FOREIGN KEY(location_id) REFERENCES inventory_locations(id), CONSTRAINT fk_adjustment_creator FOREIGN KEY(created_by_business_user_id) REFERENCES business_users(id), CONSTRAINT fk_adjustment_approver FOREIGN KEY(approved_by_business_user_id) REFERENCES business_users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE stock_adjustment_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, adjustment_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NOT NULL, batch_id BIGINT UNSIGNED NULL, quantity_delta DECIMAL(18,4) NOT NULL, unit_cost DECIMAL(19,6) NULL, CONSTRAINT fk_adjustment_item_parent FOREIGN KEY(adjustment_id) REFERENCES stock_adjustments(id) ON DELETE CASCADE, CONSTRAINT fk_adjustment_item_product FOREIGN KEY(product_id) REFERENCES products(id), CONSTRAINT fk_adjustment_item_batch FOREIGN KEY(batch_id) REFERENCES product_batches(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE stock_take_sessions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, stock_take_no VARCHAR(64) NOT NULL, business_id BIGINT UNSIGNED NOT NULL, location_id BIGINT UNSIGNED NOT NULL, status VARCHAR(24) NOT NULL DEFAULT 'DRAFT', freeze_snapshot_at DATETIME(6) NULL, submitted_at DATETIME(6) NULL, approved_at DATETIME(6) NULL, posted_at DATETIME(6) NULL, created_by_business_user_id BIGINT UNSIGNED NOT NULL, approved_by_business_user_id BIGINT UNSIGNED NULL, notes TEXT NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), UNIQUE KEY uq_stock_take_no(business_id,stock_take_no), KEY idx_stock_take_status(business_id,status,created_at), CONSTRAINT fk_stock_take_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_stock_take_location FOREIGN KEY(location_id) REFERENCES inventory_locations(id), CONSTRAINT fk_stock_take_creator FOREIGN KEY(created_by_business_user_id) REFERENCES business_users(id), CONSTRAINT fk_stock_take_approver FOREIGN KEY(approved_by_business_user_id) REFERENCES business_users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE stock_take_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, stock_take_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NOT NULL, batch_id BIGINT UNSIGNED NULL, system_qty DECIMAL(18,4) NOT NULL DEFAULT 0, counted_qty DECIMAL(18,4) NULL, variance_qty DECIMAL(18,4) NULL, counted_by_business_user_id BIGINT UNSIGNED NULL, counted_at DATETIME(6) NULL, notes VARCHAR(500) NULL, KEY idx_stock_take_item(stock_take_id,product_id), CONSTRAINT fk_stock_take_item_parent FOREIGN KEY(stock_take_id) REFERENCES stock_take_sessions(id) ON DELETE CASCADE, CONSTRAINT fk_stock_take_item_product FOREIGN KEY(product_id) REFERENCES products(id), CONSTRAINT fk_stock_take_item_batch FOREIGN KEY(batch_id) REFERENCES product_batches(id) ON DELETE SET NULL, CONSTRAINT fk_stock_take_item_counter FOREIGN KEY(counted_by_business_user_id) REFERENCES business_users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE financial_accounts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, business_id BIGINT UNSIGNED NOT NULL, outlet_id BIGINT UNSIGNED NULL, account_code VARCHAR(32) NOT NULL, name VARCHAR(120) NOT NULL, account_type VARCHAR(32) NOT NULL, currency_code CHAR(3) NOT NULL, opening_balance DECIMAL(19,6) NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), UNIQUE KEY uq_financial_account_code(business_id,account_code), CONSTRAINT fk_financial_account_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_financial_account_outlet FOREIGN KEY(outlet_id) REFERENCES outlets(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE financial_account_entries ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, business_id BIGINT UNSIGNED NOT NULL, account_id BIGINT UNSIGNED NOT NULL, entry_type VARCHAR(24) NOT NULL, amount DECIMAL(19,6) NOT NULL, source_type VARCHAR(48) NOT NULL, source_public_id CHAR(26) NULL, reference_no VARCHAR(96) NULL, description VARCHAR(500) NULL, occurred_at DATETIME(6) NOT NULL, created_by_business_user_id BIGINT UNSIGNED NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), KEY idx_financial_entry_account(account_id,occurred_at), KEY idx_financial_entry_source(source_type,source_public_id), CONSTRAINT fk_financial_entry_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_financial_entry_account FOREIGN KEY(account_id) REFERENCES financial_accounts(id), CONSTRAINT fk_financial_entry_user FOREIGN KEY(created_by_business_user_id) REFERENCES business_users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE cash_registers ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, business_id BIGINT UNSIGNED NOT NULL, outlet_id BIGINT UNSIGNED NOT NULL, register_code VARCHAR(32) NOT NULL, name VARCHAR(96) NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), UNIQUE KEY uq_register_code(business_id,register_code), CONSTRAINT fk_register_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_register_outlet FOREIGN KEY(outlet_id) REFERENCES outlets(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE cash_register_sessions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, business_id BIGINT UNSIGNED NOT NULL, outlet_id BIGINT UNSIGNED NOT NULL, register_id BIGINT UNSIGNED NOT NULL, opened_by_business_user_id BIGINT UNSIGNED NOT NULL, closed_by_business_user_id BIGINT UNSIGNED NULL, status VARCHAR(16) NOT NULL DEFAULT 'OPEN', opening_float DECIMAL(19,6) NOT NULL DEFAULT 0, expected_cash DECIMAL(19,6) NULL, counted_cash DECIMAL(19,6) NULL, variance_amount DECIMAL(19,6) NULL, opened_at DATETIME(6) NOT NULL, closed_at DATETIME(6) NULL, notes VARCHAR(500) NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), KEY idx_cash_session_register(register_id,status), KEY idx_cash_session_user(opened_by_business_user_id,status), CONSTRAINT fk_cash_session_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_cash_session_outlet FOREIGN KEY(outlet_id) REFERENCES outlets(id), CONSTRAINT fk_cash_session_register FOREIGN KEY(register_id) REFERENCES cash_registers(id), CONSTRAINT fk_cash_session_opener FOREIGN KEY(opened_by_business_user_id) REFERENCES business_users(id), CONSTRAINT fk_cash_session_closer FOREIGN KEY(closed_by_business_user_id) REFERENCES business_users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE cash_register_movements ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, business_id BIGINT UNSIGNED NOT NULL, session_id BIGINT UNSIGNED NOT NULL, direction VARCHAR(8) NOT NULL, movement_type VARCHAR(48) NOT NULL, amount DECIMAL(19,6) NOT NULL, source_type VARCHAR(48) NULL, source_public_id CHAR(26) NULL, notes VARCHAR(500) NULL, occurred_at DATETIME(6) NOT NULL, created_by_business_user_id BIGINT UNSIGNED NOT NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), KEY idx_cash_movement_session(session_id,occurred_at), CONSTRAINT fk_cash_move_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_cash_move_session FOREIGN KEY(session_id) REFERENCES cash_register_sessions(id), CONSTRAINT fk_cash_move_user FOREIGN KEY(created_by_business_user_id) REFERENCES business_users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE cash_register_audits ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, business_id BIGINT UNSIGNED NOT NULL, session_id BIGINT UNSIGNED NOT NULL, action_key VARCHAR(48) NOT NULL, old_values_json LONGTEXT NULL, new_values_json LONGTEXT NULL, actor_business_user_id BIGINT UNSIGNED NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), KEY idx_cash_audit_session(session_id,created_at), CONSTRAINT fk_cash_audit_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_cash_audit_session FOREIGN KEY(session_id) REFERENCES cash_register_sessions(id) ON DELETE CASCADE, CONSTRAINT fk_cash_audit_actor FOREIGN KEY(actor_business_user_id) REFERENCES business_users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE sales ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, business_id BIGINT UNSIGNED NOT NULL, outlet_id BIGINT UNSIGNED NOT NULL, sale_no VARCHAR(64) NOT NULL, sale_type VARCHAR(24) NOT NULL DEFAULT 'SALE', status VARCHAR(24) NOT NULL DEFAULT 'POSTED', customer_id BIGINT UNSIGNED NULL, cash_session_id BIGINT UNSIGNED NULL, currency_code CHAR(3) NOT NULL, subtotal DECIMAL(19,6) NOT NULL DEFAULT 0, discount_type VARCHAR(16) NULL, discount_value DECIMAL(19,6) NOT NULL DEFAULT 0, discount_amount DECIMAL(19,6) NOT NULL DEFAULT 0, tax_amount DECIMAL(19,6) NOT NULL DEFAULT 0, total_amount DECIMAL(19,6) NOT NULL DEFAULT 0, paid_amount DECIMAL(19,6) NOT NULL DEFAULT 0, due_amount DECIMAL(19,6) NOT NULL DEFAULT 0, change_amount DECIMAL(19,6) NOT NULL DEFAULT 0, payment_status VARCHAR(24) NOT NULL DEFAULT 'UNPAID', notes TEXT NULL, sold_at DATETIME(6) NOT NULL, created_by_business_user_id BIGINT UNSIGNED NOT NULL, voided_by_business_user_id BIGINT UNSIGNED NULL, voided_at DATETIME(6) NULL, void_reason VARCHAR(500) NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), UNIQUE KEY uq_sale_no(business_id,sale_no), KEY idx_sale_outlet_date(outlet_id,sold_at), KEY idx_sale_customer_date(customer_id,sold_at), KEY idx_sale_status(business_id,status,sold_at), CONSTRAINT fk_sale_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_sale_outlet FOREIGN KEY(outlet_id) REFERENCES outlets(id), CONSTRAINT fk_sale_customer FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE SET NULL, CONSTRAINT fk_sale_cash_session FOREIGN KEY(cash_session_id) REFERENCES cash_register_sessions(id) ON DELETE SET NULL, CONSTRAINT fk_sale_creator FOREIGN KEY(created_by_business_user_id) REFERENCES business_users(id), CONSTRAINT fk_sale_voider FOREIGN KEY(voided_by_business_user_id) REFERENCES business_users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE sale_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, sale_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NOT NULL, product_name_snapshot VARCHAR(191) NOT NULL, sku_snapshot VARCHAR(96) NULL, quantity DECIMAL(18,4) NOT NULL, unit_price DECIMAL(19,6) NOT NULL, discount_type VARCHAR(16) NULL, discount_value DECIMAL(19,6) NOT NULL DEFAULT 0, discount_amount DECIMAL(19,6) NOT NULL DEFAULT 0, tax_rate_percent DECIMAL(9,6) NOT NULL DEFAULT 0, tax_amount DECIMAL(19,6) NOT NULL DEFAULT 0, line_total DECIMAL(19,6) NOT NULL, cogs_amount DECIMAL(19,6) NOT NULL DEFAULT 0, KEY idx_sale_item_sale(sale_id), KEY idx_sale_item_product(product_id), CONSTRAINT fk_sale_item_sale FOREIGN KEY(sale_id) REFERENCES sales(id) ON DELETE CASCADE, CONSTRAINT fk_sale_item_product FOREIGN KEY(product_id) REFERENCES products(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE sale_item_batches ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, sale_item_id BIGINT UNSIGNED NOT NULL, batch_id BIGINT UNSIGNED NOT NULL, location_id BIGINT UNSIGNED NOT NULL, quantity DECIMAL(18,4) NOT NULL, UNIQUE KEY uq_sale_item_batch(sale_item_id,batch_id,location_id), CONSTRAINT fk_sale_batch_item FOREIGN KEY(sale_item_id) REFERENCES sale_items(id) ON DELETE CASCADE, CONSTRAINT fk_sale_batch_batch FOREIGN KEY(batch_id) REFERENCES product_batches(id), CONSTRAINT fk_sale_batch_location FOREIGN KEY(location_id) REFERENCES inventory_locations(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE sale_payments ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, business_id BIGINT UNSIGNED NOT NULL, sale_id BIGINT UNSIGNED NOT NULL, payment_method_id BIGINT UNSIGNED NOT NULL, financial_account_id BIGINT UNSIGNED NULL, cash_session_id BIGINT UNSIGNED NULL, amount DECIMAL(19,6) NOT NULL, reference_no VARCHAR(191) NULL, status VARCHAR(24) NOT NULL DEFAULT 'POSTED', paid_at DATETIME(6) NOT NULL, received_by_business_user_id BIGINT UNSIGNED NOT NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), KEY idx_sale_payment_sale(sale_id,paid_at), CONSTRAINT fk_sale_payment_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_sale_payment_sale FOREIGN KEY(sale_id) REFERENCES sales(id) ON DELETE CASCADE, CONSTRAINT fk_sale_payment_method FOREIGN KEY(payment_method_id) REFERENCES payment_methods(id), CONSTRAINT fk_sale_payment_account FOREIGN KEY(financial_account_id) REFERENCES financial_accounts(id) ON DELETE SET NULL, CONSTRAINT fk_sale_payment_cash_session FOREIGN KEY(cash_session_id) REFERENCES cash_register_sessions(id) ON DELETE SET NULL, CONSTRAINT fk_sale_payment_receiver FOREIGN KEY(received_by_business_user_id) REFERENCES business_users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE sales_returns ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, return_no VARCHAR(64) NOT NULL, business_id BIGINT UNSIGNED NOT NULL, outlet_id BIGINT UNSIGNED NOT NULL, sale_id BIGINT UNSIGNED NULL, customer_id BIGINT UNSIGNED NULL, status VARCHAR(24) NOT NULL DEFAULT 'POSTED', total_amount DECIMAL(19,6) NOT NULL DEFAULT 0, refund_amount DECIMAL(19,6) NOT NULL DEFAULT 0, reason VARCHAR(500) NULL, returned_at DATETIME(6) NOT NULL, created_by_business_user_id BIGINT UNSIGNED NOT NULL, approved_by_business_user_id BIGINT UNSIGNED NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), UNIQUE KEY uq_return_no(business_id,return_no), KEY idx_return_sale(sale_id), CONSTRAINT fk_return_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_return_outlet FOREIGN KEY(outlet_id) REFERENCES outlets(id), CONSTRAINT fk_return_sale FOREIGN KEY(sale_id) REFERENCES sales(id) ON DELETE SET NULL, CONSTRAINT fk_return_customer FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE SET NULL, CONSTRAINT fk_return_creator FOREIGN KEY(created_by_business_user_id) REFERENCES business_users(id), CONSTRAINT fk_return_approver FOREIGN KEY(approved_by_business_user_id) REFERENCES business_users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE sales_return_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, sales_return_id BIGINT UNSIGNED NOT NULL, sale_item_id BIGINT UNSIGNED NULL, product_id BIGINT UNSIGNED NOT NULL, batch_id BIGINT UNSIGNED NULL, location_id BIGINT UNSIGNED NOT NULL, quantity DECIMAL(18,4) NOT NULL, unit_price DECIMAL(19,6) NOT NULL, line_total DECIMAL(19,6) NOT NULL, restock TINYINT(1) NOT NULL DEFAULT 1, CONSTRAINT fk_return_item_return FOREIGN KEY(sales_return_id) REFERENCES sales_returns(id) ON DELETE CASCADE, CONSTRAINT fk_return_item_sale_item FOREIGN KEY(sale_item_id) REFERENCES sale_items(id) ON DELETE SET NULL, CONSTRAINT fk_return_item_product FOREIGN KEY(product_id) REFERENCES products(id), CONSTRAINT fk_return_item_batch FOREIGN KEY(batch_id) REFERENCES product_batches(id) ON DELETE SET NULL, CONSTRAINT fk_return_item_location FOREIGN KEY(location_id) REFERENCES inventory_locations(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE customer_payments ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, business_id BIGINT UNSIGNED NOT NULL, outlet_id BIGINT UNSIGNED NOT NULL, customer_id BIGINT UNSIGNED NOT NULL, sale_id BIGINT UNSIGNED NULL, payment_method_id BIGINT UNSIGNED NOT NULL, financial_account_id BIGINT UNSIGNED NULL, cash_session_id BIGINT UNSIGNED NULL, amount DECIMAL(19,6) NOT NULL, payment_type VARCHAR(32) NOT NULL DEFAULT 'DUE_COLLECTION', reference_no VARCHAR(191) NULL, paid_at DATETIME(6) NOT NULL, received_by_business_user_id BIGINT UNSIGNED NOT NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), KEY idx_customer_payment(customer_id,paid_at), CONSTRAINT fk_customer_payment_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_customer_payment_outlet FOREIGN KEY(outlet_id) REFERENCES outlets(id), CONSTRAINT fk_customer_payment_customer FOREIGN KEY(customer_id) REFERENCES customers(id), CONSTRAINT fk_customer_payment_sale FOREIGN KEY(sale_id) REFERENCES sales(id) ON DELETE SET NULL, CONSTRAINT fk_customer_payment_method FOREIGN KEY(payment_method_id) REFERENCES payment_methods(id), CONSTRAINT fk_customer_payment_account FOREIGN KEY(financial_account_id) REFERENCES financial_accounts(id) ON DELETE SET NULL, CONSTRAINT fk_customer_payment_cash FOREIGN KEY(cash_session_id) REFERENCES cash_register_sessions(id) ON DELETE SET NULL, CONSTRAINT fk_customer_payment_receiver FOREIGN KEY(received_by_business_user_id) REFERENCES business_users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE customer_ledger ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, business_id BIGINT UNSIGNED NOT NULL, customer_id BIGINT UNSIGNED NOT NULL, entry_type VARCHAR(32) NOT NULL, source_type VARCHAR(48) NOT NULL, source_public_id CHAR(26) NULL, debit_amount DECIMAL(19,6) NOT NULL DEFAULT 0, credit_amount DECIMAL(19,6) NOT NULL DEFAULT 0, entry_date DATETIME(6) NOT NULL, description VARCHAR(500) NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), KEY idx_customer_ledger(customer_id,entry_date), KEY idx_customer_ledger_source(source_type,source_public_id), CONSTRAINT fk_customer_ledger_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_customer_ledger_customer FOREIGN KEY(customer_id) REFERENCES customers(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE purchase_orders ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, po_no VARCHAR(64) NOT NULL, business_id BIGINT UNSIGNED NOT NULL, outlet_id BIGINT UNSIGNED NOT NULL, supplier_id BIGINT UNSIGNED NOT NULL, status VARCHAR(24) NOT NULL DEFAULT 'DRAFT', currency_code CHAR(3) NOT NULL, subtotal DECIMAL(19,6) NOT NULL DEFAULT 0, discount_amount DECIMAL(19,6) NOT NULL DEFAULT 0, tax_amount DECIMAL(19,6) NOT NULL DEFAULT 0, total_amount DECIMAL(19,6) NOT NULL DEFAULT 0, ordered_at DATETIME(6) NULL, expected_at DATETIME(6) NULL, submitted_at DATETIME(6) NULL, approved_at DATETIME(6) NULL, closed_at DATETIME(6) NULL, created_by_business_user_id BIGINT UNSIGNED NOT NULL, approved_by_business_user_id BIGINT UNSIGNED NULL, notes TEXT NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), UNIQUE KEY uq_po_no(business_id,po_no), KEY idx_po_supplier_status(supplier_id,status,created_at), CONSTRAINT fk_po_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_po_outlet FOREIGN KEY(outlet_id) REFERENCES outlets(id), CONSTRAINT fk_po_supplier FOREIGN KEY(supplier_id) REFERENCES suppliers(id), CONSTRAINT fk_po_creator FOREIGN KEY(created_by_business_user_id) REFERENCES business_users(id), CONSTRAINT fk_po_approver FOREIGN KEY(approved_by_business_user_id) REFERENCES business_users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE purchase_order_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, purchase_order_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NOT NULL, ordered_qty DECIMAL(18,4) NOT NULL, received_qty DECIMAL(18,4) NOT NULL DEFAULT 0, unit_cost DECIMAL(19,6) NOT NULL, tax_rate_percent DECIMAL(9,6) NOT NULL DEFAULT 0, tax_amount DECIMAL(19,6) NOT NULL DEFAULT 0, line_total DECIMAL(19,6) NOT NULL, KEY idx_po_item(purchase_order_id,product_id), CONSTRAINT fk_po_item_po FOREIGN KEY(purchase_order_id) REFERENCES purchase_orders(id) ON DELETE CASCADE, CONSTRAINT fk_po_item_product FOREIGN KEY(product_id) REFERENCES products(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE goods_receipts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, grn_no VARCHAR(64) NOT NULL, business_id BIGINT UNSIGNED NOT NULL, outlet_id BIGINT UNSIGNED NOT NULL, purchase_order_id BIGINT UNSIGNED NOT NULL, supplier_id BIGINT UNSIGNED NOT NULL, location_id BIGINT UNSIGNED NOT NULL, status VARCHAR(24) NOT NULL DEFAULT 'POSTED', received_at DATETIME(6) NOT NULL, received_by_business_user_id BIGINT UNSIGNED NOT NULL, supplier_document_no VARCHAR(96) NULL, notes TEXT NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), UNIQUE KEY uq_grn_no(business_id,grn_no), KEY idx_grn_po(purchase_order_id,received_at), CONSTRAINT fk_grn_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_grn_outlet FOREIGN KEY(outlet_id) REFERENCES outlets(id), CONSTRAINT fk_grn_po FOREIGN KEY(purchase_order_id) REFERENCES purchase_orders(id), CONSTRAINT fk_grn_supplier FOREIGN KEY(supplier_id) REFERENCES suppliers(id), CONSTRAINT fk_grn_location FOREIGN KEY(location_id) REFERENCES inventory_locations(id), CONSTRAINT fk_grn_receiver FOREIGN KEY(received_by_business_user_id) REFERENCES business_users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE goods_receipt_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, goods_receipt_id BIGINT UNSIGNED NOT NULL, purchase_order_item_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NOT NULL, batch_id BIGINT UNSIGNED NULL, received_qty DECIMAL(18,4) NOT NULL, accepted_qty DECIMAL(18,4) NOT NULL, rejected_qty DECIMAL(18,4) NOT NULL DEFAULT 0, unit_cost DECIMAL(19,6) NOT NULL, CONSTRAINT fk_grn_item_grn FOREIGN KEY(goods_receipt_id) REFERENCES goods_receipts(id) ON DELETE CASCADE, CONSTRAINT fk_grn_item_po_item FOREIGN KEY(purchase_order_item_id) REFERENCES purchase_order_items(id), CONSTRAINT fk_grn_item_product FOREIGN KEY(product_id) REFERENCES products(id), CONSTRAINT fk_grn_item_batch FOREIGN KEY(batch_id) REFERENCES product_batches(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE purchases ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, purchase_no VARCHAR(64) NOT NULL, business_id BIGINT UNSIGNED NOT NULL, outlet_id BIGINT UNSIGNED NOT NULL, supplier_id BIGINT UNSIGNED NOT NULL, location_id BIGINT UNSIGNED NOT NULL, goods_receipt_id BIGINT UNSIGNED NULL, status VARCHAR(24) NOT NULL DEFAULT 'POSTED', currency_code CHAR(3) NOT NULL, subtotal DECIMAL(19,6) NOT NULL DEFAULT 0, discount_amount DECIMAL(19,6) NOT NULL DEFAULT 0, tax_amount DECIMAL(19,6) NOT NULL DEFAULT 0, total_amount DECIMAL(19,6) NOT NULL DEFAULT 0, paid_amount DECIMAL(19,6) NOT NULL DEFAULT 0, due_amount DECIMAL(19,6) NOT NULL DEFAULT 0, supplier_invoice_no VARCHAR(96) NULL, purchased_at DATETIME(6) NOT NULL, created_by_business_user_id BIGINT UNSIGNED NOT NULL, notes TEXT NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), UNIQUE KEY uq_purchase_no(business_id,purchase_no), KEY idx_purchase_supplier_date(supplier_id,purchased_at), CONSTRAINT fk_purchase_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_purchase_outlet FOREIGN KEY(outlet_id) REFERENCES outlets(id), CONSTRAINT fk_purchase_supplier FOREIGN KEY(supplier_id) REFERENCES suppliers(id), CONSTRAINT fk_purchase_location FOREIGN KEY(location_id) REFERENCES inventory_locations(id), CONSTRAINT fk_purchase_grn FOREIGN KEY(goods_receipt_id) REFERENCES goods_receipts(id) ON DELETE SET NULL, CONSTRAINT fk_purchase_creator FOREIGN KEY(created_by_business_user_id) REFERENCES business_users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE purchase_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, purchase_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NOT NULL, batch_id BIGINT UNSIGNED NULL, quantity DECIMAL(18,4) NOT NULL, unit_cost DECIMAL(19,6) NOT NULL, tax_rate_percent DECIMAL(9,6) NOT NULL DEFAULT 0, tax_amount DECIMAL(19,6) NOT NULL DEFAULT 0, line_total DECIMAL(19,6) NOT NULL, KEY idx_purchase_item_purchase(purchase_id), CONSTRAINT fk_purchase_item_purchase FOREIGN KEY(purchase_id) REFERENCES purchases(id) ON DELETE CASCADE, CONSTRAINT fk_purchase_item_product FOREIGN KEY(product_id) REFERENCES products(id), CONSTRAINT fk_purchase_item_batch FOREIGN KEY(batch_id) REFERENCES product_batches(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE supplier_payments ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, business_id BIGINT UNSIGNED NOT NULL, outlet_id BIGINT UNSIGNED NOT NULL, supplier_id BIGINT UNSIGNED NOT NULL, purchase_id BIGINT UNSIGNED NULL, payment_method_id BIGINT UNSIGNED NOT NULL, financial_account_id BIGINT UNSIGNED NULL, amount DECIMAL(19,6) NOT NULL, reference_no VARCHAR(191) NULL, paid_at DATETIME(6) NOT NULL, paid_by_business_user_id BIGINT UNSIGNED NOT NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), KEY idx_supplier_payment(supplier_id,paid_at), CONSTRAINT fk_supplier_payment_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_supplier_payment_outlet FOREIGN KEY(outlet_id) REFERENCES outlets(id), CONSTRAINT fk_supplier_payment_supplier FOREIGN KEY(supplier_id) REFERENCES suppliers(id), CONSTRAINT fk_supplier_payment_purchase FOREIGN KEY(purchase_id) REFERENCES purchases(id) ON DELETE SET NULL, CONSTRAINT fk_supplier_payment_method FOREIGN KEY(payment_method_id) REFERENCES payment_methods(id), CONSTRAINT fk_supplier_payment_account FOREIGN KEY(financial_account_id) REFERENCES financial_accounts(id) ON DELETE SET NULL, CONSTRAINT fk_supplier_payment_user FOREIGN KEY(paid_by_business_user_id) REFERENCES business_users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE supplier_ledger ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, business_id BIGINT UNSIGNED NOT NULL, supplier_id BIGINT UNSIGNED NOT NULL, entry_type VARCHAR(32) NOT NULL, source_type VARCHAR(48) NOT NULL, source_public_id CHAR(26) NULL, debit_amount DECIMAL(19,6) NOT NULL DEFAULT 0, credit_amount DECIMAL(19,6) NOT NULL DEFAULT 0, entry_date DATETIME(6) NOT NULL, description VARCHAR(500) NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), KEY idx_supplier_ledger(supplier_id,entry_date), KEY idx_supplier_ledger_source(source_type,source_public_id), CONSTRAINT fk_supplier_ledger_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_supplier_ledger_supplier FOREIGN KEY(supplier_id) REFERENCES suppliers(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE inventory_cost_layers ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, business_id BIGINT UNSIGNED NOT NULL, location_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NOT NULL, batch_id BIGINT UNSIGNED NULL, source_type VARCHAR(48) NOT NULL, source_public_id CHAR(26) NULL, received_at DATETIME(6) NOT NULL, original_qty DECIMAL(18,4) NOT NULL, remaining_qty DECIMAL(18,4) NOT NULL, unit_cost DECIMAL(19,6) NOT NULL, status VARCHAR(24) NOT NULL DEFAULT 'OPEN', created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), KEY idx_fifo_layer(business_id,location_id,product_id,status,received_at,id), CONSTRAINT fk_cost_layer_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_cost_layer_location FOREIGN KEY(location_id) REFERENCES inventory_locations(id), CONSTRAINT fk_cost_layer_product FOREIGN KEY(product_id) REFERENCES products(id), CONSTRAINT fk_cost_layer_batch FOREIGN KEY(batch_id) REFERENCES product_batches(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE sale_cost_allocations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, business_id BIGINT UNSIGNED NOT NULL, sale_item_id BIGINT UNSIGNED NOT NULL, cost_layer_id BIGINT UNSIGNED NOT NULL, quantity DECIMAL(18,4) NOT NULL, unit_cost DECIMAL(19,6) NOT NULL, total_cost DECIMAL(19,6) NOT NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), UNIQUE KEY uq_sale_cost_layer(sale_item_id,cost_layer_id), CONSTRAINT fk_sale_cost_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_sale_cost_item FOREIGN KEY(sale_item_id) REFERENCES sale_items(id) ON DELETE CASCADE, CONSTRAINT fk_sale_cost_layer FOREIGN KEY(cost_layer_id) REFERENCES inventory_cost_layers(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE cost_layer_movements ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, business_id BIGINT UNSIGNED NOT NULL, cost_layer_id BIGINT UNSIGNED NOT NULL, movement_type VARCHAR(32) NOT NULL, source_type VARCHAR(48) NOT NULL, source_public_id CHAR(26) NULL, quantity_delta DECIMAL(18,4) NOT NULL, remaining_after DECIMAL(18,4) NOT NULL, occurred_at DATETIME(6) NOT NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), KEY idx_cost_layer_move(cost_layer_id,occurred_at), CONSTRAINT fk_cost_move_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_cost_move_layer FOREIGN KEY(cost_layer_id) REFERENCES inventory_cost_layers(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE income_categories ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, business_id BIGINT UNSIGNED NOT NULL, name VARCHAR(120) NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, UNIQUE KEY uq_income_category(business_id,name), CONSTRAINT fk_income_category_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE expense_categories ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, business_id BIGINT UNSIGNED NOT NULL, name VARCHAR(120) NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, UNIQUE KEY uq_expense_category(business_id,name), CONSTRAINT fk_expense_category_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE other_income ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, business_id BIGINT UNSIGNED NOT NULL, outlet_id BIGINT UNSIGNED NULL, category_id BIGINT UNSIGNED NULL, financial_account_id BIGINT UNSIGNED NOT NULL, amount DECIMAL(19,6) NOT NULL, description VARCHAR(500) NOT NULL, reference_no VARCHAR(96) NULL, occurred_at DATETIME(6) NOT NULL, created_by_business_user_id BIGINT UNSIGNED NOT NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), KEY idx_income_date(business_id,occurred_at), CONSTRAINT fk_income_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_income_outlet FOREIGN KEY(outlet_id) REFERENCES outlets(id) ON DELETE SET NULL, CONSTRAINT fk_income_category FOREIGN KEY(category_id) REFERENCES income_categories(id) ON DELETE SET NULL, CONSTRAINT fk_income_account FOREIGN KEY(financial_account_id) REFERENCES financial_accounts(id), CONSTRAINT fk_income_user FOREIGN KEY(created_by_business_user_id) REFERENCES business_users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE expenses ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, business_id BIGINT UNSIGNED NOT NULL, outlet_id BIGINT UNSIGNED NULL, category_id BIGINT UNSIGNED NULL, financial_account_id BIGINT UNSIGNED NOT NULL, amount DECIMAL(19,6) NOT NULL, description VARCHAR(500) NOT NULL, reference_no VARCHAR(96) NULL, occurred_at DATETIME(6) NOT NULL, created_by_business_user_id BIGINT UNSIGNED NOT NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), KEY idx_expense_date(business_id,occurred_at), CONSTRAINT fk_expense_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_expense_outlet FOREIGN KEY(outlet_id) REFERENCES outlets(id) ON DELETE SET NULL, CONSTRAINT fk_expense_category FOREIGN KEY(category_id) REFERENCES expense_categories(id) ON DELETE SET NULL, CONSTRAINT fk_expense_account FOREIGN KEY(financial_account_id) REFERENCES financial_accounts(id), CONSTRAINT fk_expense_user FOREIGN KEY(created_by_business_user_id) REFERENCES business_users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE audit_logs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, business_id BIGINT UNSIGNED NULL, outlet_id BIGINT UNSIGNED NULL, user_id BIGINT UNSIGNED NULL, business_user_id BIGINT UNSIGNED NULL, action_key VARCHAR(96) NOT NULL, entity_type VARCHAR(64) NULL, entity_public_id CHAR(26) NULL, old_values_json LONGTEXT NULL, new_values_json LONGTEXT NULL, metadata_json LONGTEXT NULL, ip_address VARCHAR(45) NULL, user_agent VARCHAR(500) NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), KEY idx_audit_business(business_id,created_at), KEY idx_audit_entity(entity_type,entity_public_id), KEY idx_audit_user(user_id,created_at), CONSTRAINT fk_audit_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE SET NULL, CONSTRAINT fk_audit_outlet FOREIGN KEY(outlet_id) REFERENCES outlets(id) ON DELETE SET NULL, CONSTRAINT fk_audit_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_audit_business_user FOREIGN KEY(business_user_id) REFERENCES business_users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE notifications ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL UNIQUE, user_id BIGINT UNSIGNED NOT NULL, business_id BIGINT UNSIGNED NULL, notification_type VARCHAR(64) NOT NULL, title VARCHAR(191) NOT NULL, message TEXT NOT NULL, action_url VARCHAR(500) NULL, read_at DATETIME(6) NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), KEY idx_notification_user(user_id,read_at,created_at), CONSTRAINT fk_notification_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_notification_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE job_queue ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, queue_name VARCHAR(48) NOT NULL DEFAULT 'default', job_type VARCHAR(96) NOT NULL, payload_json LONGTEXT NOT NULL, status VARCHAR(24) NOT NULL DEFAULT 'PENDING', attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0, max_attempts SMALLINT UNSIGNED NOT NULL DEFAULT 5, available_at DATETIME(6) NOT NULL, locked_at DATETIME(6) NULL, lock_token VARCHAR(96) NULL, last_error TEXT NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), completed_at DATETIME(6) NULL, KEY idx_job_pickup(queue_name,status,available_at), KEY idx_job_locked(status,locked_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE idempotency_keys ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, business_id BIGINT UNSIGNED NULL, user_id BIGINT UNSIGNED NULL, idempotency_key VARCHAR(191) NOT NULL, operation_key VARCHAR(96) NOT NULL, request_hash CHAR(64) NOT NULL, response_code SMALLINT UNSIGNED NULL, response_body LONGTEXT NULL, resource_type VARCHAR(64) NULL, resource_public_id CHAR(26) NULL, expires_at DATETIME(6) NOT NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), UNIQUE KEY uq_idempotency(idempotency_key,operation_key), KEY idx_idempotency_expiry(expires_at), CONSTRAINT fk_idempotency_business FOREIGN KEY(business_id) REFERENCES businesses(id) ON DELETE CASCADE, CONSTRAINT fk_idempotency_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE login_attempts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, email VARCHAR(191) NOT NULL, ip_address VARCHAR(45) NOT NULL, was_successful TINYINT(1) NOT NULL DEFAULT 0, attempted_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), KEY idx_login_email(email,attempted_at), KEY idx_login_ip(ip_address,attempted_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE app_versions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, platform VARCHAR(24) NOT NULL, version_name VARCHAR(40) NOT NULL, build_number VARCHAR(40) NOT NULL, minimum_supported_version VARCHAR(40) NULL, minimum_supported_build VARCHAR(40) NULL, force_upgrade TINYINT(1) NOT NULL DEFAULT 0, release_notes TEXT NULL, download_url VARCHAR(500) NULL, released_at DATETIME(6) NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, UNIQUE KEY uq_app_version(platform,version_name,build_number) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; SET FOREIGN_KEY_CHECKS = 1;