CREATE TABLE IF NOT EXISTS stores (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(190) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS monthly_reports (
  id INT AUTO_INCREMENT PRIMARY KEY,
  store_id INT NOT NULL,
  year SMALLINT NOT NULL,
  month TINYINT NOT NULL,
  walkins DECIMAL(10,2) NOT NULL DEFAULT 0,
  source_filename VARCHAR(255) NULL,
  imported_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_store_year_month (store_id, year, month),
  CONSTRAINT fk_monthly_reports_store FOREIGN KEY (store_id) REFERENCES stores(id)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS department_sales (
  id INT AUTO_INCREMENT PRIMARY KEY,
  report_id INT NOT NULL,
  department VARCHAR(190) NOT NULL,
  gross DECIMAL(12,2) NOT NULL DEFAULT 0,
  vat DECIMAL(12,2) NOT NULL DEFAULT 0,
  net DECIMAL(12,2) NOT NULL DEFAULT 0,
  vat_rate DECIMAL(6,4) NULL,
  category_share DECIMAL(8,4) NULL,
  CONSTRAINT fk_department_sales_report FOREIGN KEY (report_id) REFERENCES monthly_reports(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS vat_summaries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  report_id INT NOT NULL,
  vat_rate DECIMAL(6,4) NOT NULL,
  net DECIMAL(12,2) NOT NULL DEFAULT 0,
  vat DECIMAL(12,2) NOT NULL DEFAULT 0,
  total DECIMAL(12,2) NOT NULL DEFAULT 0,
  CONSTRAINT fk_vat_summaries_report FOREIGN KEY (report_id) REFERENCES monthly_reports(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
