-- CloseYourChallan.com — schema (MySQL 5.7+ / MariaDB 10.3+)
-- All money columns are BIGINT paise. Never FLOAT.
-- Charset utf8mb4 throughout so Indian-language content and names survive.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------- identity
CREATE TABLE IF NOT EXISTS users (
  id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id         VARCHAR(40)  NOT NULL,
  name              VARCHAR(120) NOT NULL,
  mobile            VARCHAR(15)  NOT NULL,
  mobile_verified_at DATETIME NULL,
  email             VARCHAR(190) NULL,
  email_verified_at DATETIME NULL,
  password_hash     VARCHAR(255) NULL,
  status            VARCHAR(20)  NOT NULL DEFAULT 'active',
  last_login_at     DATETIME NULL,
  deleted_at        DATETIME NULL,
  created_at        DATETIME NOT NULL,
  updated_at        DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_public (public_id),
  UNIQUE KEY uq_users_mobile (mobile),
  UNIQUE KEY uq_users_email (email),
  KEY ix_users_status (status),
  KEY ix_users_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_vehicles (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id      VARCHAR(40) NOT NULL,
  user_id        BIGINT UNSIGNED NOT NULL,
  vehicle_number VARCHAR(16) NOT NULL,
  nickname       VARCHAR(60) NULL,
  is_primary     TINYINT(1) NOT NULL DEFAULT 0,
  deleted_at     DATETIME NULL,
  created_at     DATETIME NOT NULL,
  updated_at     DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_vehicle_public (public_id),
  UNIQUE KEY uq_user_vehicle (user_id, vehicle_number),
  KEY ix_vehicle_number (vehicle_number),
  CONSTRAINT fk_vehicle_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------- geography + rule catalog
CREATE TABLE IF NOT EXISTS states (
  id                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code               VARCHAR(4)  NOT NULL,
  name               VARCHAR(80) NOT NULL,
  is_enabled         TINYINT(1) NOT NULL DEFAULT 0,
  service_available  TINYINT(1) NOT NULL DEFAULT 0,
  provider_code      VARCHAR(40) NULL,
  expert_cta_default TINYINT(1) NOT NULL DEFAULT 1,
  notes              TEXT NULL,
  created_at         DATETIME NOT NULL,
  updated_at         DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_state_code (code),
  KEY ix_state_enabled (is_enabled, service_available)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cities (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  state_id   BIGINT UNSIGNED NOT NULL,
  name       VARCHAR(80) NOT NULL,
  is_active  TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_city (state_id, name),
  CONSTRAINT fk_city_state FOREIGN KEY (state_id) REFERENCES states(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS challan_types (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code        VARCHAR(40)  NOT NULL,
  name        VARCHAR(120) NOT NULL,
  description TEXT NULL,
  is_active   TINYINT(1) NOT NULL DEFAULT 1,
  created_at  DATETIME NOT NULL,
  updated_at  DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_type_code (code),
  KEY ix_type_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- The eligibility rule table. A rule with challan_type_id NULL is the
-- state-wide default; a rule naming a type is more specific and wins.
CREATE TABLE IF NOT EXISTS eligibility_rules (
  id                         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  state_id                   BIGINT UNSIGNED NOT NULL,
  challan_type_id            BIGINT UNSIGNED NULL,
  action                     VARCHAR(40) NOT NULL,
  physical_presence_required TINYINT(1) NOT NULL DEFAULT 0,
  expert_cta_enabled         TINYINT(1) NOT NULL DEFAULT 1,
  customer_message           TEXT NULL,
  service_fee_paise          BIGINT NULL,
  priority                   INT NOT NULL DEFAULT 0,
  version                    INT NOT NULL DEFAULT 1,
  effective_from             DATETIME NULL,
  effective_to               DATETIME NULL,
  is_active                  TINYINT(1) NOT NULL DEFAULT 1,
  created_by                 BIGINT UNSIGNED NULL,
  created_at                 DATETIME NOT NULL,
  updated_at                 DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY ix_rule_lookup (state_id, challan_type_id, is_active, priority),
  CONSTRAINT fk_rule_state FOREIGN KEY (state_id) REFERENCES states(id) ON DELETE CASCADE,
  CONSTRAINT fk_rule_type  FOREIGN KEY (challan_type_id) REFERENCES challan_types(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------- challan data
CREATE TABLE IF NOT EXISTS vehicle_challans (
  id                       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id                VARCHAR(40) NOT NULL,
  vehicle_id               BIGINT UNSIGNED NULL,
  user_id                  BIGINT UNSIGNED NULL,
  external_id              VARCHAR(120) NULL,
  challan_number           VARCHAR(80)  NOT NULL,
  vehicle_number           VARCHAR(16)  NOT NULL,
  state_code               VARCHAR(4)   NOT NULL,
  city                     VARCHAR(80)  NULL,
  location                 VARCHAR(190) NULL,
  offence_code             VARCHAR(40)  NOT NULL DEFAULT 'UNCLASSIFIED',
  offence_name             VARCHAR(190) NULL,
  violation_code           VARCHAR(40)  NULL,
  challan_datetime         DATETIME NULL,
  original_amount          BIGINT NOT NULL DEFAULT 0,
  discount_amount          BIGINT NOT NULL DEFAULT 0,
  discount_verified        TINYINT(1) NOT NULL DEFAULT 0,
  discount_verified_by     BIGINT UNSIGNED NULL,
  payable_amount           BIGINT NOT NULL DEFAULT 0,
  status                   VARCHAR(30) NOT NULL DEFAULT 'pending',
  court_status             VARCHAR(30) NOT NULL DEFAULT 'not_in_court',
  source                   VARCHAR(40) NULL,
  eligibility_action       VARCHAR(40) NULL,
  eligibility_rule_id      BIGINT UNSIGNED NULL,
  eligibility_rule_version INT NULL,
  first_fetched_at         DATETIME NULL,
  last_fetched_at          DATETIME NULL,
  created_at               DATETIME NOT NULL,
  updated_at               DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_challan_public (public_id),
  UNIQUE KEY uq_challan_vehicle_number (vehicle_number, challan_number),
  KEY ix_challan_vehicle (vehicle_number),
  KEY ix_challan_state (state_code),
  KEY ix_challan_status (status),
  KEY ix_challan_external (external_id),
  KEY ix_challan_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS challan_refresh_logs (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  vehicle_number VARCHAR(16) NOT NULL,
  user_id        BIGINT UNSIGNED NULL,
  vehicle_id     BIGINT UNSIGNED NULL,
  provider       VARCHAR(40) NOT NULL,
  trigger_source VARCHAR(40) NOT NULL,
  status         VARCHAR(30) NOT NULL,
  error_category VARCHAR(40) NULL,
  response_ms    INT NOT NULL DEFAULT 0,
  challan_count  INT NOT NULL DEFAULT 0,
  ip             VARCHAR(45) NULL,
  requested_at   DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY ix_refresh_vehicle (vehicle_number, requested_at),
  KEY ix_refresh_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------ orders/money
CREATE TABLE IF NOT EXISTS orders (
  id                   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id            VARCHAR(40) NOT NULL,
  user_id              BIGINT UNSIGNED NOT NULL,
  vehicle_id           BIGINT UNSIGNED NULL,
  vehicle_number       VARCHAR(16) NOT NULL,
  status               VARCHAR(30) NOT NULL DEFAULT 'awaiting_payment',
  currency             VARCHAR(3)  NOT NULL DEFAULT 'INR',
  original_amount      BIGINT NOT NULL DEFAULT 0,
  government_discount  BIGINT NOT NULL DEFAULT 0,
  government_payable   BIGINT NOT NULL DEFAULT 0,
  service_fee          BIGINT NOT NULL DEFAULT 0,
  coupon_id            BIGINT UNSIGNED NULL,
  coupon_code          VARCHAR(40) NULL,
  coupon_discount      BIGINT NOT NULL DEFAULT 0,
  tax_amount           BIGINT NOT NULL DEFAULT 0,
  final_amount         BIGINT NOT NULL DEFAULT 0,
  service_fee_snapshot BIGINT NOT NULL DEFAULT 0,
  item_count           INT NOT NULL DEFAULT 0,
  paid_at              DATETIME NULL,
  refunded_amount      BIGINT NOT NULL DEFAULT 0,
  created_at           DATETIME NOT NULL,
  updated_at           DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_order_public (public_id),
  KEY ix_order_user (user_id, status),
  KEY ix_order_status (status),
  KEY ix_order_created (created_at),
  CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Immutable snapshot: these columns are written once at purchase and never
-- updated by a challan refresh.
CREATE TABLE IF NOT EXISTS order_items (
  id                       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id                VARCHAR(40) NOT NULL,
  order_id                 BIGINT UNSIGNED NOT NULL,
  challan_id               BIGINT UNSIGNED NULL,
  challan_number           VARCHAR(80) NOT NULL,
  vehicle_number           VARCHAR(16) NOT NULL,
  state_code               VARCHAR(4)  NOT NULL,
  city                     VARCHAR(80) NULL,
  location                 VARCHAR(190) NULL,
  offence_code             VARCHAR(40) NULL,
  offence_name             VARCHAR(190) NULL,
  challan_datetime         DATETIME NULL,
  original_amount          BIGINT NOT NULL DEFAULT 0,
  government_discount      BIGINT NOT NULL DEFAULT 0,
  government_payable       BIGINT NOT NULL DEFAULT 0,
  service_fee              BIGINT NOT NULL DEFAULT 0,
  coupon_discount          BIGINT NOT NULL DEFAULT 0,
  tax_amount               BIGINT NOT NULL DEFAULT 0,
  item_total               BIGINT NOT NULL DEFAULT 0,
  lawyer_cost              BIGINT NOT NULL DEFAULT 0,
  other_cost               BIGINT NOT NULL DEFAULT 0,
  eligibility_action       VARCHAR(40) NULL,
  eligibility_rule_id      BIGINT UNSIGNED NULL,
  eligibility_rule_version INT NULL,
  status                   VARCHAR(40) NOT NULL DEFAULT 'pending_payment',
  closed_at                DATETIME NULL,
  created_at               DATETIME NOT NULL,
  updated_at               DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_item_public (public_id),
  KEY ix_item_order (order_id),
  KEY ix_item_challan (challan_id),
  KEY ix_item_status (status),
  KEY ix_item_state (state_code),
  CONSTRAINT fk_item_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payments (
  id                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id          VARCHAR(40) NOT NULL,
  order_id           BIGINT UNSIGNED NOT NULL,
  user_id            BIGINT UNSIGNED NOT NULL,
  gateway            VARCHAR(30) NOT NULL,
  gateway_order_id   VARCHAR(80) NOT NULL,
  gateway_payment_id VARCHAR(80) NULL,
  amount             BIGINT NOT NULL,
  currency           VARCHAR(3) NOT NULL DEFAULT 'INR',
  status             VARCHAR(20) NOT NULL DEFAULT 'created',
  verified_source    VARCHAR(30) NULL,
  failure_reason     VARCHAR(80) NULL,
  refund_amount      BIGINT NOT NULL DEFAULT 0,
  paid_at            DATETIME NULL,
  created_at         DATETIME NOT NULL,
  updated_at         DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_payment_public (public_id),
  KEY ix_payment_gateway_order (gateway_order_id),
  KEY ix_payment_order (order_id, status),
  CONSTRAINT fk_payment_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Unique event_id is what makes webhook processing idempotent.
CREATE TABLE IF NOT EXISTS webhook_events (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  gateway      VARCHAR(30) NOT NULL,
  event_id     VARCHAR(120) NOT NULL,
  event_type   VARCHAR(60) NOT NULL,
  status       VARCHAR(40) NOT NULL DEFAULT 'received',
  payload      MEDIUMTEXT NULL,
  processed_at DATETIME NULL,
  created_at   DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_webhook_event (gateway, event_id),
  KEY ix_webhook_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS coupons (
  id                     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code                   VARCHAR(40) NOT NULL,
  description            VARCHAR(190) NULL,
  discount_type          VARCHAR(10) NOT NULL DEFAULT 'fixed',
  discount_value         BIGINT NOT NULL DEFAULT 0,
  max_discount_paise     BIGINT NOT NULL DEFAULT 0,
  min_order_paise        BIGINT NOT NULL DEFAULT 0,
  max_uses_total         INT NOT NULL DEFAULT 0,
  max_uses_per_user      INT NOT NULL DEFAULT 1,
  first_order_only       TINYINT(1) NOT NULL DEFAULT 0,
  applicable_state_codes VARCHAR(190) NULL,
  starts_at              DATETIME NULL,
  ends_at                DATETIME NULL,
  is_active              TINYINT(1) NOT NULL DEFAULT 1,
  created_at             DATETIME NOT NULL,
  updated_at             DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_coupon_code (code),
  KEY ix_coupon_active (is_active, starts_at, ends_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS coupon_usage (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  coupon_id      BIGINT UNSIGNED NOT NULL,
  user_id        BIGINT UNSIGNED NULL,
  order_id       BIGINT UNSIGNED NOT NULL,
  discount_paise BIGINT NOT NULL DEFAULT 0,
  created_at     DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_usage_order (order_id),
  KEY ix_usage_coupon (coupon_id, user_id),
  CONSTRAINT fk_usage_coupon FOREIGN KEY (coupon_id) REFERENCES coupons(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------- lawyers
CREATE TABLE IF NOT EXISTS lawyers (
  id                     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id              VARCHAR(40) NOT NULL,
  name                   VARCHAR(120) NOT NULL,
  mobile                 VARCHAR(15) NOT NULL,
  email                  VARCHAR(190) NOT NULL,
  password_hash          VARCHAR(255) NOT NULL,
  state_code             VARCHAR(4) NOT NULL,
  additional_state_codes VARCHAR(120) NULL,
  city                   VARCHAR(80) NULL,
  bar_council_number     VARCHAR(80) NULL,
  bar_council_state      VARCHAR(80) NULL,
  enrolment_year         INT NULL,
  status                 VARCHAR(20) NOT NULL DEFAULT 'pending',
  is_accepting           TINYINT(1) NOT NULL DEFAULT 1,
  rejection_reason       VARCHAR(255) NULL,
  approved_by            BIGINT UNSIGNED NULL,
  approved_at            DATETIME NULL,
  last_login_at          DATETIME NULL,
  created_at             DATETIME NOT NULL,
  updated_at             DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_lawyer_public (public_id),
  UNIQUE KEY uq_lawyer_email (email),
  UNIQUE KEY uq_lawyer_mobile (mobile),
  KEY ix_lawyer_routing (status, is_accepting, state_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS lawyer_documents (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  lawyer_id     BIGINT UNSIGNED NOT NULL,
  document_type VARCHAR(40) NOT NULL,
  storage_id    VARCHAR(64) NOT NULL,
  mime_type     VARCHAR(80) NULL,
  byte_size     BIGINT NOT NULL DEFAULT 0,
  sha256        CHAR(64) NULL,
  aad           VARCHAR(190) NULL,
  created_at    DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY ix_lawyerdoc (lawyer_id),
  CONSTRAINT fk_lawyerdoc FOREIGN KEY (lawyer_id) REFERENCES lawyers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS lawyer_assignments (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_item_id   BIGINT UNSIGNED NOT NULL,
  lawyer_id       BIGINT UNSIGNED NOT NULL,
  assigned_by     BIGINT UNSIGNED NULL,
  assignment_mode VARCHAR(20) NOT NULL DEFAULT 'auto',
  state_code      VARCHAR(4) NOT NULL,
  status          VARCHAR(20) NOT NULL DEFAULT 'active',
  assigned_at     DATETIME NOT NULL,
  ended_at        DATETIME NULL,
  PRIMARY KEY (id),
  KEY ix_assign_item (order_item_id, status),
  KEY ix_assign_lawyer (lawyer_id, status),
  CONSTRAINT fk_assign_item FOREIGN KEY (order_item_id) REFERENCES order_items(id) ON DELETE CASCADE,
  CONSTRAINT fk_assign_lawyer FOREIGN KEY (lawyer_id) REFERENCES lawyers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS lawyer_updates (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_item_id  BIGINT UNSIGNED NOT NULL,
  lawyer_id      BIGINT UNSIGNED NULL,
  staff_id       BIGINT UNSIGNED NULL,
  status         VARCHAR(40) NOT NULL,
  note           TEXT NULL,
  customer_visible TINYINT(1) NOT NULL DEFAULT 1,
  expected_cost  BIGINT NULL,
  actual_cost    BIGINT NULL,
  court_date     DATE NULL,
  created_at     DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY ix_update_item (order_item_id, created_at),
  CONSTRAINT fk_update_item FOREIGN KEY (order_item_id) REFERENCES order_items(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------------------------- documents
CREATE TABLE IF NOT EXISTS user_documents (
  id                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id          VARCHAR(40) NOT NULL,
  user_id            BIGINT UNSIGNED NOT NULL,
  order_item_id      BIGINT UNSIGNED NOT NULL,
  document_type      VARCHAR(40) NOT NULL,
  storage_id         VARCHAR(64) NOT NULL,
  storage_driver     VARCHAR(20) NOT NULL DEFAULT 'local',
  original_extension VARCHAR(8) NULL,
  mime_type          VARCHAR(80) NULL,
  byte_size          BIGINT NOT NULL DEFAULT 0,
  sha256             CHAR(64) NULL,
  encryption         VARCHAR(20) NOT NULL DEFAULT 'aes-256-gcm',
  aad                VARCHAR(190) NULL,
  shared_with_lawyer TINYINT(1) NOT NULL DEFAULT 0,
  status             VARCHAR(20) NOT NULL DEFAULT 'received',
  deleted_at         DATETIME NULL,
  created_at         DATETIME NOT NULL,
  updated_at         DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_doc_public (public_id),
  UNIQUE KEY uq_doc_storage (storage_id),
  KEY ix_doc_owner (user_id, order_item_id),
  KEY ix_doc_item (order_item_id, document_type),
  CONSTRAINT fk_doc_item FOREIGN KEY (order_item_id) REFERENCES order_items(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS document_access_logs (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  document_id BIGINT UNSIGNED NOT NULL,
  actor_type  VARCHAR(20) NOT NULL,
  actor_id    BIGINT UNSIGNED NULL,
  action      VARCHAR(20) NOT NULL,
  ip          VARCHAR(45) NULL,
  created_at  DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY ix_docaccess (document_id, created_at),
  KEY ix_docaccess_actor (actor_type, actor_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------ staff + rbac
CREATE TABLE IF NOT EXISTS roles (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code        VARCHAR(40) NOT NULL,
  name        VARCHAR(80) NOT NULL,
  is_system   TINYINT(1) NOT NULL DEFAULT 0,
  created_at  DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_role_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS permissions (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code        VARCHAR(60) NOT NULL,
  description VARCHAR(190) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_perm_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS role_permissions (
  role_id       BIGINT UNSIGNED NOT NULL,
  permission_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (role_id, permission_id),
  CONSTRAINT fk_rp_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
  CONSTRAINT fk_rp_perm FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS support_users (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id     VARCHAR(40) NOT NULL,
  name          VARCHAR(120) NOT NULL,
  email         VARCHAR(190) NOT NULL,
  mobile        VARCHAR(15) NULL,
  password_hash VARCHAR(255) NOT NULL,
  role_id       BIGINT UNSIGNED NOT NULL,
  status        VARCHAR(20) NOT NULL DEFAULT 'active',
  last_login_at DATETIME NULL,
  created_by    BIGINT UNSIGNED NULL,
  created_at    DATETIME NOT NULL,
  updated_at    DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_staff_public (public_id),
  UNIQUE KEY uq_staff_email (email),
  KEY ix_staff_role (role_id, status),
  CONSTRAINT fk_staff_role FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------- tickets/chat
CREATE TABLE IF NOT EXISTS support_tickets (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id      VARCHAR(40) NOT NULL,
  requester_type VARCHAR(20) NOT NULL DEFAULT 'customer',
  user_id        BIGINT UNSIGNED NULL,
  lawyer_id      BIGINT UNSIGNED NULL,
  order_item_id  BIGINT UNSIGNED NULL,
  challan_id     BIGINT UNSIGNED NULL,
  category       VARCHAR(40) NOT NULL DEFAULT 'general',
  subject        VARCHAR(190) NOT NULL,
  priority       VARCHAR(10) NOT NULL DEFAULT 'normal',
  status         VARCHAR(20) NOT NULL DEFAULT 'open',
  assigned_staff BIGINT UNSIGNED NULL,
  created_at     DATETIME NOT NULL,
  updated_at     DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_ticket_public (public_id),
  KEY ix_ticket_status (status, priority),
  KEY ix_ticket_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS support_messages (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  ticket_id  BIGINT UNSIGNED NOT NULL,
  actor_type VARCHAR(20) NOT NULL,
  actor_id   BIGINT UNSIGNED NULL,
  body       TEXT NOT NULL,
  is_internal TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY ix_msg_ticket (ticket_id, created_at),
  CONSTRAINT fk_msg_ticket FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------------------------- content
CREATE TABLE IF NOT EXISTS pages (
  id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  slug             VARCHAR(120) NOT NULL,
  title            VARCHAR(190) NOT NULL,
  body_html        MEDIUMTEXT NULL,
  banner_image     VARCHAR(190) NULL,
  is_published     TINYINT(1) NOT NULL DEFAULT 1,
  is_system        TINYINT(1) NOT NULL DEFAULT 0,
  seo_title        VARCHAR(190) NULL,
  seo_description  VARCHAR(300) NULL,
  seo_canonical    VARCHAR(190) NULL,
  seo_robots       VARCHAR(40) NULL DEFAULT 'index,follow',
  og_image         VARCHAR(190) NULL,
  created_at       DATETIME NOT NULL,
  updated_at       DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_page_slug (slug),
  KEY ix_page_pub (is_published)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS blog_categories (
  id   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  slug VARCHAR(120) NOT NULL,
  name VARCHAR(120) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_cat_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS blog_posts (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  slug            VARCHAR(160) NOT NULL,
  title           VARCHAR(190) NOT NULL,
  excerpt         VARCHAR(400) NULL,
  body_html       MEDIUMTEXT NULL,
  featured_image  VARCHAR(190) NULL,
  category_id     BIGINT UNSIGNED NULL,
  author_name     VARCHAR(120) NULL,
  status          VARCHAR(20) NOT NULL DEFAULT 'draft',
  published_at    DATETIME NULL,
  seo_title       VARCHAR(190) NULL,
  seo_description VARCHAR(300) NULL,
  created_at      DATETIME NOT NULL,
  updated_at      DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_post_slug (slug),
  KEY ix_post_pub (status, published_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS blog_tags (
  id   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  slug VARCHAR(120) NOT NULL,
  name VARCHAR(120) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_tag_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS blog_post_tags (
  post_id BIGINT UNSIGNED NOT NULL,
  tag_id  BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (post_id, tag_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS email_templates (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code       VARCHAR(60) NOT NULL,
  name       VARCHAR(120) NOT NULL,
  subject    VARCHAR(190) NOT NULL,
  body_html  MEDIUMTEXT NOT NULL,
  variables  VARCHAR(400) NULL,
  is_system  TINYINT(1) NOT NULL DEFAULT 0,
  is_active  TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_tpl_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS notifications (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  channel        VARCHAR(20) NOT NULL DEFAULT 'email',
  template_code  VARCHAR(60) NOT NULL,
  recipient      VARCHAR(190) NOT NULL,
  user_id        BIGINT UNSIGNED NULL,
  provider       VARCHAR(30) NULL,
  provider_ref   VARCHAR(120) NULL,
  subject        VARCHAR(190) NULL,
  status         VARCHAR(20) NOT NULL DEFAULT 'queued',
  attempts       INT NOT NULL DEFAULT 0,
  failure_reason VARCHAR(255) NULL,
  dedupe_key     VARCHAR(120) NULL,
  sent_at        DATETIME NULL,
  created_at     DATETIME NOT NULL,
  updated_at     DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY ix_notif_status (status, attempts),
  KEY ix_notif_dedupe (dedupe_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------ settings + audit
CREATE TABLE IF NOT EXISTS settings (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  setting_key VARCHAR(120) NOT NULL,
  value_text  MEDIUMTEXT NULL,
  value_type  VARCHAR(10) NOT NULL DEFAULT 'string',
  is_secret   TINYINT(1) NOT NULL DEFAULT 0,
  group_name  VARCHAR(40) NOT NULL DEFAULT 'general',
  created_at  DATETIME NOT NULL,
  updated_at  DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_setting_key (setting_key),
  KEY ix_setting_group (group_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS audit_logs (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  actor_type  VARCHAR(20) NOT NULL,
  actor_id    BIGINT UNSIGNED NULL,
  action      VARCHAR(80) NOT NULL,
  target_type VARCHAR(40) NULL,
  target_id   VARCHAR(190) NULL,
  before_json MEDIUMTEXT NULL,
  after_json  MEDIUMTEXT NULL,
  ip          VARCHAR(45) NULL,
  user_agent  VARCHAR(255) NULL,
  created_at  DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY ix_audit_action (action, created_at),
  KEY ix_audit_actor (actor_type, actor_id),
  KEY ix_audit_target (target_type, target_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS otp_logs (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  mobile_hash     CHAR(64) NOT NULL,
  mobile_last4    VARCHAR(4) NOT NULL,
  code_hash       CHAR(64) NOT NULL,
  purpose         VARCHAR(30) NOT NULL,
  status          VARCHAR(20) NOT NULL DEFAULT 'pending',
  delivery_status VARCHAR(20) NULL,
  provider        VARCHAR(30) NULL,
  provider_ref    VARCHAR(120) NULL,
  attempts        INT NOT NULL DEFAULT 0,
  expires_at      DATETIME NOT NULL,
  verified_at     DATETIME NULL,
  ip              VARCHAR(45) NULL,
  created_at      DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY ix_otp_lookup (mobile_hash, purpose, status),
  KEY ix_otp_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS login_logs (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  guard      VARCHAR(20) NOT NULL,
  identifier VARCHAR(190) NOT NULL,
  success    TINYINT(1) NOT NULL DEFAULT 0,
  reason     VARCHAR(60) NULL,
  ip         VARCHAR(45) NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY ix_login_identity (guard, identifier, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS rate_limits (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  rl_key       VARCHAR(190) NOT NULL,
  window_start BIGINT NOT NULL,
  hits         INT NOT NULL DEFAULT 0,
  expires_at   BIGINT NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_rl (rl_key, window_start),
  KEY ix_rl_expiry (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS api_logs (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  provider       VARCHAR(40) NOT NULL,
  operation      VARCHAR(60) NOT NULL,
  status_code    INT NULL,
  error_category VARCHAR(40) NULL,
  response_ms    INT NOT NULL DEFAULT 0,
  created_at     DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY ix_apilog (provider, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS expert_requests (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id     VARCHAR(40) NOT NULL,
  user_id       BIGINT UNSIGNED NULL,
  challan_id    BIGINT UNSIGNED NULL,
  vehicle_number VARCHAR(16) NOT NULL,
  state_code    VARCHAR(4) NOT NULL,
  reason_code   VARCHAR(40) NOT NULL,
  contact_mobile VARCHAR(15) NULL,
  ticket_id     BIGINT UNSIGNED NULL,
  assigned_lawyer_id BIGINT UNSIGNED NULL,
  status        VARCHAR(20) NOT NULL DEFAULT 'open',
  created_at    DATETIME NOT NULL,
  updated_at    DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_expert_public (public_id),
  KEY ix_expert_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------- analytics
CREATE TABLE IF NOT EXISTS page_views (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  path          VARCHAR(190) NOT NULL,
  visitor_hash  CHAR(32) NOT NULL,
  referrer_host VARCHAR(120) NULL,
  device_type   VARCHAR(12) NULL,
  utm_source    VARCHAR(80) NULL,
  utm_medium    VARCHAR(80) NULL,
  utm_campaign  VARCHAR(80) NULL,
  viewed_on     DATE NOT NULL,
  created_at    DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY ix_pv_day (viewed_on),
  KEY ix_pv_visitor (visitor_hash, viewed_on)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS funnel_events (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  step        VARCHAR(40) NOT NULL,
  user_id     BIGINT UNSIGNED NULL,
  occurred_on DATE NOT NULL,
  created_at  DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY ix_funnel_day (occurred_on, step)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS analytics_daily (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  stat_date       DATE NOT NULL,
  page_views      INT NOT NULL DEFAULT 0,
  unique_visitors INT NOT NULL DEFAULT 0,
  funnel_json     TEXT NULL,
  created_at      DATETIME NOT NULL,
  updated_at      DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_analytics_day (stat_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;