-- ShopToDo / DealShelf schema
-- MySQL 8+ / MariaDB 10.4+
-- Charset/Collation

CREATE TABLE IF NOT EXISTS stores_list (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  store_name VARCHAR(190) NOT NULL,
  store_address VARCHAR(255) NOT NULL,
  store_geo_location VARCHAR(255) NULL, -- added for API spec
  store_lat DECIMAL(10,7) NULL,
  store_lng DECIMAL(10,7) NULL,
  store_logo VARCHAR(500) NULL,
  sort_order BIGINT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_stores_user_id (user_id),
  UNIQUE KEY uq_store_unique (user_id, store_name, store_address),
  CONSTRAINT fk_stores_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS items_list (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  store_id BIGINT UNSIGNED NOT NULL,
  item_name VARCHAR(190) NOT NULL,
  item_price DECIMAL(10,2) NULL,
  discount_amount DECIMAL(10,2) NULL,
  item_quantity DECIMAL(10,3) NULL,
  item_unit VARCHAR(30) NULL,
  total_price DECIMAL(10,2) NULL,
  contains_per_item DECIMAL(10,3) NULL,
  contains_per_item_unit VARCHAR(30) NULL,
  item_location_aisle VARCHAR(120) NULL,
  item_rating INT NULL,
  item_sort_order BIGINT UNSIGNED NOT NULL DEFAULT 0,
  status INT NOT NULL DEFAULT 1, -- 1=active,0=completed/archived
  item_sale_expiration_date DATETIME NULL,
  notes TEXT NULL, -- added for API spec
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_items_user_id (user_id),
  KEY idx_items_store_id (store_id),
  UNIQUE KEY uq_item_store_name (store_id, item_name),
  CONSTRAINT fk_items_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_items_store FOREIGN KEY (store_id) REFERENCES stores_list(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS item_images (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  item_id BIGINT UNSIGNED NOT NULL,
  image_thumb VARCHAR(500) NULL,
  image_original VARCHAR(500) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_item_images_item_id (item_id),
  CONSTRAINT fk_item_images_item FOREIGN KEY (item_id) REFERENCES items_list(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS deal_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  store_id BIGINT UNSIGNED NOT NULL,
  item_name VARCHAR(190) NOT NULL,
  item_price DECIMAL(10,2) NULL,
  discount_amount DECIMAL(10,2) NULL,
  item_quantity DECIMAL(10,3) NULL,
  item_unit VARCHAR(30) NULL,
  total_price DECIMAL(10,2) NULL,
  contains_per_item DECIMAL(10,3) NULL,
  contains_per_item_unit VARCHAR(30) NULL,
  item_location_aisle VARCHAR(120) NULL,
  item_rating INT NULL,
  item_sort_order BIGINT UNSIGNED NOT NULL DEFAULT 0,
  status INT NOT NULL DEFAULT 1, -- 1=active,0=expired/disabled
  item_sale_expiration_date DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_deals_user_id (user_id),
  KEY idx_deals_store_id (store_id),
  UNIQUE KEY uq_deal_unique (store_id, item_name),
  CONSTRAINT fk_deals_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_deals_store FOREIGN KEY (store_id) REFERENCES stores_list(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS deal_images (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  deal_item_id BIGINT UNSIGNED NOT NULL,
  image_thumb VARCHAR(500) NULL,
  image_original VARCHAR(500) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_deal_images_deal_id (deal_item_id),
  CONSTRAINT fk_deal_images_deal FOREIGN KEY (deal_item_id) REFERENCES deal_items(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS shopping_history (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  store_id BIGINT UNSIGNED NULL,
  store_name VARCHAR(190) NULL,
  store_location VARCHAR(255) NULL,
  store_logo VARCHAR(500) NULL,
  item_id BIGINT UNSIGNED NULL, -- original items_list id (if created from item status change)
  item_name VARCHAR(190) NULL,
  item_price DECIMAL(10,2) NULL,
  discount_amount DECIMAL(10,2) NULL,
  item_quantity DECIMAL(10,3) NULL,
  item_unit VARCHAR(30) NULL,
  total_price DECIMAL(10,2) NULL,
  contains_per_item DECIMAL(10,3) NULL,
  contains_per_item_unit VARCHAR(30) NULL,
  item_location_aisle VARCHAR(120) NULL,
  item_rating INT NULL,
  item_sort_order BIGINT UNSIGNED NOT NULL DEFAULT 0,
  status INT NOT NULL DEFAULT 1,
  item_sale_expiration_date DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_history_user_id (user_id),
  KEY idx_history_store_id (store_id),
  CONSTRAINT fk_history_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS shopping_history_images (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  item_id BIGINT UNSIGNED NOT NULL, -- refers to shopping_history.id per spec
  image_thumb VARCHAR(500) NULL,
  image_original VARCHAR(500) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_history_images_item_id (item_id),
  CONSTRAINT fk_history_images_item FOREIGN KEY (item_id) REFERENCES shopping_history(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS deal_push_notifications (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  store_name VARCHAR(190) NOT NULL,
  store_location VARCHAR(255) NOT NULL,
  deal_item_name VARCHAR(190) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_deal_push_user_id (user_id),
  CONSTRAINT fk_deal_push_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
