CREATE DATABASE IF NOT EXISTS db_162482_17 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE db_162482_17;

CREATE TABLE tenants (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  subdomain VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company VARCHAR(255),
  contact_name VARCHAR(255),
  email VARCHAR(255),
  phone VARCHAR(50),
  address TEXT,
  vat_id VARCHAR(100),
  tenant_id INT NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sku VARCHAR(100),
  name VARCHAR(255),
  description TEXT,
  price DECIMAL(12,2) NOT NULL,
  tax_rate DECIMAL(5,2) DEFAULT 20.00,
  tenant_id INT NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE invoices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_number VARCHAR(100) UNIQUE NOT NULL,
  user_id INT NOT NULL,
  customer_id INT NOT NULL,
  date DATE NOT NULL,
  due_date DATE NULL,
  status ENUM('draft','sent','paid','cancelled') DEFAULT 'draft',
  subtotal DECIMAL(12,2) DEFAULT 0,
  tax_total DECIMAL(12,2) DEFAULT 0,
  total DECIMAL(12,2) DEFAULT 0,
  notes TEXT,
  tenant_id INT NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE invoice_lines (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_id INT NOT NULL,
  product_id INT NULL,
  description TEXT,
  qty DECIMAL(10,2) DEFAULT 1,
  unit_price DECIMAL(12,2) NOT NULL,
  tax_rate DECIMAL(5,2) DEFAULT 20.00,
  line_total DECIMAL(12,2) NOT NULL,
  tenant_id INT NOT NULL DEFAULT 1,
  FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
);

CREATE TABLE payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  invoice_id INT NULL,
  amount DECIMAL(12,2) NOT NULL,
  currency VARCHAR(10) DEFAULT 'EUR',
  paid_at DATETIME,
  source VARCHAR(50),
  raw_reference TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE bank_imports (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  file_name VARCHAR(255),
  imported_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  raw LONGTEXT,
  parsed JSON NULL
);

CREATE TABLE recurring_profiles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  name VARCHAR(255),
  customer_id INT,
  template_invoice_id INT NULL,
  period ENUM('monthly','quarterly','yearly') DEFAULT 'monthly',
  next_run DATE,
  active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE api_keys (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  user_id INT NOT NULL,
  api_key VARCHAR(64) UNIQUE NOT NULL,
  label VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO tenants (name, subdomain) VALUES ('Default Tenant','sev');
