CREATE TABLE IF NOT EXISTS cities (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  plate_code VARCHAR(10) NULL,
  UNIQUE KEY uq_cities_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE IF NOT EXISTS districts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  city_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  UNIQUE KEY uq_district_city_name (city_id, name),
  CONSTRAINT fk_districts_city FOREIGN KEY (city_id) REFERENCES cities(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE IF NOT EXISTS neighborhoods (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  district_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  UNIQUE KEY uq_neighborhood_district_name (district_id, name),
  CONSTRAINT fk_neighborhoods_district FOREIGN KEY (district_id) REFERENCES districts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE IF NOT EXISTS portfolios (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  portfolio_no VARCHAR(50) NOT NULL UNIQUE,
  consultant_id BIGINT UNSIGNED NOT NULL,
  type ENUM('sale','rent') NOT NULL,
  category VARCHAR(100) NOT NULL,
  city_id BIGINT UNSIGNED NULL,
  district_id BIGINT UNSIGNED NULL,
  neighborhood_id BIGINT UNSIGNED NULL,
  address TEXT NULL,
  block_no VARCHAR(50) NULL,
  parcel_no VARCHAR(50) NULL,
  map_url VARCHAR(500) NULL,
  owner_name VARCHAR(150) NULL,
  owner_phone VARCHAR(30) NULL,
  owner_email VARCHAR(190) NULL,
  owner_address TEXT NULL,
  price DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  currency CHAR(3) NOT NULL DEFAULT 'TRY',
  status ENUM('draft','active','sold','rented','archived') NOT NULL DEFAULT 'draft',
  description TEXT NULL,
  published_at DATETIME NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL,
  INDEX idx_portfolios_consultant (consultant_id),
  INDEX idx_portfolios_status (status),
  INDEX idx_portfolios_location (city_id, district_id, neighborhood_id),
  INDEX idx_portfolios_type_category (type, category),
  INDEX idx_portfolios_price (price),
  CONSTRAINT fk_portfolios_consultant FOREIGN KEY (consultant_id) REFERENCES users(id),
  CONSTRAINT fk_portfolios_city FOREIGN KEY (city_id) REFERENCES cities(id) ON DELETE SET NULL,
  CONSTRAINT fk_portfolios_district FOREIGN KEY (district_id) REFERENCES districts(id) ON DELETE SET NULL,
  CONSTRAINT fk_portfolios_neighborhood FOREIGN KEY (neighborhood_id) REFERENCES neighborhoods(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE IF NOT EXISTS portfolio_photos (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  portfolio_id BIGINT UNSIGNED NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  is_cover TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_portfolio_photos_portfolio (portfolio_id),
  CONSTRAINT fk_portfolio_photos_portfolio FOREIGN KEY (portfolio_id) REFERENCES portfolios(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE IF NOT EXISTS portfolio_workflows (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  portfolio_id BIGINT UNSIGNED NOT NULL,
  step ENUM('received','photo_taken','banner_installed','published','sold','rented') NOT NULL,
  status ENUM('pending','completed','skipped') NOT NULL DEFAULT 'pending',
  completed_by BIGINT UNSIGNED NULL,
  completed_at DATETIME NULL,
  note TEXT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_portfolio_workflow_step (portfolio_id, step),
  INDEX idx_portfolio_workflows_status (status),
  CONSTRAINT fk_portfolio_workflows_portfolio FOREIGN KEY (portfolio_id) REFERENCES portfolios(id) ON DELETE CASCADE,
  CONSTRAINT fk_portfolio_workflows_user FOREIGN KEY (completed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;