CREATE TABLE IF NOT EXISTS sales_leads (
  id BIGSERIAL PRIMARY KEY,
  lead_id TEXT NOT NULL UNIQUE,
  tenant_id TEXT NOT NULL,
  source_type TEXT NOT NULL,
  source_channel TEXT NOT NULL,
  source_campaign_id TEXT,
  full_name TEXT NOT NULL,
  company_name TEXT,
  phone TEXT,
  email TEXT,
  messenger_handles_json TEXT NOT NULL DEFAULT '{}',
  lead_temperature TEXT NOT NULL,
  lead_score DOUBLE PRECISION NOT NULL DEFAULT 50,
  customer_type TEXT,
  segment_type TEXT,
  initial_need_summary TEXT,
  preferred_channel TEXT NOT NULL,
  assigned_agent_type TEXT NOT NULL,
  status TEXT NOT NULL,
  crm_customer_id TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_sales_leads_tenant_status_score ON sales_leads(tenant_id, status, lead_score);
CREATE INDEX IF NOT EXISTS idx_sales_leads_phone_email ON sales_leads(phone, email);

CREATE TABLE IF NOT EXISTS sales_deals (
  id BIGSERIAL PRIMARY KEY,
  deal_id TEXT NOT NULL UNIQUE,
  tenant_id TEXT NOT NULL,
  lead_id TEXT,
  customer_id TEXT,
  pipeline_id TEXT NOT NULL DEFAULT 'sales_default',
  stage_id TEXT NOT NULL,
  scenario_type TEXT NOT NULL,
  priority INTEGER NOT NULL DEFAULT 3,
  title TEXT NOT NULL,
  need_summary TEXT,
  product_context_json TEXT NOT NULL DEFAULT '{}',
  estimated_amount DOUBLE PRECISION,
  final_amount DOUBLE PRECISION,
  currency TEXT NOT NULL DEFAULT 'KZT',
  payment_model TEXT,
  document_required BOOLEAN NOT NULL DEFAULT TRUE,
  payment_required BOOLEAN NOT NULL DEFAULT TRUE,
  assigned_human_user_id TEXT,
  assigned_ai_orchestrator_id TEXT,
  preferred_channel TEXT NOT NULL,
  current_channel TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'active',
  won_reason TEXT,
  lost_reason TEXT,
  close_reason TEXT,
  next_action_type TEXT,
  next_action_at TEXT,
  last_contact_at TEXT,
  closed_at TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_sales_deals_tenant_stage_status ON sales_deals(tenant_id, stage_id, status);
CREATE INDEX IF NOT EXISTS idx_sales_deals_customer_channel ON sales_deals(customer_id, current_channel);
CREATE INDEX IF NOT EXISTS idx_sales_deals_next_action ON sales_deals(next_action_at, status);

CREATE TABLE IF NOT EXISTS sales_communication_sessions (
  id BIGSERIAL PRIMARY KEY,
  communication_id TEXT NOT NULL UNIQUE,
  tenant_id TEXT NOT NULL,
  deal_id TEXT NOT NULL,
  lead_id TEXT,
  customer_id TEXT,
  channel_type TEXT NOT NULL,
  direction TEXT NOT NULL,
  agent_type TEXT NOT NULL,
  started_at TEXT NOT NULL,
  ended_at TEXT,
  duration_sec INTEGER,
  subject TEXT,
  status TEXT NOT NULL DEFAULT 'active',
  summary TEXT,
  transcript_id TEXT,
  next_action_type TEXT,
  next_action_at TEXT,
  sentiment TEXT,
  result_code TEXT,
  metadata_json TEXT NOT NULL DEFAULT '{}',
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_sales_comm_deal_started ON sales_communication_sessions(deal_id, started_at);
CREATE INDEX IF NOT EXISTS idx_sales_comm_channel_status ON sales_communication_sessions(channel_type, status);

CREATE TABLE IF NOT EXISTS sales_messages (
  id BIGSERIAL PRIMARY KEY,
  message_id TEXT NOT NULL UNIQUE,
  tenant_id TEXT NOT NULL,
  deal_id TEXT NOT NULL,
  communication_id TEXT NOT NULL,
  sender_type TEXT NOT NULL,
  sender_id TEXT,
  channel_provider TEXT NOT NULL,
  external_message_id TEXT,
  body TEXT NOT NULL,
  attachments_json TEXT NOT NULL DEFAULT '[]',
  delivery_status TEXT,
  read_status TEXT,
  message_metadata_json TEXT NOT NULL DEFAULT '{}',
  sent_at TEXT NOT NULL,
  created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_sales_messages_deal_sent ON sales_messages(deal_id, sent_at);
CREATE INDEX IF NOT EXISTS idx_sales_messages_ext_id ON sales_messages(channel_provider, external_message_id);

CREATE TABLE IF NOT EXISTS sales_calls (
  id BIGSERIAL PRIMARY KEY,
  call_id TEXT NOT NULL UNIQUE,
  tenant_id TEXT NOT NULL,
  deal_id TEXT NOT NULL,
  communication_id TEXT NOT NULL,
  phone_number TEXT NOT NULL,
  direction TEXT NOT NULL,
  provider TEXT NOT NULL,
  external_call_id TEXT,
  recording_url TEXT,
  transcript_status TEXT NOT NULL DEFAULT 'pending',
  transcript_id TEXT,
  call_status TEXT NOT NULL DEFAULT 'started',
  summary TEXT,
  started_at TEXT NOT NULL,
  ended_at TEXT,
  duration_sec INTEGER,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_sales_calls_deal_started ON sales_calls(deal_id, started_at);
CREATE INDEX IF NOT EXISTS idx_sales_calls_ext_provider ON sales_calls(provider, external_call_id);

CREATE TABLE IF NOT EXISTS sales_transcripts (
  id BIGSERIAL PRIMARY KEY,
  transcript_id TEXT NOT NULL UNIQUE,
  tenant_id TEXT NOT NULL,
  call_id TEXT NOT NULL,
  language TEXT NOT NULL DEFAULT 'ru',
  transcript_text TEXT NOT NULL,
  diarization_json TEXT NOT NULL DEFAULT '{}',
  extracted_entities_json TEXT NOT NULL DEFAULT '{}',
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS sales_notes (
  id BIGSERIAL PRIMARY KEY,
  note_id TEXT NOT NULL UNIQUE,
  tenant_id TEXT NOT NULL,
  deal_id TEXT NOT NULL,
  author_type TEXT NOT NULL,
  author_id TEXT,
  note_type TEXT NOT NULL,
  content TEXT NOT NULL,
  created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS sales_offers (
  id BIGSERIAL PRIMARY KEY,
  offer_id TEXT NOT NULL UNIQUE,
  tenant_id TEXT NOT NULL,
  deal_id TEXT NOT NULL,
  offer_type TEXT NOT NULL,
  title TEXT NOT NULL,
  description TEXT,
  line_items_json TEXT NOT NULL DEFAULT '[]',
  pricing_json TEXT NOT NULL DEFAULT '{}',
  total_amount DOUBLE PRECISION NOT NULL DEFAULT 0,
  currency TEXT NOT NULL DEFAULT 'KZT',
  validity_until TEXT,
  status TEXT NOT NULL DEFAULT 'draft',
  rendered_document_url TEXT,
  created_by_type TEXT NOT NULL DEFAULT 'text_ai',
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_sales_offers_deal_status ON sales_offers(deal_id, status);

CREATE TABLE IF NOT EXISTS sales_conditions (
  id BIGSERIAL PRIMARY KEY,
  condition_id TEXT NOT NULL UNIQUE,
  tenant_id TEXT NOT NULL,
  deal_id TEXT NOT NULL,
  product_name TEXT,
  service_name TEXT,
  quantity DOUBLE PRECISION,
  unit TEXT,
  delivery_mode TEXT,
  execution_date TEXT,
  start_date TEXT,
  end_date TEXT,
  payment_terms TEXT,
  custom_terms_json TEXT NOT NULL DEFAULT '{}',
  agreed_price DOUBLE PRECISION,
  currency TEXT NOT NULL DEFAULT 'KZT',
  confirmed_at TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS sales_counterparties (
  id BIGSERIAL PRIMARY KEY,
  counterparty_id TEXT NOT NULL UNIQUE,
  tenant_id TEXT NOT NULL,
  deal_id TEXT NOT NULL UNIQUE,
  customer_id TEXT,
  company_name TEXT,
  full_name TEXT,
  bin_iin TEXT,
  address TEXT,
  bank_details_json TEXT NOT NULL DEFAULT '{}',
  signer_name TEXT,
  signer_role TEXT,
  signer_basis TEXT,
  email_for_docs TEXT,
  phone_for_docs TEXT,
  completeness_status TEXT NOT NULL DEFAULT 'draft',
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS sales_documents (
  id BIGSERIAL PRIMARY KEY,
  document_id TEXT NOT NULL UNIQUE,
  tenant_id TEXT NOT NULL,
  deal_id TEXT NOT NULL,
  customer_id TEXT,
  document_type TEXT NOT NULL,
  template_id TEXT,
  version INTEGER NOT NULL DEFAULT 1,
  status TEXT NOT NULL DEFAULT 'draft',
  file_url TEXT,
  rendered_payload_json TEXT NOT NULL DEFAULT '{}',
  external_sign_provider_id TEXT,
  signed_at TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_sales_documents_deal_status ON sales_documents(deal_id, status);

CREATE TABLE IF NOT EXISTS sales_invoices (
  id BIGSERIAL PRIMARY KEY,
  invoice_id TEXT NOT NULL UNIQUE,
  tenant_id TEXT NOT NULL,
  deal_id TEXT NOT NULL,
  customer_id TEXT,
  invoice_number TEXT NOT NULL UNIQUE,
  basis_document_id TEXT,
  amount DOUBLE PRECISION NOT NULL DEFAULT 0,
  currency TEXT NOT NULL DEFAULT 'KZT',
  due_date TEXT,
  status TEXT NOT NULL DEFAULT 'draft',
  payment_link TEXT,
  line_items_json TEXT NOT NULL DEFAULT '[]',
  metadata_json TEXT NOT NULL DEFAULT '{}',
  issued_at TEXT,
  paid_at TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_sales_invoices_deal_status_due ON sales_invoices(deal_id, status, due_date);

CREATE TABLE IF NOT EXISTS sales_payments (
  id BIGSERIAL PRIMARY KEY,
  payment_id TEXT NOT NULL UNIQUE,
  tenant_id TEXT NOT NULL,
  deal_id TEXT NOT NULL,
  invoice_id TEXT,
  payment_provider TEXT,
  external_payment_id TEXT,
  amount DOUBLE PRECISION NOT NULL DEFAULT 0,
  currency TEXT NOT NULL DEFAULT 'KZT',
  status TEXT NOT NULL DEFAULT 'pending',
  paid_at TEXT,
  payment_method TEXT,
  failure_reason TEXT,
  metadata_json TEXT NOT NULL DEFAULT '{}',
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_sales_payments_deal_status ON sales_payments(deal_id, status);

CREATE TABLE IF NOT EXISTS sales_stage_history (
  id BIGSERIAL PRIMARY KEY,
  history_id TEXT NOT NULL UNIQUE,
  tenant_id TEXT NOT NULL,
  deal_id TEXT NOT NULL,
  from_stage_id TEXT,
  to_stage_id TEXT NOT NULL,
  changed_by_type TEXT NOT NULL,
  changed_by_id TEXT,
  reason TEXT,
  changed_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_sales_stage_history_deal_changed ON sales_stage_history(deal_id, changed_at);

CREATE TABLE IF NOT EXISTS sales_escalations (
  id BIGSERIAL PRIMARY KEY,
  escalation_id TEXT NOT NULL UNIQUE,
  tenant_id TEXT NOT NULL,
  deal_id TEXT NOT NULL,
  escalation_type TEXT NOT NULL,
  reason TEXT NOT NULL,
  severity TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'open',
  assigned_to_user_id TEXT,
  created_at TEXT NOT NULL,
  resolved_at TEXT
);
CREATE INDEX IF NOT EXISTS idx_sales_escalations_deal_status ON sales_escalations(deal_id, status);

CREATE TABLE IF NOT EXISTS sales_automation_tasks (
  id BIGSERIAL PRIMARY KEY,
  task_id TEXT NOT NULL UNIQUE,
  tenant_id TEXT NOT NULL,
  deal_id TEXT NOT NULL,
  task_type TEXT NOT NULL,
  payload_json TEXT NOT NULL DEFAULT '{}',
  run_at TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending',
  retry_count INTEGER NOT NULL DEFAULT 0,
  last_error TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_sales_tasks_run_status ON sales_automation_tasks(run_at, status);

CREATE TABLE IF NOT EXISTS sales_channel_switches (
  id BIGSERIAL PRIMARY KEY,
  switch_id TEXT NOT NULL UNIQUE,
  tenant_id TEXT NOT NULL,
  deal_id TEXT NOT NULL,
  communication_id TEXT,
  from_channel TEXT NOT NULL,
  to_channel TEXT NOT NULL,
  reason_for_channel_switch TEXT NOT NULL,
  switched_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_sales_channel_switches_deal_switched ON sales_channel_switches(deal_id, switched_at);