-- ============================================================
-- Gedoong CRM — Database Schema (Production)
-- Jalankan via phpMyAdmin atau MySQL CLI setelah buat database
-- ============================================================

CREATE DATABASE IF NOT EXISTS gedoong
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE gedoong;

-- ------------------------------------------------------------
-- users
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `id`          VARCHAR(36)  NOT NULL,
  `email`       VARCHAR(255) NOT NULL,
  `password`    VARCHAR(255) NOT NULL,
  `name`        VARCHAR(255) NOT NULL,
  `phone`       VARCHAR(255) NULL,
  `role`        ENUM('agent','admin') NOT NULL DEFAULT 'agent',
  `is_active`   TINYINT(1)   NOT NULL DEFAULT 1,
  `deleted_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`),
  UNIQUE KEY `UQ_users_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- listings
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `listings` (
  `id`             VARCHAR(36)     NOT NULL,
  `title`          VARCHAR(255)    NOT NULL,
  `description`    TEXT            NULL,
  `address`        VARCHAR(255)    NOT NULL,
  `city`           VARCHAR(255)    NULL,
  `province`       VARCHAR(255)    NULL,
  `postal_code`    VARCHAR(255)    NULL,
  `price`          DECIMAL(15,2)   NOT NULL,
  `bedroom`        INT             NULL,
  `bathroom`       INT             NULL,
  `land_area`      DECIMAL(10,2)   NULL,
  `building_area`  DECIMAL(10,2)   NULL,
  `listing_type`   ENUM('sale','rent') NOT NULL,
  `status`         ENUM('active','sold','off_market') NOT NULL DEFAULT 'active',
  `images`         JSON            NULL,
  `video_url`      VARCHAR(255)    NULL,
  `views`          INT             NOT NULL DEFAULT 0,
  `is_featured`    TINYINT(1)      NOT NULL DEFAULT 0,
  `agent_id`       VARCHAR(36)     NOT 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_listings_agent_id` (`agent_id`),
  CONSTRAINT `FK_listings_agent` FOREIGN KEY (`agent_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- clients
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `clients` (
  `id`          VARCHAR(36)  NOT NULL,
  `name`        VARCHAR(255) NOT NULL,
  `phone`       VARCHAR(255) NOT NULL,
  `email`       VARCHAR(255) NULL,
  `source`      ENUM('referral','social_media','website','walk_in','other') NOT NULL DEFAULT 'other',
  `stage`       ENUM('prospect','show','negotiation','closing','lost') NOT NULL DEFAULT 'prospect',
  `notes`       TEXT         NULL,
  `listing_id`  VARCHAR(36)  NULL,
  `agent_id`    VARCHAR(36)  NOT 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_clients_agent_id` (`agent_id`),
  KEY `IDX_clients_listing_id` (`listing_id`),
  CONSTRAINT `FK_clients_agent`   FOREIGN KEY (`agent_id`)   REFERENCES `users`    (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_clients_listing` FOREIGN KEY (`listing_id`) REFERENCES `listings` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- commissions
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `commissions` (
  `id`          VARCHAR(36)    NOT NULL,
  `listing_id`  VARCHAR(36)    NOT NULL,
  `agent_id`    VARCHAR(36)    NOT NULL,
  `client_id`   VARCHAR(36)    NULL,
  `amount`      DECIMAL(15,2)  NOT NULL,
  `percentage`  DECIMAL(5,2)   NOT NULL,
  `status`      ENUM('pending','paid','overdue') NOT NULL DEFAULT 'pending',
  `due_date`    DATE           NOT NULL,
  `paid_date`   DATE           NULL,
  `notes`       TEXT           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_commissions_listing_id` (`listing_id`),
  KEY `IDX_commissions_agent_id`   (`agent_id`),
  KEY `IDX_commissions_client_id`  (`client_id`),
  CONSTRAINT `FK_commissions_listing` FOREIGN KEY (`listing_id`) REFERENCES `listings` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_commissions_agent`   FOREIGN KEY (`agent_id`)   REFERENCES `users`    (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_commissions_client`  FOREIGN KEY (`client_id`)  REFERENCES `clients`  (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;