CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  role ENUM('admin') NOT NULL DEFAULT 'admin',
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS customers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  phone VARCHAR(20) NOT NULL,
  address VARCHAR(255) NULL,
  notes TEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  INDEX idx_customers_phone (phone),
  INDEX idx_customers_name (name),
  INDEX idx_customers_active_name (is_active, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS customer_transactions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id BIGINT UNSIGNED NOT NULL,
  transaction_type ENUM('debit', 'credit') NOT NULL,
  transaction_date DATE NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  note TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  CONSTRAINT chk_customer_transactions_amount_positive CHECK (amount > 0),
  CONSTRAINT fk_customer_transactions_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_customer_transactions_user FOREIGN KEY (created_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
  INDEX idx_customer_transactions_customer (customer_id),
  INDEX idx_customer_transactions_type (transaction_type),
  INDEX idx_customer_transactions_date (transaction_date),
  INDEX idx_customer_transactions_customer_date (customer_id, transaction_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS daily_khatas (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  entry_date DATE NOT NULL,
  total_chicken_purchasing DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  total_sabzi_purchasing DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  total_chicken_sale DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  total_sabzi_sale DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  total_expenses DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  expenses_details TEXT NULL,
  cash_in_hand DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  bank_payment_received DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  total_udhaar DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  total_purchasing DECIMAL(14,2)
    GENERATED ALWAYS AS (total_chicken_purchasing + total_sabzi_purchasing) STORED,
  total_sale DECIMAL(14,2)
    GENERATED ALWAYS AS (total_chicken_sale + total_sabzi_sale) STORED,
  sale_distribution_total DECIMAL(14,2)
    GENERATED ALWAYS AS (total_expenses + cash_in_hand + bank_payment_received + total_udhaar) STORED,
  sale_match_difference DECIMAL(14,2)
    GENERATED ALWAYS AS ((total_chicken_sale + total_sabzi_sale) - (total_expenses + cash_in_hand + bank_payment_received + total_udhaar)) STORED,
  profit_loss DECIMAL(14,2)
    GENERATED ALWAYS AS ((total_chicken_sale + total_sabzi_sale) - (total_chicken_purchasing + total_sabzi_purchasing)) STORED,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_daily_khatas_user FOREIGN KEY (created_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
  UNIQUE KEY uq_daily_khatas_entry_date (entry_date),
  INDEX idx_daily_khatas_created_by (created_by),
  INDEX idx_daily_khatas_entry_date (entry_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS personal_access_tokens (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tokenable_type VARCHAR(255) NOT NULL,
  tokenable_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(255) NOT NULL,
  token VARCHAR(64) NOT NULL,
  abilities TEXT NULL,
  last_used_at TIMESTAMP NULL DEFAULT NULL,
  expires_at TIMESTAMP NULL DEFAULT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY personal_access_tokens_token_unique (token),
  INDEX personal_access_tokens_tokenable_type_tokenable_id_index (tokenable_type, tokenable_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;