-- Bizora OS — server database schema
-- Powered by AETHERIS TECH & INNOVATION (ATI)
-- Tenant isolation: every business-scoped table carries business_id and every
-- query in the application layer is expected to filter by it explicitly.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS businesses (
  id            CHAR(36)     NOT NULL PRIMARY KEY,
  name          VARCHAR(190) NOT NULL,
  phone         VARCHAR(40)  NULL,
  currency      VARCHAR(8)   NOT NULL DEFAULT 'GHS',
  timezone      VARCHAR(60)  NOT NULL DEFAULT 'Africa/Accra',
  status        ENUM('active','suspended') NOT NULL DEFAULT 'active',
  created_at    DATETIME(3)  NOT NULL,
  updated_at    DATETIME(3)  NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS users (
  id            CHAR(36)     NOT NULL PRIMARY KEY,
  business_id   CHAR(36)     NOT NULL,
  name          VARCHAR(190) NOT NULL,
  email         VARCHAR(190) NULL,
  phone         VARCHAR(40)  NULL,
  password_hash VARCHAR(255) NOT NULL,
  role          ENUM('owner','manager','cashier','salesperson','inventory','accountant','viewer') NOT NULL DEFAULT 'owner',
  status        ENUM('active','disabled') NOT NULL DEFAULT 'active',
  created_at    DATETIME(3)  NOT NULL,
  updated_at    DATETIME(3)  NOT NULL,
  UNIQUE KEY uniq_business_email (business_id, email),
  KEY idx_users_business (business_id),
  CONSTRAINT fk_users_business FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS auth_tokens (
  id            CHAR(36)     NOT NULL PRIMARY KEY,
  user_id       CHAR(36)     NOT NULL,
  token_hash    CHAR(64)     NOT NULL,
  device_id     VARCHAR(100) NULL,
  expires_at    DATETIME(3)  NOT NULL,
  created_at    DATETIME(3)  NOT NULL,
  revoked_at    DATETIME(3)  NULL,
  UNIQUE KEY uniq_token_hash (token_hash),
  KEY idx_tokens_user (user_id),
  CONSTRAINT fk_tokens_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS devices (
  id            CHAR(36)     NOT NULL PRIMARY KEY,
  business_id   CHAR(36)     NOT NULL,
  user_id       CHAR(36)     NULL,
  device_uuid   VARCHAR(100) NOT NULL,
  label         VARCHAR(190) NULL,
  last_seen_at  DATETIME(3)  NULL,
  created_at    DATETIME(3)  NOT NULL,
  UNIQUE KEY uniq_device_uuid (device_uuid),
  KEY idx_devices_business (business_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS products (
  id            CHAR(36)     NOT NULL PRIMARY KEY,
  business_id   CHAR(36)     NOT NULL,
  sku           VARCHAR(80)  NULL,
  name          VARCHAR(190) NOT NULL,
  price         DECIMAL(14,2) NOT NULL DEFAULT 0,
  cost          DECIMAL(14,2) NOT NULL DEFAULT 0,
  stock         DECIMAL(14,2) NOT NULL DEFAULT 0,
  stock_tracking TINYINT(1)  NOT NULL DEFAULT 1,
  version       INT UNSIGNED NOT NULL DEFAULT 1,
  deleted_at    DATETIME(3)  NULL,
  created_at    DATETIME(3)  NOT NULL,
  updated_at    DATETIME(3)  NOT NULL,
  KEY idx_products_business (business_id),
  KEY idx_products_sku (business_id, sku),
  CONSTRAINT fk_products_business FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS customers (
  id            CHAR(36)     NOT NULL PRIMARY KEY,
  business_id   CHAR(36)     NOT NULL,
  name          VARCHAR(190) NOT NULL,
  phone         VARCHAR(40)  NULL,
  address       VARCHAR(255) NULL,
  version       INT UNSIGNED NOT NULL DEFAULT 1,
  deleted_at    DATETIME(3)  NULL,
  created_at    DATETIME(3)  NOT NULL,
  updated_at    DATETIME(3)  NOT NULL,
  KEY idx_customers_business (business_id),
  CONSTRAINT fk_customers_business FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS suppliers (
  id            CHAR(36)     NOT NULL PRIMARY KEY,
  business_id   CHAR(36)     NOT NULL,
  name          VARCHAR(190) NOT NULL,
  phone         VARCHAR(40)  NULL,
  address       VARCHAR(255) NULL,
  deleted_at    DATETIME(3)  NULL,
  created_at    DATETIME(3)  NOT NULL,
  updated_at    DATETIME(3)  NOT NULL,
  KEY idx_suppliers_business (business_id),
  CONSTRAINT fk_suppliers_business FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sales (
  id             CHAR(36)     NOT NULL PRIMARY KEY,
  business_id    CHAR(36)     NOT NULL,
  customer_id    CHAR(36)     NULL,
  invoice_no     VARCHAR(60)  NOT NULL,
  subtotal       DECIMAL(14,2) NOT NULL DEFAULT 0,
  discount       DECIMAL(14,2) NOT NULL DEFAULT 0,
  charges        DECIMAL(14,2) NOT NULL DEFAULT 0,
  total          DECIMAL(14,2) NOT NULL DEFAULT 0,
  amount_paid    DECIMAL(14,2) NOT NULL DEFAULT 0,
  outstanding    DECIMAL(14,2) NOT NULL DEFAULT 0,
  status         ENUM('POSTED','VOID') NOT NULL DEFAULT 'POSTED',
  device_id      VARCHAR(100) NULL,
  client_operation_id CHAR(36) NULL,
  created_at     DATETIME(3)  NOT NULL,
  updated_at     DATETIME(3)  NOT NULL,
  UNIQUE KEY uniq_sale_business_invoice (business_id, invoice_no),
  UNIQUE KEY uniq_sale_client_op (client_operation_id),
  KEY idx_sales_business (business_id, created_at),
  KEY idx_sales_customer (customer_id),
  CONSTRAINT fk_sales_business FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sale_items (
  id            CHAR(36)     NOT NULL PRIMARY KEY,
  sale_id       CHAR(36)     NOT NULL,
  product_id    CHAR(36)     NULL,
  name          VARCHAR(190) NOT NULL,
  qty           DECIMAL(14,2) NOT NULL,
  unit_price    DECIMAL(14,2) NOT NULL,
  line_total    DECIMAL(14,2) NOT NULL,
  KEY idx_sale_items_sale (sale_id),
  CONSTRAINT fk_sale_items_sale FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS purchases (
  id            CHAR(36)     NOT NULL PRIMARY KEY,
  business_id   CHAR(36)     NOT NULL,
  supplier_id   CHAR(36)     NULL,
  reference_no  VARCHAR(60)  NULL,
  total         DECIMAL(14,2) NOT NULL DEFAULT 0,
  created_at    DATETIME(3)  NOT NULL,
  updated_at    DATETIME(3)  NOT NULL,
  KEY idx_purchases_business (business_id),
  CONSTRAINT fk_purchases_business FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS purchase_items (
  id            CHAR(36)     NOT NULL PRIMARY KEY,
  purchase_id   CHAR(36)     NOT NULL,
  product_id    CHAR(36)     NULL,
  qty           DECIMAL(14,2) NOT NULL,
  unit_cost     DECIMAL(14,2) NOT NULL,
  KEY idx_purchase_items_purchase (purchase_id),
  CONSTRAINT fk_purchase_items_purchase FOREIGN KEY (purchase_id) REFERENCES purchases(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS expenses (
  id            CHAR(36)     NOT NULL PRIMARY KEY,
  business_id   CHAR(36)     NOT NULL,
  category      VARCHAR(100) NOT NULL DEFAULT 'general',
  amount        DECIMAL(14,2) NOT NULL,
  note          VARCHAR(255) NULL,
  created_at    DATETIME(3)  NOT NULL,
  updated_at    DATETIME(3)  NOT NULL,
  KEY idx_expenses_business (business_id, created_at),
  CONSTRAINT fk_expenses_business FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payments (
  id             CHAR(36)     NOT NULL PRIMARY KEY,
  business_id    CHAR(36)     NOT NULL,
  reference_type VARCHAR(40)  NOT NULL,
  reference_id   CHAR(36)     NOT NULL,
  amount         DECIMAL(14,2) NOT NULL,
  method         VARCHAR(40)  NOT NULL DEFAULT 'cash',
  created_at     DATETIME(3)  NOT NULL,
  KEY idx_payments_business (business_id),
  KEY idx_payments_reference (reference_type, reference_id),
  CONSTRAINT fk_payments_business FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS inventory_movements (
  id             CHAR(36)     NOT NULL PRIMARY KEY,
  business_id    CHAR(36)     NOT NULL,
  product_id     CHAR(36)     NOT NULL,
  movement_type  ENUM('PURCHASE','SALE','RETURN_IN','RETURN_OUT','ADJUSTMENT_IN','ADJUSTMENT_OUT','TRANSFER_OUT','TRANSFER_IN') NOT NULL,
  qty            DECIMAL(14,2) NOT NULL,
  reference      CHAR(36)     NULL,
  created_at     DATETIME(3)  NOT NULL,
  KEY idx_inv_movements_product (product_id, created_at),
  KEY idx_inv_movements_business (business_id),
  CONSTRAINT fk_inv_movements_business FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS attachments (
  id            CHAR(36)     NOT NULL PRIMARY KEY,
  business_id   CHAR(36)     NOT NULL,
  entity_type   VARCHAR(40)  NOT NULL,
  entity_id     CHAR(36)     NOT NULL,
  storage_key   VARCHAR(255) NOT NULL,
  created_at    DATETIME(3)  NOT NULL,
  KEY idx_attachments_entity (entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Every mutation from a device carries a globally unique operation_id.
-- Recording processed IDs here makes retried pushes idempotent (blueprint §22).
CREATE TABLE IF NOT EXISTS sync_operations (
  operation_id   CHAR(36)     NOT NULL PRIMARY KEY,
  business_id    CHAR(36)     NOT NULL,
  device_id      VARCHAR(100) NOT NULL,
  entity_type    VARCHAR(40)  NOT NULL,
  entity_id      CHAR(36)     NOT NULL,
  operation_type ENUM('CREATE','UPDATE','DELETE') NOT NULL,
  status         ENUM('ACKED','CONFLICT','FAILED') NOT NULL DEFAULT 'ACKED',
  error          VARCHAR(255) NULL,
  processed_at   DATETIME(3)  NOT NULL,
  KEY idx_sync_ops_business (business_id, processed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS audit_logs (
  id            CHAR(36)     NOT NULL PRIMARY KEY,
  business_id   CHAR(36)     NULL,
  actor_id      CHAR(36)     NULL,
  action        VARCHAR(100) NOT NULL,
  entity_type   VARCHAR(40)  NULL,
  entity_id     CHAR(36)     NULL,
  metadata      TEXT         NULL,
  created_at    DATETIME(3)  NOT NULL,
  KEY idx_audit_business (business_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;