CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) UNIQUE NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE banks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(50) UNIQUE NOT NULL,
  name VARCHAR(255) NOT NULL,
  logo_path VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO banks (code,name,logo_path) VALUES
 ('BT','Banca Transilvania','bt.png'),
 ('ING','ING Bank','ing.png'),
 ('BRD','BRD Group Societe Generale','brd.png'),
 ('BCR','BCR Bank','bcr.png'),
 ('RFI','Raiffeisen Bank SA','raiffeisen.png'),
 ('CEC','CEC Bank SA','cec.png'),
 ('FBN','First Bank SA','firstbank.png'),
 ('UCR','UniCredit Bank SA','unicredit.png'),
 ('RVL','Revolut LTD','revolut.png'),
 ('LBR','Libra Bank','libra.png'),
 ('GAR','Garanti Bank','garanti.png');
CREATE TABLE uploads (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  bank_id INT NULL,
  doc_type ENUM('settlement','z_report','unknown') DEFAULT 'unknown',
  original_name VARCHAR(255),
  file_path VARCHAR(255),
  uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  processed TINYINT(1) DEFAULT 0,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (bank_id) REFERENCES banks(id)
);
CREATE TABLE ocr_results (
  id INT AUTO_INCREMENT PRIMARY KEY,
  upload_id INT NOT NULL,
  ocr_json LONGTEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (upload_id) REFERENCES uploads(id) ON DELETE CASCADE
);
CREATE TABLE settlements (
  id INT AUTO_INCREMENT PRIMARY KEY,
  upload_id INT,
  bank_id INT,
  terminal_id VARCHAR(100),
  merchant VARCHAR(255),
  batch_no VARCHAR(50),
  date DATE,
  time TIME,
  total_amount DECIMAL(12,2),
  currency VARCHAR(10),
  transactions INT DEFAULT 0,
  raw_data LONGTEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (upload_id) REFERENCES uploads(id) ON DELETE CASCADE,
  FOREIGN KEY (bank_id) REFERENCES banks(id)
);
CREATE TABLE z_reports (
  id INT AUTO_INCREMENT PRIMARY KEY,
  upload_id INT,
  device_id VARCHAR(100),
  fiscal_date DATE,
  fiscal_shift INT,
  total_sales DECIMAL(12,2),
  vat_breakdown JSON NULL,
  raw_data LONGTEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (upload_id) REFERENCES uploads(id) ON DELETE CASCADE
);