-- Tabel fitur lanjutan: notifications, whatsapp_logs, documents

CREATE TABLE IF NOT EXISTS `notifications` (
  `id`          VARCHAR(36)  NOT NULL,
  `user_id`     VARCHAR(36)  NOT NULL,
  `type`        ENUM('new_client','listing_sold','commission_paid','whatsapp_message','document_signed','document_ready') NOT NULL,
  `title`       VARCHAR(255) NOT NULL,
  `message`     TEXT         NOT NULL,
  `data`        JSON         NULL,
  `is_read`     TINYINT(1)   NOT NULL DEFAULT 0,
  `read_at`     DATETIME(6)  NULL,
  `created_at`  DATETIME(6)  NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  KEY `IDX_notifications_user_id` (`user_id`),
  CONSTRAINT `FK_notifications_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `whatsapp_logs` (
  `id`           VARCHAR(36)  NOT NULL,
  `user_id`      VARCHAR(36)  NOT NULL,
  `client_id`    VARCHAR(36)  NOT NULL,
  `phone_number` VARCHAR(30)  NOT NULL,
  `message`      TEXT         NOT NULL,
  `direction`    ENUM('outgoing','incoming') NOT NULL DEFAULT 'outgoing',
  `sent_at`      DATETIME(6)  NOT NULL,
  `created_at`   DATETIME(6)  NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  KEY `IDX_whatsapp_logs_user_id` (`user_id`),
  KEY `IDX_whatsapp_logs_client_id` (`client_id`),
  CONSTRAINT `FK_whatsapp_logs_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_whatsapp_logs_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `documents` (
  `id`                   VARCHAR(36)  NOT NULL,
  `user_id`              VARCHAR(36)  NOT NULL,
  `client_id`            VARCHAR(36)  NULL,
  `listing_id`           VARCHAR(36)  NULL,
  `type`                 ENUM('AJB','PPJB','SURAT_KUASA') NOT NULL,
  `status`               ENUM('draft','pending_sign','signed','expired') NOT NULL DEFAULT 'draft',
  `file_url`             VARCHAR(500) NULL,
  `file_name`            VARCHAR(255) NULL,
  `template_data`        JSON         NULL,
  `signature_request_id` VARCHAR(100) NULL,
  `signed_at`            DATETIME(6)  NULL,
  `expired_at`           DATETIME(6)  NULL,
  `created_at`           DATETIME(6)  NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updated_at`           DATETIME(6)  NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  KEY `IDX_documents_user_id` (`user_id`),
  KEY `IDX_documents_client_id` (`client_id`),
  KEY `IDX_documents_listing_id` (`listing_id`),
  CONSTRAINT `FK_documents_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_documents_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE SET NULL,
  CONSTRAINT `FK_documents_listing` FOREIGN KEY (`listing_id`) REFERENCES `listings` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;