DDL: dodavanje.sql

-- Insert data into Company table
INSERT INTO Company (
    company_id, company_name, company_representative, company_address, company_bank_accounts, company_vat,
    company_email, company_logo_url, created_at, updated_at
) VALUES
(1, 'AgencyOS Inc.', 'Naum Shapkarovski',
 '{"street": "123 Main St", "city": "Skopje", "state": "Macedonia", "zip": "1000"}',
 '[{"bank_name": "NLB", "iban": "MK123456789", "swift": "NLBMK22"}]',
 'MK12345678', 'info@agencyos.com', '/logos/agencyos.png', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(2, 'TechMasters', 'Ivan Petrovski',
 '{"street": "45 Innovation Lane", "city": "Tetovo", "state": "Macedonia", "zip": "1200"}',
 '[{"bank_name": "Halkbank", "iban": "MK987654321", "swift": "HALKMK22"}]',
 'MK98765432', 'contact@techmasters.com', '/logos/techmasters.png', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

-- Insert data into User table
INSERT INTO "User" (
    user_id, user_name, user_email, user_role, company_id, created_at, updated_at
) VALUES
(1, 'John Doe', 'john.doe@agencyos.com', 'Admin', 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(2, 'Jane Smith', 'jane.smith@agencyos.com', 'HR', 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(3, 'Mark Lee', 'mark.lee@techmasters.com', 'Finance Manager', 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

-- Insert data into Employee table
INSERT INTO Employee (
    employee_id, employee_name, employee_email, employee_role, employee_status,
    employee_address, employee_bank_info, employee_cv_ref, employee_photo_ref,
    company_id, created_at, updated_at
) VALUES
(1, 'Alice Brown', 'alice.brown@agencyos.com', 'Backend Developer', 'Active',
 '{"street":"123 Main St","city":"Skopje","state":"Macedonia","zip":"1000"}',
 'MK123456789',
 '/cvs/alice_brown.pdf', '/photos/alice.jpg', 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(2, 'Bob Green', 'bob.green@agencyos.com', 'Backend Developer', 'Active',
 '{"street":"123 Main St","city":"Skopje","state":"Macedonia","zip":"1000"}',
 'MK987654321',
 '/cvs/bob_green.pdf', '/photos/bob.jpg', 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(3, 'Charlie White', 'charlie.white@techmasters.com', 'Frontend Engineer', 'Active',
 '{"street":"45 Innovation Lane","city":"Tetovo","state":"Macedonia","zip":"1200"}',
 'MK112233445',
 '/cvs/charlie_white.pdf', '/photos/charlie.jpg', 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

-- Insert data into Candidate table
INSERT INTO Candidate (
    candidate_id, candidate_name, candidate_email, candidate_status,
    candidate_cv_ref, candidate_stage, candidate_notes, company_id, created_at, updated_at
) VALUES
(1, 'Eve Taylor', 'eve.taylor@gmail.com', 'Applied',
 '/cvs/eve_taylor.pdf', 'Initial Screening', 'Highly recommended', 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(2, 'Frank Harris', 'frank.harris@gmail.com', 'Interview',
 '/cvs/frank_harris.pdf', 'Technical Interview', 'Strong technical skills', 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

-- Insert data into Client table
INSERT INTO Client (
    client_id, client_name, client_representative, client_email,
    client_address, client_vat, client_logo_url, company_id, created_at, updated_at
) VALUES
(1, 'Global Enterprises', 'Sophia Johnson', 'sophia.johnson@globalent.com',
 '{"street":"456 Business Rd","city":"Bitola","state":"Macedonia","zip":"7000"}',
 'MK55443322', '/logos/global_ent.jpg', 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(2, 'NextGen Solutions', 'Michael Carter', 'michael.carter@nextgen.com',
 '{"street":"789 Innovation Blvd","city":"Ohrid","state":"Macedonia","zip":"6000"}',
 'MK66778899', '/logos/nextgen.jpg', 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

-- Insert data into Invoice table
INSERT INTO Invoice (
    invoice_id,
    invoice_no,
    invoice_issue_date,
    invoice_due_date,
    invoice_notes,
    invoice_status,
    invoice_currency,
    invoice_taxes,
    invoice_quantity_type,
    invoice_discount,
    invoice_month,
    invoice_pdf_ref,
    company_id,
    client_id,
    created_at,
    updated_at
) VALUES
(
    1,
    202501,
    '2025-01-15',
    '2025-02-15',
    'Payment for Project Alpha',
    'Pending',
    'EUR',
    18.50,
    'Hours',
    0.00,
    '2025-01',
    'invoices/202501.pdf',
    1,
    1,
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP
),
(
    2,
    202502,
    '2025-01-10',
    '2025-02-10',
    'Payment for Project Gamma',
    'Draft',
    'USD',
    12.00,
    'Deliverables',
    50.00,
    '2025-01',
    'invoices/202502.pdf',
    2,
    2,
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP
);

-- Insert data into LineItem table
INSERT INTO LineItem (
    item_id, description, quantity, unit_price, currency, total, invoice_id
) VALUES
(1, 'Custom Software Development', 100, 50.00, 'USD', 5000.00, 1),
(2, 'Maintenance and Support', 20, 75.00, 'USD', 1500.00, 1),
(3, 'Website Development', 50, 100.00, 'USD', 5000.00, 2);

-- Insert data into EmailTemplate table
INSERT INTO EmailTemplate (
    template_id, type, subject, body, company_id, created_at, updated_at
) VALUES
(1, 'New Invoice', 'Invoice Issued',
 'Dear {{client_name}},\n\nYour invoice #{{invoice_id}} has been issued.', 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(2, 'Welcome Employee', 'Welcome to AgencyOS',
 'Dear {{employee_name}},\n\nWelcome to the team at {{company_name}}!', 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);