
-- v7 Enterprise Refactor (FULL)
-- MariaDB 10.11 compatible
-- Safe + idempotent

CREATE TABLE IF NOT EXISTS users (
  user_id BIGINT NOT NULL,
  mode VARCHAR(10) NOT NULL DEFAULT 'buyer',
  phone VARCHAR(20) NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id),
  INDEX idx_users_mode (mode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sellers profile extra fields
ALTER TABLE sellers
  ADD COLUMN IF NOT EXISTS city VARCHAR(50) NULL,
  ADD COLUMN IF NOT EXISTS card_number VARCHAR(32) NULL,
  ADD COLUMN IF NOT EXISTS card_owner VARCHAR(120) NULL,
  ADD COLUMN IF NOT EXISTS phone VARCHAR(20) NULL;

-- Flowers optional fields
ALTER TABLE flowers
  ADD COLUMN IF NOT EXISTS category VARCHAR(50) NULL,
  ADD COLUMN IF NOT EXISTS normalized_name VARCHAR(255) NULL,
  ADD COLUMN IF NOT EXISTS is_active TINYINT NOT NULL DEFAULT 1,
  ADD COLUMN IF NOT EXISTS created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP;

-- Needs & offers (if not exists)
CREATE TABLE IF NOT EXISTS needs (
  id BIGINT NOT NULL AUTO_INCREMENT,
  buyer_id BIGINT NOT NULL,
  text TEXT NOT NULL,
  photo_file_id VARCHAR(255) NULL,
  city VARCHAR(50) NULL,
  budget INT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'open',
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_needs_status (status),
  INDEX idx_needs_buyer (buyer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS need_offers (
  id BIGINT NOT NULL AUTO_INCREMENT,
  need_id BIGINT NOT NULL,
  seller_id BIGINT NOT NULL,
  photo_file_id VARCHAR(255) NOT NULL,
  price INT NOT NULL,
  caption TEXT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_need_offers_need (need_id),
  INDEX idx_need_offers_seller (seller_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS offer_chats (
  id BIGINT NOT NULL AUTO_INCREMENT,
  offer_id BIGINT NOT NULL,
  buyer_id BIGINT NOT NULL,
  seller_id BIGINT NOT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_offer_chats_offer (offer_id),
  INDEX idx_offer_chats_buyer (buyer_id),
  INDEX idx_offer_chats_seller (seller_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS offer_chat_messages (
  id BIGINT NOT NULL AUTO_INCREMENT,
  chat_id BIGINT NOT NULL,
  sender VARCHAR(20) NOT NULL,
  message TEXT NOT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_offer_chat_msgs_chat (chat_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tickets
CREATE TABLE IF NOT EXISTS tickets (
  id INT NOT NULL AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  subject VARCHAR(255) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'open',
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_tickets_user (user_id),
  INDEX idx_tickets_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ticket_messages (
  id INT NOT NULL AUTO_INCREMENT,
  ticket_id INT NOT NULL,
  sender VARCHAR(20) NOT NULL,
  message TEXT NOT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_ticket_msgs_ticket (ticket_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Commission payments
CREATE TABLE IF NOT EXISTS commission_payments (
  id INT NOT NULL AUTO_INCREMENT,
  order_id BIGINT NOT NULL,
  total_price INT NOT NULL,
  commission_amount INT NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_commission_order (order_id),
  INDEX idx_commission_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

