-- v149: full install = v148 schema + accounting module (acc_* tables). Run on an EMPTY database.
-- v146-fix1: (1) bk_agent_memory.memory_key/scope -> VARCHAR(191) so UNIQUE key works (error 1170);
--            (2) all TEXT columns with literal DEFAULT -> VARCHAR(32/64) (MySQL 8 error 1101);
--            (3) first admin: run BarKala_CPanel_first_admin.sql AFTER signing up.
-- v146-fix2: inline REFERENCES removed; every FK is now declared exactly once via ALTER TABLE.
-- v146-fix3: products.image / voice audio_data -> LONGTEXT (base64 > 64KB), long texts -> MEDIUMTEXT,
--            updated_at now auto-updates (replaces the Postgres set_updated_at triggers).
-- Run on an EMPTY database (ALTER TABLE ... ADD CONSTRAINT section is not re-runnable).
-- BarKala v146 -> cPanel MySQL/MariaDB schema migration
-- Generated from the SQL files shipped inside BarKala_v146_TelegramBot.zip
-- IMPORTANT: This file replaces Supabase/PostgreSQL schema primitives with MySQL-compatible tables.
-- Supabase Auth/RLS/SQL RPC logic is intentionally NOT faked here; those responsibilities move to the cPanel API layer.
-- Target: MySQL 8.x / MariaDB 10.6+ (JSON supported).
-- IDs formerly generated by gen_random_uuid() are CHAR(36); the cPanel API should generate UUIDs.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

