CREATE TABLE users (

    id INT AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(255) NOT NULL,

    email VARCHAR(255) UNIQUE NOT NULL,

    password VARCHAR(255) NOT NULL,

    role ENUM('admin','staff') DEFAULT 'staff',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

CREATE TABLE clients (

    id INT AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(255) NOT NULL,

    company VARCHAR(255),

    email VARCHAR(255),

    phone VARCHAR(20),

    address TEXT,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

CREATE TABLE invoices (

    id INT AUTO_INCREMENT PRIMARY KEY,

    invoice_number VARCHAR(20) UNIQUE NOT NULL,

    client_id INT NOT NULL,

    description TEXT,

    amount DECIMAL(10,2) NOT NULL,

    currency VARCHAR(3) DEFAULT 'USD',

    payment_provider ENUM('stripe','quickbooks','wix','manual') DEFAULT 'manual',

    payment_type ENUM('one-time','recurring') DEFAULT 'one-time',

    payment_link TEXT,

    qb_invoice_id VARCHAR(255),

    status ENUM('pending','paid','cancelled','overdue') DEFAULT 'pending',

    due_date DATE,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (client_id) REFERENCES clients(id)

);

CREATE TABLE payments (

    id INT AUTO_INCREMENT PRIMARY KEY,

    invoice_id INT NOT NULL,

    provider ENUM('stripe','quickbooks','wix','manual') NOT NULL,

    provider_transaction_id VARCHAR(255),

    amount DECIMAL(10,2) NOT NULL,

    currency VARCHAR(3) DEFAULT 'USD',

    status ENUM('paid','failed','pending') DEFAULT 'pending',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (invoice_id) REFERENCES invoices(id)

);

CREATE TABLE subscriptions (

    id INT AUTO_INCREMENT PRIMARY KEY,

    invoice_id INT,

    client_id INT NOT NULL,

    provider ENUM('stripe','quickbooks') NOT NULL,

    subscription_id VARCHAR(255),

    billing_cycle ENUM('weekly','monthly','yearly') DEFAULT 'monthly',

    amount DECIMAL(10,2) NOT NULL,

    currency VARCHAR(3) DEFAULT 'USD',

    status ENUM('active','cancelled','past_due') DEFAULT 'active',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (invoice_id) REFERENCES invoices(id),

    FOREIGN KEY (client_id) REFERENCES clients(id)

);

CREATE TABLE settings (

    id INT AUTO_INCREMENT PRIMARY KEY,

    setting_key VARCHAR(100) UNIQUE NOT NULL,

    setting_value TEXT,

    setting_group ENUM('stripe','quickbooks','wix','app') NOT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

);