-- DigitallOS / Agency OS Version 1 database schema
-- IMPORTANT: First select the database you created in cPanel/phpMyAdmin,
-- then import this file. This script intentionally does not create or select
-- a database because shared-hosting database users usually lack that permission.

CREATE TABLE IF NOT EXISTS users (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(120) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 role ENUM('owner','admin','manager','staff','client') NOT NULL DEFAULT 'staff',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS clients (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(190) NOT NULL,
 website VARCHAR(255) NULL,
 industry VARCHAR(120) NULL,
 status ENUM('active','prospect','paused','archived') DEFAULT 'active',
 notes TEXT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS contacts (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 client_id INT UNSIGNED NULL,
 first_name VARCHAR(100) NOT NULL,
 last_name VARCHAR(100) NULL,
 email VARCHAR(190) NULL,
 phone VARCHAR(50) NULL,
 company VARCHAR(190) NULL,
 lifecycle_stage VARCHAR(60) DEFAULT 'lead',
 source VARCHAR(120) NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX(client_id), CONSTRAINT fk_contacts_client FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE SET NULL
);
CREATE TABLE IF NOT EXISTS deals (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 client_id INT UNSIGNED NULL,
 contact_id INT UNSIGNED NULL,
 title VARCHAR(190) NOT NULL,
 value DECIMAL(12,2) DEFAULT 0,
 stage VARCHAR(80) DEFAULT 'New',
 probability TINYINT UNSIGNED DEFAULT 10,
 expected_close DATE NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX(client_id), INDEX(contact_id)
);
CREATE TABLE IF NOT EXISTS locations (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 client_id INT UNSIGNED NOT NULL,
 location_name VARCHAR(190) NOT NULL,
 address1 VARCHAR(190) NULL,
 city VARCHAR(120) NULL,
 state VARCHAR(60) NULL,
 postal_code VARCHAR(20) NULL,
 phone VARCHAR(50) NULL,
 county VARCHAR(120) NULL,
 communities TEXT NULL,
 services TEXT NULL,
 status ENUM('draft','review','approved','published') DEFAULT 'draft',
 wp_post_url VARCHAR(255) NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX(client_id)
);
CREATE TABLE IF NOT EXISTS submissions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 client_id INT UNSIGNED NULL,
 form_name VARCHAR(190) NOT NULL,
 name VARCHAR(190) NULL,
 email VARCHAR(190) NULL,
 phone VARCHAR(60) NULL,
 payload JSON NULL,
 source_url VARCHAR(500) NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX(client_id), INDEX(created_at)
);
CREATE TABLE IF NOT EXISTS attribution_events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 visitor_id VARCHAR(100) NULL,
 contact_id INT UNSIGNED NULL,
 event_name VARCHAR(120) NOT NULL,
 source VARCHAR(120) NULL,
 medium VARCHAR(120) NULL,
 campaign VARCHAR(190) NULL,
 landing_page VARCHAR(500) NULL,
 referrer VARCHAR(500) NULL,
 metadata JSON NULL,
 occurred_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX(visitor_id), INDEX(contact_id), INDEX(occurred_at)
);
CREATE TABLE IF NOT EXISTS approvals (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 client_id INT UNSIGNED NOT NULL,
 item_type VARCHAR(80) NOT NULL,
 item_id INT UNSIGNED NOT NULL,
 title VARCHAR(190) NOT NULL,
 status ENUM('pending','approved','changes_requested') DEFAULT 'pending',
 feedback TEXT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX(client_id), INDEX(item_type,item_id)
);
CREATE TABLE IF NOT EXISTS integrations (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 client_id INT UNSIGNED NULL,
 type VARCHAR(80) NOT NULL,
 name VARCHAR(190) NOT NULL,
 endpoint VARCHAR(500) NULL,
 secret_token VARCHAR(255) NULL,
 settings JSON NULL,
 enabled TINYINT(1) DEFAULT 1,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);