-- Schema MySQL/MariaDB pentru Return Platform (Sameday-first)

CREATE TABLE IF NOT EXISTS admins (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  username VARCHAR(64) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- user initial: admin / admin123
INSERT INTO admins (username, password_hash)
VALUES ('admin', '$2b$10$KyfQ53av2Bb/pV1c1qcabeoTTjM0d6m7CW9N8VgBomj7GyUxtz6TK')
ON DUPLICATE KEY UPDATE username=username;

CREATE TABLE IF NOT EXISTS sameday_tokens (
  id TINYINT UNSIGNED NOT NULL,
  token VARCHAR(128) NOT NULL,
  expire_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS returns (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_code VARCHAR(32) NOT NULL UNIQUE,
  status VARCHAR(20) NOT NULL DEFAULT 'new',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,

  order_id VARCHAR(64) NULL,
  website VARCHAR(128) NULL,

  customer_name VARCHAR(255) NOT NULL,
  customer_phone VARCHAR(64) NOT NULL,
  customer_email VARCHAR(255) NOT NULL,
  person_type TINYINT NOT NULL DEFAULT 0,
  company_name VARCHAR(255) NULL,

  pickup_county_id INT NULL,
  pickup_county VARCHAR(128) NOT NULL,
  pickup_city_id INT NULL,
  pickup_city VARCHAR(128) NOT NULL,
  pickup_extra_km DECIMAL(8,2) NOT NULL DEFAULT 0.00,
  pickup_address VARCHAR(255) NOT NULL,
  easybox_dropoff TINYINT(1) NOT NULL DEFAULT 0,
  easybox_locker_id INT NULL,
  easybox_locker_name VARCHAR(255) NULL,
  easybox_locker_address VARCHAR(255) NULL,
  easybox_locker_city VARCHAR(128) NULL,
  easybox_locker_county VARCHAR(128) NULL,
  pickup_postcode VARCHAR(32) NULL,
  handover_method VARCHAR(32) NOT NULL DEFAULT 'courier',
  dropoff_location VARCHAR(128) NULL,
  reason_code VARCHAR(64) NULL,
  reason_details TEXT NULL,
  product_condition VARCHAR(64) NULL,

  return_reason TEXT NULL,
  refund_method VARCHAR(32) NULL,
  refund_iban VARCHAR(64) NULL,
  refund_bank VARCHAR(64) NULL,
  notes TEXT NULL,
  agreed_terms TINYINT NOT NULL DEFAULT 0,

  package_weight DECIMAL(10,2) NOT NULL DEFAULT 1.00,
  package_length INT NOT NULL DEFAULT 0,
  package_width INT NOT NULL DEFAULT 0,
  package_height INT NOT NULL DEFAULT 0,

  awb_courier VARCHAR(32) NULL,
  awb_number VARCHAR(64) NULL,
  awb_label_path VARCHAR(255) NULL,
  awb_sent_email_at DATETIME NULL,
  awb_sent_whatsapp_at DATETIME NULL,
  awb_last_error TEXT NULL,
  awb_created_at DATETIME NULL,
  awb_payload_json LONGTEXT NULL,

  PRIMARY KEY (id),
  KEY idx_status (status),
  KEY idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS return_items (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  return_id INT UNSIGNED NOT NULL,
  product_name VARCHAR(255) NOT NULL,
  sku VARCHAR(128) NULL,
  quantity INT NOT NULL DEFAULT 1,
  reason VARCHAR(255) NULL,
  condition_note VARCHAR(255) NULL,
  serial_number VARCHAR(255) NULL,
  PRIMARY KEY (id),
  KEY idx_return_id (return_id),
  CONSTRAINT fk_return_items_return FOREIGN KEY (return_id) REFERENCES returns(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



-- Cache geolocație Sameday (județe/localități) pentru a nu depinde constant de API
CREATE TABLE IF NOT EXISTS sameday_geo_counties (
  id INT NOT NULL PRIMARY KEY,
  name VARCHAR(128) NOT NULL,
  code VARCHAR(16) NULL,
  updated_at DATETIME NOT NULL,
  KEY idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sameday_geo_cities (
  id INT NOT NULL PRIMARY KEY,
  county_id INT NOT NULL,
  name VARCHAR(128) NOT NULL,
  extra_km DECIMAL(8,2) NULL,
  updated_at DATETIME NOT NULL,
  KEY idx_county (county_id),
  KEY idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- (Optional) Cache lockere; util dacă pe viitor vrei listare locală
CREATE TABLE IF NOT EXISTS sameday_lockers (
  id INT NOT NULL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  address VARCHAR(255) NULL,
  city VARCHAR(128) NULL,
  county VARCHAR(128) NULL,
  lat DECIMAL(10,7) NULL,
  lng DECIMAL(10,7) NULL,
  updated_at DATETIME NOT NULL,
  KEY idx_city (city),
  KEY idx_county (county)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
