Marktanalyse & Dienstenuitbreiding

-- Dienstcatalogus met uurtarieven
CREATE TABLE hourly_services (
    service_id      INT IDENTITY(1,1) PRIMARY KEY,
    service_name    NVARCHAR(100) NOT NULL,
    category        NVARCHAR(50)  NOT NULL,      -- 'mcp','api','llm','hosting','consultancy'
    default_rate    DECIMAL(10,2) NOT NULL,
    min_hours       DECIMAL(4,1)  DEFAULT 2.0,
    description_nl  NVARCHAR(500),
    description_en  NVARCHAR(500),
    active          BIT DEFAULT 1,
    sort_order      INT DEFAULT 0,
    created_at      DATETIME DEFAULT GETDATE()
);

-- Reseller-specifieke tariefoverschrijvingen
CREATE TABLE hourly_service_rates (
    rate_id         INT IDENTITY(1,1) PRIMARY KEY,
    service_id      INT NOT NULL REFERENCES hourly_services(service_id),
    reseller_id     NVARCHAR(10) NOT NULL,
    custom_rate     DECIMAL(10,2) NOT NULL,
    valid_from      DATE DEFAULT GETDATE(),
    valid_until     DATE NULL
);

-- Strippenkaarten / urenpakketten
CREATE TABLE hour_packages (
    package_id      INT IDENTITY(1,1) PRIMARY KEY,
    hours           INT NOT NULL,
    discount_pct    DECIMAL(4,1) NOT NULL,
    label_nl        NVARCHAR(50),
    label_en        NVARCHAR(50),
    active          BIT DEFAULT 1
);

-- Uurregistratie per klant
CREATE TABLE hourly_work_log (
    log_id          INT IDENTITY(1,1) PRIMARY KEY,
    klant_id        INT NOT NULL,
    reseller_id     NVARCHAR(10) NOT NULL,
    service_id      INT NOT NULL REFERENCES hourly_services(service_id),
    hours_worked    DECIMAL(5,2) NOT NULL,
    rate_applied    DECIMAL(10,2) NOT NULL,
    description     NVARCHAR(1000),
    work_date       DATE NOT NULL,
    invoice_id      INT NULL,
    package_id      INT NULL REFERENCES hour_packages(package_id),
    created_by      NVARCHAR(50),
    created_at      DATETIME DEFAULT GETDATE()
);