

-- Categories tabel
CREATE TABLE IF NOT EXISTS categories (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(100) NOT NULL UNIQUE,
  description TEXT,
  price_private DECIMAL(10,2),
  price_company DECIMAL(10,2),
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Regions tabel
CREATE TABLE IF NOT EXISTS regions (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(100) NOT NULL UNIQUE,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Companies tabel
CREATE TABLE IF NOT EXISTS companies (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  name VARCHAR(200) NOT NULL,
  slug VARCHAR(200) NOT NULL UNIQUE,
  description TEXT,
  website VARCHAR(255),
  email VARCHAR(255),
  phone VARCHAR(50),
  address VARCHAR(255),
  city VARCHAR(100),
  province VARCHAR(100),
  postal_code VARCHAR(20),
  logo_path VARCHAR(255),
  is_premium BOOLEAN DEFAULT FALSE,
  premium_expires_at TIMESTAMP NULL,
  deactivation_type VARCHAR(50),
  deactivated_at TIMESTAMP NULL,
  quotes_used INT DEFAULT 0,
  credits INT DEFAULT 0,
  is_trial BOOLEAN DEFAULT TRUE,
  trial_notification_sent BOOLEAN DEFAULT FALSE,
  monthly_quote_limit INT DEFAULT 4,
  free_returns_remaining INT DEFAULT 2,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Quote Requests tabel
CREATE TABLE IF NOT EXISTS quote_requests (
  id INT PRIMARY KEY AUTO_INCREMENT,
  category_id INT NOT NULL,
  region_id INT NOT NULL,
  company_id INT,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL,
  phone VARCHAR(50),
  message TEXT,
  form_data JSON,
  status VARCHAR(50) DEFAULT 'pending',
  amount DECIMAL(10,2),
  is_direct_quote BOOLEAN DEFAULT FALSE,
  is_direct BOOLEAN DEFAULT FALSE,
  submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Company Quote Requests tabel
CREATE TABLE IF NOT EXISTS company_quote_requests (
  id INT PRIMARY KEY AUTO_INCREMENT,
  quote_request_id INT NOT NULL,
  company_id INT NOT NULL,
  status VARCHAR(50) DEFAULT 'pending',
  quoted_amount DECIMAL(10,2),
  response_message TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Invoices tabel
CREATE TABLE IF NOT EXISTS invoices (
  id INT PRIMARY KEY AUTO_INCREMENT,
  company_id INT NOT NULL,
  invoice_number VARCHAR(20) NOT NULL UNIQUE,
  month INT NOT NULL,
  year INT NOT NULL,
  subtotal_amount DECIMAL(10,2) NOT NULL,
  vat_rate DECIMAL(5,2) NOT NULL,
  vat_amount DECIMAL(10,2) NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  status VARCHAR(50) DEFAULT 'pending',
  due_date TIMESTAMP NOT NULL,
  paid_at TIMESTAMP NULL,
  mollie_payment_id VARCHAR(100),
  mollie_payment_url VARCHAR(500),
  direct_quote_requests INT DEFAULT 0,
  direct_quote_amount DECIMAL(10,2) DEFAULT 0.00,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Settings tabel
CREATE TABLE IF NOT EXISTS settings (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL UNIQUE,
  value TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Content Pages tabel
CREATE TABLE IF NOT EXISTS content_pages (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(200) NOT NULL,
  content TEXT NOT NULL,
  type VARCHAR(50) DEFAULT 'standard',
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Basis instellingen
INSERT IGNORE INTO settings (name, value) VALUES 
('premium_price', '29.99'),
('vat_rate', '21.00'),
('site_name', 'OfferteLinker'),
('direct_quote_price', '5.00');

-- Test categorieën
INSERT IGNORE INTO categories (name, slug, description, price_private, price_company, is_active) VALUES 
('Webdesign', 'webdesign', 'Professionele website ontwikkeling', 2.50, 3.00, TRUE),
('Marketing', 'marketing', 'Online marketing diensten', 2.00, 2.50, TRUE),
('Fotografie', 'fotografie', 'Professionele fotografie', 1.50, 2.00, TRUE);

-- Test regio's
INSERT IGNORE INTO regions (name, slug, is_active) VALUES 
('Noord-Holland', 'noord-holland', TRUE),
('Zuid-Holland', 'zuid-holland', TRUE),
('Utrecht', 'utrecht', TRUE);

-- About Us pagina
INSERT IGNORE INTO content_pages (title, content, type, is_active) VALUES 
('Over Ons', '<h1>Welkom bij OfferteLinker</h1><p>Uw platform voor het verbinden van klanten met bedrijven.</p>', 'about-us', TRUE);

-- Succes bericht
SELECT 'Database setup voltooid! U kunt nu inloggen met admin@example.com / adminpass' AS Status;