-- =========================================================
-- 028_agent_module.sql
-- Sprint 10: agent module foundation
-- =========================================================

CREATE TABLE IF NOT EXISTS agent_daily_books (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  customer_id BIGINT UNSIGNED NOT NULL,
  book_date DATE NOT NULL,
  status ENUM('open','locked') NOT NULL DEFAULT 'open',
  locked_by_user_id BIGINT UNSIGNED NULL,
  locked_at DATETIME NULL,
  created_by_user_id BIGINT UNSIGNED NOT NULL,
  created_by_name_snapshot VARCHAR(120) NOT NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_agent_daily_books_loc_customer_date (location_id, customer_id, book_date),
  KEY idx_agent_daily_books_tenant_loc_date (tenant_id, location_id, book_date),
  KEY idx_agent_daily_books_tenant_customer_date (tenant_id, customer_id, book_date),
  KEY idx_agent_daily_books_status (status),
  CONSTRAINT fk_agent_daily_books_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_agent_daily_books_location FOREIGN KEY (location_id) REFERENCES locations(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_agent_daily_books_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS agent_daily_book_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  daily_book_id BIGINT UNSIGNED NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  product_id BIGINT UNSIGNED NOT NULL,
  product_variant_id BIGINT UNSIGNED NULL,
  product_name_snapshot VARCHAR(180) NOT NULL,
  variant_name_snapshot VARCHAR(180) NULL,
  sku_snapshot VARCHAR(120) NULL,
  unit_snapshot VARCHAR(60) NOT NULL,
  warehouse_group_name_snapshot VARCHAR(120) NULL,
  description TEXT NULL,
  qty DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  unit_price DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  line_total DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  uses_decimal TINYINT(1) NOT NULL DEFAULT 0,
  bundle_snapshot_json JSON NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_agent_daily_book_items_book (daily_book_id),
  KEY idx_agent_daily_book_items_product (product_id),
  KEY idx_agent_daily_book_items_variant (product_variant_id),
  CONSTRAINT fk_agent_daily_book_items_book FOREIGN KEY (daily_book_id) REFERENCES agent_daily_books(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS agent_deposits (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  customer_id BIGINT UNSIGNED NOT NULL,
  deposit_date DATE NOT NULL,
  amount DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  note VARCHAR(255) NULL,
  status ENUM('available','used','void') NOT NULL DEFAULT 'available',
  created_by_user_id BIGINT UNSIGNED NOT NULL,
  created_by_name_snapshot VARCHAR(120) NOT NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_agent_deposits_scope_date (tenant_id, location_id, customer_id, deposit_date),
  KEY idx_agent_deposits_status (status),
  CONSTRAINT fk_agent_deposits_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_agent_deposits_location FOREIGN KEY (location_id) REFERENCES locations(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_agent_deposits_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS agent_recaps (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  customer_id BIGINT UNSIGNED NOT NULL,
  code VARCHAR(80) NOT NULL,
  date_from DATE NOT NULL,
  date_to DATE NOT NULL,
  status ENUM('draft','final') NOT NULL DEFAULT 'draft',
  total_gross DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  deposit_applied DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  amount_due DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  payment_amount DECIMAL(18,2) NULL,
  payment_note VARCHAR(255) NULL,
  paid_at DATETIME NULL,
  landing_token VARCHAR(80) NOT NULL,
  created_by_user_id BIGINT UNSIGNED NOT NULL,
  created_by_name_snapshot VARCHAR(120) NOT NULL,
  finalized_by_user_id BIGINT UNSIGNED NULL,
  finalized_by_name_snapshot VARCHAR(120) NULL,
  finalized_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_agent_recaps_code (code),
  UNIQUE KEY uq_agent_recaps_landing_token (landing_token),
  KEY idx_agent_recaps_scope_range (tenant_id, location_id, customer_id, date_from, date_to),
  KEY idx_agent_recaps_status (status),
  CONSTRAINT fk_agent_recaps_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_agent_recaps_location FOREIGN KEY (location_id) REFERENCES locations(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_agent_recaps_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS agent_recap_sources (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  agent_recap_id BIGINT UNSIGNED NOT NULL,
  daily_book_id BIGINT UNSIGNED NOT NULL,
  book_date DATE NOT NULL,
  created_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_agent_recap_sources_daily_book (daily_book_id),
  UNIQUE KEY uq_agent_recap_sources_recap_book (agent_recap_id, daily_book_id),
  KEY idx_agent_recap_sources_recap (agent_recap_id),
  CONSTRAINT fk_agent_recap_sources_recap FOREIGN KEY (agent_recap_id) REFERENCES agent_recaps(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_agent_recap_sources_daily_book FOREIGN KEY (daily_book_id) REFERENCES agent_daily_books(id)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS agent_recap_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  agent_recap_id BIGINT UNSIGNED NOT NULL,
  book_date DATE NOT NULL,
  daily_book_item_id BIGINT UNSIGNED NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  product_id BIGINT UNSIGNED NOT NULL,
  product_variant_id BIGINT UNSIGNED NULL,
  product_name_snapshot VARCHAR(180) NOT NULL,
  variant_name_snapshot VARCHAR(180) NULL,
  sku_snapshot VARCHAR(120) NULL,
  unit_snapshot VARCHAR(60) NOT NULL,
  warehouse_group_name_snapshot VARCHAR(120) NULL,
  description TEXT NULL,
  qty DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  unit_price DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  line_total DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  uses_decimal TINYINT(1) NOT NULL DEFAULT 0,
  bundle_snapshot_json JSON NULL,
  created_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_agent_recap_items_recap (agent_recap_id),
  KEY idx_agent_recap_items_daily_item (daily_book_item_id),
  CONSTRAINT fk_agent_recap_items_recap FOREIGN KEY (agent_recap_id) REFERENCES agent_recaps(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_agent_recap_items_daily_item FOREIGN KEY (daily_book_item_id) REFERENCES agent_daily_book_items(id)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS agent_recap_deposit_links (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  agent_recap_id BIGINT UNSIGNED NOT NULL,
  agent_deposit_id BIGINT UNSIGNED NOT NULL,
  amount_used DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  created_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_agent_recap_deposit_links_pair (agent_recap_id, agent_deposit_id),
  KEY idx_agent_recap_deposit_links_deposit (agent_deposit_id),
  CONSTRAINT fk_agent_recap_deposit_links_recap FOREIGN KEY (agent_recap_id) REFERENCES agent_recaps(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_agent_recap_deposit_links_deposit FOREIGN KEY (agent_deposit_id) REFERENCES agent_deposits(id)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;