-- Replacement for Supabase auth.users. Passwords MUST be stored as strong password hashes by the API (e.g. Argon2id/bcrypt), never plaintext.
CREATE TABLE IF NOT EXISTS users (
  id CHAR(36) NOT NULL,
  email VARCHAR(320) NULL,
  phone VARCHAR(64) NULL,
  password_hash VARCHAR(255) NULL,
  role VARCHAR(32) NOT NULL DEFAULT 'retail',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_email (email),
  KEY idx_users_phone (phone),
  KEY idx_users_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- profiles
CREATE TABLE IF NOT EXISTS `profiles` (
id         CHAR(36) primary key,
  email      TEXT,
  phone      TEXT,
  role VARCHAR(32) not null default 'retail',
  created_at DATETIME not null default CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- orders
CREATE TABLE IF NOT EXISTS `orders` (
id             CHAR(36) primary key,
  agent_id       CHAR(36),
  customer_name  TEXT,
  customer_phone TEXT,
  total_amount   DECIMAL(20,2) not null default 0,
  item_count     INT not null default 0,
  invoice_text   MEDIUMTEXT,
  status VARCHAR(32) not null default 'pending' check (status in ('pending','paid','cancelled')),
  created_at     DATETIME not null default CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- marketer_orders
CREATE TABLE IF NOT EXISTS `marketer_orders` (
id              CHAR(36) primary key,
  marketer_token  TEXT,
  marketer_name   TEXT,
  marketer_phone  TEXT,
  customer_name   TEXT,
  customer_phone  TEXT,
  total_amount    DECIMAL(20,2) not null default 0,
  item_count      INT not null default 0,
  invoice_text    MEDIUMTEXT,
  invoice_data    JSON,
  status VARCHAR(32) not null default 'pending' check (status in ('pending','paid','cancelled')),
  created_at      DATETIME not null default CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bk_projects
CREATE TABLE IF NOT EXISTS `bk_projects` (
id                CHAR(36) primary key,
  title             TEXT not null,
  client_name       TEXT,
  client_phone      TEXT,
  status VARCHAR(32) not null default 'draft' check (status in ('draft','sent','approved','closed')),
  discount_percent  DECIMAL(20,2) not null default 0,
  notes             TEXT,
  items             JSON NULL,
  created_at        DATETIME not null default CURRENT_TIMESTAMP,
  updated_at        DATETIME not null default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bk_stories
CREATE TABLE IF NOT EXISTS `bk_stories` (
id CHAR(36) NOT NULL,
  image_data LONGTEXT NOT NULL,
  caption TEXT NULL,
  views_count INT NOT NULL DEFAULT 0,
  reactions_count INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_bk_stories_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- products
CREATE TABLE IF NOT EXISTS `products` (
id VARCHAR(191) NOT NULL,
  name TEXT NOT NULL,
  price DECIMAL(20,2) NOT NULL DEFAULT 0,
  unit TEXT NULL,
  description MEDIUMTEXT NULL,
  terms MEDIUMTEXT NULL,
  badge TEXT NULL,
  images JSON NULL,
  stock INT NULL,
  category_title TEXT NULL,
  image LONGTEXT NULL,
  category TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bk_invoice_messages
CREATE TABLE IF NOT EXISTS `bk_invoice_messages` (
id VARCHAR(191) NOT NULL,
  invoice_group_id VARCHAR(191) NULL,
  invoice_no TEXT NULL,
  invoice_page INT NOT NULL DEFAULT 1,
  invoice_pages INT NOT NULL DEFAULT 1,
  sender_role TEXT NULL,
  sender_token TEXT NULL,
  sender_name TEXT NULL,
  image_data LONGTEXT NULL,
  caption TEXT NULL,
  invoice_data JSON NULL,
  customer_token VARCHAR(191) NULL,
  created_ts BIGINT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'pending',
  status_updated_at DATETIME NULL,
  status_by VARCHAR(64) NULL,
  printed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_invoice_messages_created_ts (created_ts),
  KEY idx_invoice_messages_group (invoice_group_id),
  KEY idx_invoice_messages_customer_token (customer_token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bk_customers
CREATE TABLE IF NOT EXISTS `bk_customers` (
token VARCHAR(191) NOT NULL,
  phone VARCHAR(64) NULL,
  name TEXT NULL,
  address TEXT NULL,
  referrer_marketer_token VARCHAR(191) NULL,
  total_orders INT NOT NULL DEFAULT 0,
  total_amount DECIMAL(20,2) NOT NULL DEFAULT 0,
  last_order_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (token),
  KEY idx_bk_customers_phone (phone),
  KEY idx_bk_customers_referrer (referrer_marketer_token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bk_marketer_customers
CREATE TABLE IF NOT EXISTS `bk_marketer_customers` (
token VARCHAR(191) primary key,               
  marketer_token TEXT not null,
  marketer_name TEXT,                   
  code TEXT,                            
  name TEXT,
  phone TEXT,
  address TEXT,
  created_at DATETIME not null default CURRENT_TIMESTAMP,
  updated_at DATETIME not null default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bk_direct_voice_notes
CREATE TABLE IF NOT EXISTS `bk_direct_voice_notes` (
id VARCHAR(191) primary key,
  invoice_group_id TEXT not null,
  invoice_no TEXT,
  sender_role TEXT not null check (sender_role in ('admin','marketer')),
  sender_token TEXT not null,
  sender_name TEXT,
  audio_data LONGTEXT not null,
  created_ts BIGINT not null,
  created_at DATETIME not null default CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bk_public_invoices
CREATE TABLE IF NOT EXISTS `bk_public_invoices` (
token        VARCHAR(191) primary key,
  invoice_data JSON not null,
  created_at   DATETIME not null default CURRENT_TIMESTAMP,
  updated_at   DATETIME not null default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bk_admin_invites
CREATE TABLE IF NOT EXISTS `bk_admin_invites` (
token VARCHAR(191) primary key,
  created_by CHAR(36),
  created_at DATETIME not null default CURRENT_TIMESTAMP,
  expires_at DATETIME not null,
  used_by CHAR(36),
  used_at DATETIME,
  note TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bk_admin_activity_log
CREATE TABLE IF NOT EXISTS `bk_admin_activity_log` (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  actor CHAR(36),
  actor_email TEXT,
  action TEXT not null,
  detail JSON,
  created_at DATETIME not null default CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bk_agent_approvals
CREATE TABLE IF NOT EXISTS `bk_agent_approvals` (
id CHAR(36) primary key,
  requested_by CHAR(36) not null,
  action TEXT not null,
  payload JSON NULL,
  reason TEXT,
  status VARCHAR(32) not null default 'pending'
    check (status in ('pending','approved','rejected','executed','failed')),
  reviewed_by CHAR(36),
  reviewed_at DATETIME,
  executed_at DATETIME,
  result JSON,
  error_message TEXT,
  created_at DATETIME not null default CURRENT_TIMESTAMP,
  updated_at DATETIME not null default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bk_agent_audit
CREATE TABLE IF NOT EXISTS `bk_agent_audit` (
id CHAR(36) primary key,
  actor_id CHAR(36),
  event_type TEXT not null,
  action TEXT,
  approval_id CHAR(36),
  payload JSON NULL,
  success TINYINT(1) not null default true,
  created_at DATETIME not null default CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bk_agent_tasks
CREATE TABLE IF NOT EXISTS `bk_agent_tasks` (
id CHAR(36) primary key,
  created_by CHAR(36),
  title TEXT not null,
  description TEXT,
  status VARCHAR(32) not null default 'open'
    check (status in ('open','in_progress','done','cancelled')),
  due_at DATETIME,
  metadata JSON NULL,
  created_at DATETIME not null default CURRENT_TIMESTAMP,
  updated_at DATETIME not null default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bk_agent_content_drafts
CREATE TABLE IF NOT EXISTS `bk_agent_content_drafts` (
id CHAR(36) primary key,
  created_by CHAR(36),
  content_type TEXT not null check (content_type in ('product','page','article','banner')),
  target_id TEXT,
  title TEXT,
  body MEDIUMTEXT,
  metadata JSON NULL,
  status VARCHAR(32) not null default 'draft'
    check (status in ('draft','approved','published','rejected')),
  created_at DATETIME not null default CURRENT_TIMESTAMP,
  updated_at DATETIME not null default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bk_agent_memory
CREATE TABLE IF NOT EXISTS `bk_agent_memory` (
id CHAR(36) primary key,
  user_id CHAR(36) not null ,
  memory_key VARCHAR(191) not null,
  memory_value JSON NULL,
  scope VARCHAR(191) not null default 'agent',
  importance INT not null default 50 check (importance between 0 and 100),
  expires_at DATETIME,
  created_at DATETIME not null default CURRENT_TIMESTAMP,
  updated_at DATETIME not null default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  unique(user_id, memory_key, scope)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bk_agent_workflows
CREATE TABLE IF NOT EXISTS `bk_agent_workflows` (
id CHAR(36) primary key,
  owner_id CHAR(36) not null ,
  name TEXT not null,
  description TEXT,
  trigger_type VARCHAR(32) not null default 'manual'
    check (trigger_type in ('manual','daily','new_order')),
  enabled TINYINT(1) not null default true,
  definition JSON NULL,
  created_at DATETIME not null default CURRENT_TIMESTAMP,
  updated_at DATETIME not null default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bk_agent_workflow_runs
CREATE TABLE IF NOT EXISTS `bk_agent_workflow_runs` (
id CHAR(36) primary key,
  workflow_id CHAR(36) not null,
  owner_id CHAR(36) not null ,
  status VARCHAR(32) not null default 'queued'
    check (status in ('queued','running','waiting_approval','completed','failed','cancelled')),
  current_step INT not null default 0,
  context JSON NULL,
  result JSON NULL,
  error_message TEXT,
  trace_id TEXT,
  started_at DATETIME,
  finished_at DATETIME,
  created_at DATETIME not null default CURRENT_TIMESTAMP,
  updated_at DATETIME not null default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bk_agent_workflow_steps
CREATE TABLE IF NOT EXISTS `bk_agent_workflow_steps` (
id CHAR(36) primary key,
  run_id CHAR(36) not null,
  step_index INT not null,
  step_type TEXT not null,
  status VARCHAR(32) not null default 'queued'
    check (status in ('queued','running','waiting_approval','completed','failed','skipped')),
  input JSON NULL,
  output JSON NULL,
  approval_id CHAR(36),
  error_message TEXT,
  started_at DATETIME,
  finished_at DATETIME,
  created_at DATETIME not null default CURRENT_TIMESTAMP,
  unique(run_id, step_index)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bk_ai_usage
CREATE TABLE IF NOT EXISTS `bk_ai_usage` (
id CHAR(36) primary key,
  user_id CHAR(36) not null,
  action TEXT not null,
  model TEXT,
  created_at DATETIME not null default CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bk_security_events
CREATE TABLE IF NOT EXISTS `bk_security_events` (
id CHAR(36) primary key,
  event_type TEXT not null,
  severity VARCHAR(32) not null default 'info'
    check (severity in ('info','low','medium','high','critical')),
  source_ip TEXT,
  user_id CHAR(36),
  path TEXT,
  method TEXT,
  user_agent TEXT,
  metadata JSON NULL,
  fingerprint TEXT,
  created_at DATETIME not null default CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bk_security_incidents
CREATE TABLE IF NOT EXISTS `bk_security_incidents` (
id CHAR(36) primary key,
  title TEXT not null,
  severity VARCHAR(32) not null default 'medium'
    check (severity in ('low','medium','high','critical')),
  status VARCHAR(32) not null default 'open'
    check (status in ('open','investigating','contained','resolved','false_positive')),
  source_event_id CHAR(36),
  fingerprint TEXT,
  evidence JSON NULL,
  ai_analysis JSON NULL,
  containment JSON NULL,
  created_at DATETIME not null default CURRENT_TIMESTAMP,
  updated_at DATETIME not null default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  resolved_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bk_security_rules
CREATE TABLE IF NOT EXISTS `bk_security_rules` (
id CHAR(36) primary key,
  name TEXT not null,
  rule_type TEXT not null
    check (rule_type in ('rate_limit','ip_block','path_block','country_block','user_lock','bot_challenge')),
  enabled TINYINT(1) not null default true,
  action VARCHAR(32) not null default 'alert'
    check (action in ('alert','challenge','block')),
  threshold INT,
  window_seconds INT,
  `match` JSON NULL,
  expires_at DATETIME,
  created_at DATETIME not null default CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bk_security_heartbeats
CREATE TABLE IF NOT EXISTS `bk_security_heartbeats` (
id CHAR(36) primary key,
  client_id TEXT not null,
  user_id CHAR(36),
  page_path TEXT,
  app_version TEXT,
  last_seen_at DATETIME not null default CURRENT_TIMESTAMP,
  metadata JSON NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bk_firewall_buckets
CREATE TABLE IF NOT EXISTS `bk_firewall_buckets` (
bucket_key VARCHAR(191) primary key,
  window_start DATETIME not null,
  request_count INT not null default 0,
  blocked_count INT not null default 0,
  last_seen_at DATETIME not null default CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bk_firewall_blocks
CREATE TABLE IF NOT EXISTS `bk_firewall_blocks` (
id CHAR(36) primary key,
  fingerprint TEXT not null,
  reason TEXT not null,
  severity VARCHAR(32) not null default 'high'
    check (severity in ('medium','high','critical')),
  source_ip TEXT,
  path TEXT,
  expires_at DATETIME not null,
  created_at DATETIME not null default CURRENT_TIMESTAMP,
  active TINYINT(1) not null default true
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bk_firewall_alerts
CREATE TABLE IF NOT EXISTS `bk_firewall_alerts` (
id CHAR(36) primary key,
  fingerprint TEXT,
  title TEXT not null,
  severity TEXT not null,
  score INT not null default 0,
  evidence JSON NULL,
  created_at DATETIME not null default CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bk_soc_incidents
CREATE TABLE IF NOT EXISTS `bk_soc_incidents` (
id CHAR(36) primary key,
  fingerprint TEXT,
  category TEXT not null,
  severity TEXT not null check(severity in ('low','medium','high','critical')),
  status VARCHAR(32) not null default 'open' check(status in ('open','investigating','contained','resolved','false_positive')),
  title TEXT not null,
  score INT not null default 0,
  evidence JSON NULL,
  ai_analysis JSON NULL,
  response JSON NULL,
  created_at DATETIME not null default CURRENT_TIMESTAMP,
  updated_at DATETIME not null default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  resolved_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bk_soc_session_controls
CREATE TABLE IF NOT EXISTS `bk_soc_session_controls` (
session_fingerprint VARCHAR(191) primary key,
  action TEXT not null check(action in ('monitor','challenge','quarantine','block')),
  reason TEXT,
  expires_at DATETIME not null,
  evidence JSON NULL,
  created_at DATETIME not null default CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bk_soc_health
CREATE TABLE IF NOT EXISTS `bk_soc_health` (
id CHAR(36) primary key,
  component TEXT not null,
  status TEXT not null check(status in ('healthy','degraded','down','unknown')),
  latency_ms INT,
  details JSON NULL,
  checked_at DATETIME not null default CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bk_soc_policies
CREATE TABLE IF NOT EXISTS `bk_soc_policies` (
id CHAR(36) primary key,
  name TEXT not null,
  enabled TINYINT(1) not null default true,
  min_score INT not null default 80,
  response_action TEXT not null check(response_action in ('alert','challenge','quarantine','block')),
  duration_seconds INT not null default 900,
  categories JSON NULL,
  created_at DATETIME not null default CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bk_security_direct_messages
CREATE TABLE IF NOT EXISTS `bk_security_direct_messages` (
id CHAR(36) primary key,
  incident_id CHAR(36) null,
  severity VARCHAR(32) not null default 'high'
    check (severity in ('low','medium','high','critical')),
  title TEXT not null,
  message TEXT not null,
  source VARCHAR(64) not null default 'ai_security_guard',
  fingerprint TEXT,
  metadata JSON NULL,
  is_read TINYINT(1) not null default false,
  created_at DATETIME not null default CURRENT_TIMESTAMP,
  read_at DATETIME,
  read_by CHAR(36)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- bk_telegram_settings
CREATE TABLE IF NOT EXISTS `bk_telegram_settings` (
id TINYINT NOT NULL DEFAULT 1,
  bot_token TEXT NULL,
  chat_id TEXT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 0,
  allowed_user_ids JSON NULL,
  webhook_secret TEXT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  updated_by CHAR(36) NULL,
  PRIMARY KEY (id),
  CONSTRAINT chk_telegram_settings_singleton CHECK (id = 1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT IGNORE INTO bk_telegram_settings(id,enabled,allowed_user_ids) VALUES(1,0,JSON_ARRAY());

-- bk_telegram_bot_state
CREATE TABLE IF NOT EXISTS `bk_telegram_bot_state` (
telegram_user_id BIGINT NOT NULL PRIMARY KEY,
  state TEXT,
  context JSON NULL,
  updated_at DATETIME not null default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Useful indexes / cross-table relations that do not depend on Supabase Auth.
ALTER TABLE profiles ADD CONSTRAINT fk_profiles_user FOREIGN KEY (id) REFERENCES users(id) ON DELETE CASCADE;
ALTER TABLE orders ADD CONSTRAINT fk_orders_agent FOREIGN KEY (agent_id) REFERENCES users(id) ON DELETE SET NULL;
ALTER TABLE bk_admin_activity_log ADD CONSTRAINT fk_admin_log_actor FOREIGN KEY (actor) REFERENCES users(id) ON DELETE SET NULL;
ALTER TABLE bk_admin_invites ADD CONSTRAINT fk_invites_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL;
ALTER TABLE bk_admin_invites ADD CONSTRAINT fk_invites_used_by FOREIGN KEY (used_by) REFERENCES users(id) ON DELETE SET NULL;
ALTER TABLE bk_agent_approvals ADD CONSTRAINT fk_agent_approvals_requested_by FOREIGN KEY (requested_by) REFERENCES users(id) ON DELETE RESTRICT;
ALTER TABLE bk_agent_approvals ADD CONSTRAINT fk_agent_approvals_reviewed_by FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL;
ALTER TABLE bk_agent_audit ADD CONSTRAINT fk_agent_audit_actor FOREIGN KEY (actor_id) REFERENCES users(id) ON DELETE SET NULL;
ALTER TABLE bk_agent_tasks ADD CONSTRAINT fk_agent_tasks_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL;
ALTER TABLE bk_agent_content_drafts ADD CONSTRAINT fk_content_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL;
ALTER TABLE bk_ai_usage ADD CONSTRAINT fk_ai_usage_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
ALTER TABLE bk_security_direct_messages ADD CONSTRAINT fk_direct_read_by FOREIGN KEY (read_by) REFERENCES users(id) ON DELETE SET NULL;
ALTER TABLE bk_telegram_settings ADD CONSTRAINT fk_telegram_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL;

-- FKs that previously existed only as inline REFERENCES (kept here so they are not lost):
ALTER TABLE bk_agent_audit ADD CONSTRAINT fk_agent_audit_approval FOREIGN KEY (approval_id) REFERENCES bk_agent_approvals(id) ON DELETE SET NULL;
ALTER TABLE bk_agent_workflow_runs ADD CONSTRAINT fk_wf_runs_workflow FOREIGN KEY (workflow_id) REFERENCES bk_agent_workflows(id) ON DELETE CASCADE;
ALTER TABLE bk_agent_workflow_steps ADD CONSTRAINT fk_wf_steps_run FOREIGN KEY (run_id) REFERENCES bk_agent_workflow_runs(id) ON DELETE CASCADE;
ALTER TABLE bk_security_incidents ADD CONSTRAINT fk_sec_incidents_event FOREIGN KEY (source_event_id) REFERENCES bk_security_events(id) ON DELETE SET NULL;

SET FOREIGN_KEY_CHECKS=1;

-- NEXT STEP (outside this schema file): cPanel PHP API must implement authentication, authorization, and the Supabase RPC behaviors.
-- Do NOT expose database credentials in index.html.

-- cPanel API authentication sessions
CREATE TABLE IF NOT EXISTS `api_sessions` (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  token_hash CHAR(64) NOT NULL,
  user_id CHAR(36) NOT NULL,
  expires_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_api_sessions_token (token_hash),
  KEY idx_api_sessions_user (user_id),
  KEY idx_api_sessions_expiry (expires_at),
  CONSTRAINT fk_api_sessions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =====================================================================
-- BarKala v148 — پنل لینک‌های خصوصی بازاریاب + حساب سود + اعلام مغایرت ردیف فاکتور
-- Target: MariaDB 10.11 (ADD COLUMN IF NOT EXISTS / ADD INDEX IF NOT EXISTS).
-- این فایل «قابل اجرای مجدد» است و روی دیتابیس موجود v147 اجرا می‌شود.
-- (روی نصب تازه هم بعد از BarKala_CPanel_MySQL_v148.sql نیازی به اجرای جداگانه ندارد.)
-- =====================================================================
SET NAMES utf8mb4;

-- ---------- 1) لینک‌های خصوصی: نام پنل، تلفن بازاریاب، وضعیت و آمار بازدید ----------
ALTER TABLE `bk_marketer_customers`
  MODIFY COLUMN marketer_token VARCHAR(191) NOT NULL,
  ADD COLUMN IF NOT EXISTS panel_name VARCHAR(191) NULL,
  ADD COLUMN IF NOT EXISTS marketer_phone VARCHAR(64) NULL,
  ADD COLUMN IF NOT EXISTS link_status VARCHAR(16) NOT NULL DEFAULT 'active',
  ADD COLUMN IF NOT EXISTS open_count INT NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS last_opened_at DATETIME NULL,
  ADD INDEX IF NOT EXISTS idx_bmc_marketer (marketer_token);

-- ---------- 2) فاکتورها و سفارش‌ها: انتساب به لینک/بازاریاب ----------
ALTER TABLE `bk_invoice_messages`
  ADD COLUMN IF NOT EXISTS marketer_token VARCHAR(191) NULL,
  ADD COLUMN IF NOT EXISTS link_token VARCHAR(191) NULL,
  ADD COLUMN IF NOT EXISTS link_panel_name VARCHAR(191) NULL,
  ADD COLUMN IF NOT EXISTS order_source VARCHAR(32) NULL,
  ADD INDEX IF NOT EXISTS idx_invmsg_marketer (marketer_token),
  ADD INDEX IF NOT EXISTS idx_invmsg_link (link_token);

ALTER TABLE `marketer_orders`
  ADD COLUMN IF NOT EXISTS link_token VARCHAR(191) NULL,
  ADD COLUMN IF NOT EXISTS link_panel_name VARCHAR(191) NULL,
  ADD COLUMN IF NOT EXISTS order_source VARCHAR(32) NULL,
  ADD COLUMN IF NOT EXISTS profit_pct DECIMAL(5,2) NULL,
  ADD COLUMN IF NOT EXISTS profit_amount DECIMAL(20,2) NULL;

ALTER TABLE `bk_customers`
  ADD COLUMN IF NOT EXISTS referrer_link_token VARCHAR(191) NULL;

-- ---------- 3) ثبت‌نام سمت سرور بازاریاب‌ها (نام، تلفن، درصد سود) ----------
CREATE TABLE IF NOT EXISTS `bk_marketers` (
  token VARCHAR(191) NOT NULL,
  public_id VARCHAR(64) NOT NULL,
  full_name VARCHAR(191) NULL,
  phone VARCHAR(64) NULL,
  commission_pct DECIMAL(5,2) NULL,
  status VARCHAR(16) NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (token),
  UNIQUE KEY uq_bk_marketers_public (public_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `bk_kv_settings` (
  k VARCHAR(64) NOT NULL,
  v VARCHAR(255) NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (k)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- 4) دفتر سود بازاریاب: هر فاکتور یک ردیف، به نام بازاریاب صاحب لینک ----------
CREATE TABLE IF NOT EXISTS `bk_marketer_profit_ledger` (
  id CHAR(36) NOT NULL,
  marketer_token VARCHAR(191) NOT NULL,
  link_token VARCHAR(191) NULL,
  link_panel_name VARCHAR(191) NULL,
  order_source VARCHAR(32) NOT NULL DEFAULT 'marketer_direct',
  invoice_group_id VARCHAR(191) NOT NULL,
  invoice_no VARCHAR(64) NULL,
  customer_name VARCHAR(255) NULL,
  customer_phone VARCHAR(64) NULL,
  sale_amount DECIMAL(20,2) NOT NULL DEFAULT 0,
  profit_pct DECIMAL(5,2) NOT NULL DEFAULT 0,
  profit_amount DECIMAL(20,2) NOT NULL DEFAULT 0,
  status VARCHAR(16) NOT NULL DEFAULT 'pending',   -- pending | approved | paid | rejected | superseded
  admin_note TEXT NULL,
  paid_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_ledger_group (marketer_token, invoice_group_id),
  KEY idx_ledger_marketer (marketer_token),
  KEY idx_ledger_link (link_token),
  KEY idx_ledger_status (status),
  KEY idx_ledger_invno (invoice_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- 5) اعلام مغایرت مشتری روی ردیف‌های فاکتور (برگشت / کسری / اعتراض قیمت / عدم تحویل) ----------
CREATE TABLE IF NOT EXISTS `bk_invoice_line_flags` (
  id CHAR(36) NOT NULL,
  invoice_group_id VARCHAR(191) NOT NULL,
  invoice_no VARCHAR(64) NULL,
  line_index INT NOT NULL,
  item_name VARCHAR(255) NULL,
  item_qty DECIMAL(14,3) NULL,
  item_unit VARCHAR(64) NULL,
  unit_price DECIMAL(20,2) NULL,
  flag_type VARCHAR(24) NOT NULL,                  -- return | shortage | overprice | not_received
  qty_claimed DECIMAL(14,3) NULL,
  expected_price DECIMAL(20,2) NULL,
  note TEXT NULL,
  customer_token VARCHAR(191) NOT NULL,
  customer_name VARCHAR(255) NULL,
  customer_phone VARCHAR(64) NULL,
  marketer_token VARCHAR(191) NULL,
  link_token VARCHAR(191) NULL,
  link_panel_name VARCHAR(191) NULL,
  status VARCHAR(16) NOT NULL DEFAULT 'open',      -- open | seen | resolved | rejected
  resolved_by VARCHAR(16) NULL,
  admin_note TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_flag_line (customer_token, invoice_group_id, line_index, flag_type),
  KEY idx_flag_customer (customer_token),
  KEY idx_flag_marketer (marketer_token),
  KEY idx_flag_status (status),
  KEY idx_flag_invoice (invoice_group_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- v148b — گفتگوی هر اعلام مغایرت (متن + عکس)، رسید تحویل با کد، یادآور خرید مجدد
-- =====================================================================
CREATE TABLE IF NOT EXISTS `bk_flag_messages` (
  id CHAR(36) NOT NULL,
  flag_id CHAR(36) NOT NULL,
  invoice_group_id VARCHAR(191) NULL,
  author_role VARCHAR(16) NOT NULL,                -- customer | marketer | admin
  author_name VARCHAR(191) NULL,
  body TEXT NULL,
  image_data MEDIUMTEXT NULL,                      -- data:image/jpeg;base64,... (کوچک‌شده سمت مرورگر)
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_flagmsg_flag (flag_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `bk_delivery_receipts` (
  id CHAR(36) NOT NULL,
  invoice_group_id VARCHAR(191) NOT NULL,
  invoice_no VARCHAR(64) NULL,
  customer_token VARCHAR(191) NOT NULL,
  marketer_token VARCHAR(191) NULL,
  code VARCHAR(8) NOT NULL,
  attempts INT NOT NULL DEFAULT 0,
  status VARCHAR(16) NOT NULL DEFAULT 'pending',   -- pending | confirmed
  confirmed_by VARCHAR(16) NULL,                   -- marketer | admin
  confirmed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_delivery (customer_token, invoice_group_id),
  KEY idx_delivery_group (invoice_group_id),
  KEY idx_delivery_marketer (marketer_token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `bk_reorder_snooze` (
  marketer_token VARCHAR(191) NOT NULL,
  rkey CHAR(40) NOT NULL,
  until_at DATETIME NOT NULL,
  PRIMARY KEY (marketer_token, rkey)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- v148c — برند هر لینک (رنگ/لوگو)، سطح‌بندی و پاداش پلکانی بازاریاب‌ها
-- =====================================================================
ALTER TABLE `bk_marketer_customers`
  ADD COLUMN IF NOT EXISTS brand_color VARCHAR(16) NULL,
  ADD COLUMN IF NOT EXISTS brand_logo MEDIUMTEXT NULL,
  ADD COLUMN IF NOT EXISTS brand_tagline VARCHAR(120) NULL;

ALTER TABLE `bk_kv_settings` MODIFY COLUMN v TEXT NULL;

CREATE TABLE IF NOT EXISTS `bk_marketer_bonuses` (
  id CHAR(36) NOT NULL,
  marketer_token VARCHAR(191) NOT NULL,
  period VARCHAR(8) NOT NULL,                      -- ماه شمسی، مثل 1405-07
  level_name VARCHAR(64) NULL,
  sales DECIMAL(20,2) NOT NULL DEFAULT 0,
  bonus_pct DECIMAL(5,2) NOT NULL DEFAULT 0,
  bonus_amount DECIMAL(20,2) NOT NULL DEFAULT 0,
  status VARCHAR(16) NOT NULL DEFAULT 'pending',   -- pending | approved | paid | rejected
  admin_note TEXT NULL,
  paid_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_bonus_period (marketer_token, period),
  KEY idx_bonus_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- v148d — امنیت: محدودیت نرخ درخواست، لاگ حسابرسی
-- =====================================================================
CREATE TABLE IF NOT EXISTS `bk_rate_limits` (
  k CHAR(40) NOT NULL,
  win INT NOT NULL,
  hits INT NOT NULL DEFAULT 0,
  PRIMARY KEY (k),
  KEY idx_rl_win (win)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `bk_audit_log` (
  id CHAR(36) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actor_role VARCHAR(16) NULL,
  actor_ref VARCHAR(191) NULL,
  action VARCHAR(64) NOT NULL,
  outcome VARCHAR(16) NOT NULL DEFAULT 'ok',       -- ok | denied | error | blocked
  detail TEXT NULL,
  ip VARCHAR(64) NULL,
  ua VARCHAR(255) NULL,
  PRIMARY KEY (id),
  KEY idx_audit_time (created_at),
  KEY idx_audit_action (action, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- =====================================================================
-- BarKala v149 — ماژول حسابداری پیشرفته (صفحهٔ مستقل accounting.html)
-- Target: MariaDB 10.6+ / MySQL 8. قابل اجرای مجدد (IF NOT EXISTS / INSERT IGNORE).
-- =====================================================================
SET NAMES utf8mb4;

-- سرفصل حساب‌ها (کدینگ)
CREATE TABLE IF NOT EXISTS `acc_accounts` (
  code VARCHAR(16) NOT NULL,
  title VARCHAR(191) NOT NULL,
  type VARCHAR(16) NOT NULL,                 -- asset | liability | equity | revenue | expense
  parent_code VARCHAR(16) NULL,
  is_system TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (code),
  KEY idx_acc_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- بانک و صندوق
CREATE TABLE IF NOT EXISTS `acc_bank_accounts` (
  id CHAR(36) NOT NULL,
  title VARCHAR(191) NOT NULL,
  kind VARCHAR(16) NOT NULL DEFAULT 'bank',  -- bank | cash | pos
  account_no VARCHAR(64) NULL,
  gl_code VARCHAR(16) NOT NULL DEFAULT '1110',
  opening_balance DECIMAL(20,2) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- اسناد حسابداری
CREATE TABLE IF NOT EXISTS `acc_journal_entries` (
  id CHAR(36) NOT NULL,
  entry_no INT NOT NULL AUTO_INCREMENT,
  entry_date DATE NOT NULL,
  description VARCHAR(500) NULL,
  source VARCHAR(24) NOT NULL DEFAULT 'manual', -- manual | receipt | payment | expense | cheque | ai
  source_id VARCHAR(64) NULL,
  status VARCHAR(12) NOT NULL DEFAULT 'posted', -- draft | posted | void
  created_by CHAR(36) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_entry_no (entry_no),
  KEY idx_je_date (entry_date),
  KEY idx_je_source (source, source_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `acc_journal_lines` (
  id BIGINT NOT NULL AUTO_INCREMENT,
  entry_id CHAR(36) NOT NULL,
  account_code VARCHAR(16) NOT NULL,
  debit DECIMAL(20,2) NOT NULL DEFAULT 0,
  credit DECIMAL(20,2) NOT NULL DEFAULT 0,
  memo VARCHAR(255) NULL,
  PRIMARY KEY (id),
  KEY idx_jl_entry (entry_id),
  KEY idx_jl_account (account_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- دریافت و پرداخت
CREATE TABLE IF NOT EXISTS `acc_payments` (
  id CHAR(36) NOT NULL,
  direction VARCHAR(8) NOT NULL,             -- in (دریافت) | out (پرداخت)
  pay_date DATE NOT NULL,
  amount DECIMAL(20,2) NOT NULL,
  method VARCHAR(16) NOT NULL DEFAULT 'transfer', -- cash | transfer | card | cheque
  party_name VARCHAR(191) NULL,
  party_type VARCHAR(16) NULL,               -- customer | marketer | supplier | other
  order_id CHAR(36) NULL,
  bank_account_id CHAR(36) NULL,
  reference_no VARCHAR(64) NULL,
  note VARCHAR(500) NULL,
  created_by CHAR(36) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_pay_date (pay_date),
  KEY idx_pay_order (order_id),
  KEY idx_pay_party (party_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- چک‌ها
CREATE TABLE IF NOT EXISTS `acc_cheques` (
  id CHAR(36) NOT NULL,
  direction VARCHAR(8) NOT NULL,             -- in (دریافتی) | out (پرداختی)
  cheque_no VARCHAR(64) NOT NULL,
  bank_name VARCHAR(100) NULL,
  party_name VARCHAR(191) NULL,
  amount DECIMAL(20,2) NOT NULL,
  issue_date DATE NULL,
  due_date DATE NOT NULL,
  status VARCHAR(16) NOT NULL DEFAULT 'pending', -- pending | cleared | bounced | returned
  bank_account_id CHAR(36) NULL,
  note VARCHAR(500) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_chq_due (due_date),
  KEY idx_chq_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- هزینه‌ها
CREATE TABLE IF NOT EXISTS `acc_expenses` (
  id CHAR(36) NOT NULL,
  exp_date DATE NOT NULL,
  category VARCHAR(64) NOT NULL,
  account_code VARCHAR(16) NOT NULL DEFAULT '6900',
  amount DECIMAL(20,2) NOT NULL,
  vendor VARCHAR(191) NULL,
  bank_account_id CHAR(36) NULL,
  note VARCHAR(500) NULL,
  created_by CHAR(36) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_exp_date (exp_date),
  KEY idx_exp_cat (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- پیشنهادهای هوش مصنوعی که منتظر تأیید حسابدارند
CREATE TABLE IF NOT EXISTS `acc_ai_suggestions` (
  id CHAR(36) NOT NULL,
  kind VARCHAR(24) NOT NULL,                 -- insight | journal_draft | alert
  title VARCHAR(255) NOT NULL,
  body MEDIUMTEXT NULL,
  payload JSON NULL,
  status VARCHAR(12) NOT NULL DEFAULT 'new', -- new | accepted | dismissed
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ais_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- کدینگ پیش‌فرض
INSERT IGNORE INTO acc_accounts(code,title,type,is_system) VALUES
('1100','صندوق','asset',1),('1110','بانک‌ها','asset',1),('1200','چک‌های دریافتنی','asset',1),
('1300','حساب‌های دریافتنی (مشتریان)','asset',1),('1400','موجودی کالا','asset',1),
('2100','حساب‌های پرداختنی (تأمین‌کنندگان)','liability',1),('2200','چک‌های پرداختنی','liability',1),
('2300','کارمزد و پاداش بازاریاب‌ها (پرداختنی)','liability',1),('2400','مالیات و عوارض پرداختنی','liability',1),
('3100','سرمایه','equity',1),('3200','سود و زیان انباشته','equity',1),
('4100','درآمد فروش','revenue',1),('4900','سایر درآمدها','revenue',1),
('5100','بهای تمام‌شده کالای فروش‌رفته','expense',1),
('6100','حقوق و دستمزد','expense',1),('6200','اجاره','expense',1),('6300','حمل و ارسال','expense',1),
('6400','تبلیغات و بازاریابی','expense',1),('6500','قبوض و انرژی','expense',1),('6600','کارمزد بانکی','expense',1),
('6700','کارمزد و پاداش بازاریاب‌ها','expense',1),('6900','سایر هزینه‌ها','expense',1);

INSERT IGNORE INTO acc_bank_accounts(id,title,kind,gl_code,opening_balance)
VALUES ('00000000-0000-4000-8000-000000000001','صندوق اصلی','cash','1100',0);

INSERT IGNORE INTO bk_kv_settings(k,v) VALUES ('acc_ai_enabled','1');