-- =============================================
-- SHARK CRM - Database Schema
-- =============================================

CREATE DATABASE IF NOT EXISTS shark_crm CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE shark_crm;

-- Accounts (tenants)
CREATE TABLE accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_name VARCHAR(150) NOT NULL,
    business_name VARCHAR(200) NOT NULL,
    phone VARCHAR(30),
    email VARCHAR(150),
    status ENUM('active','inactive') DEFAULT 'active',
    currency VARCHAR(10) DEFAULT 'USD',
    datetime_created DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Roles
CREATE TABLE roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    permissions JSON,
    account_id INT NOT NULL,
    FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE
);

-- Users
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    phone VARCHAR(30),
    email VARCHAR(150) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role_id INT,
    account_id INT NOT NULL,
    avatar VARCHAR(255),
    datetime_created DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE SET NULL
);

-- Item Types
CREATE TABLE item_type (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

INSERT INTO item_type (name) VALUES ('Product'), ('Service');

-- Items (Products / Services)
CREATE TABLE items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    image VARCHAR(255),
    description TEXT,
    price DECIMAL(15,2) DEFAULT 0.00,
    items_type_id INT,
    account_id INT NOT NULL,
    date_created DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (items_type_id) REFERENCES item_type(id),
    FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE
);

-- Pipeline Stages
CREATE TABLE stage (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    position INT DEFAULT 0,
    color VARCHAR(20) DEFAULT '#00e5c3',
    is_start TINYINT(1) DEFAULT 0,
    is_end TINYINT(1) DEFAULT 0,
    account_id INT NOT NULL,
    FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE
);

-- Customers
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(200) NOT NULL,
    business_name VARCHAR(200),
    phone VARCHAR(30),
    email VARCHAR(150),
    priority TINYINT DEFAULT 3,
    account_id INT NOT NULL,
    date_created DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE
);

-- Customer Contacts
CREATE TABLE customer_contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    job_title VARCHAR(150),
    phone VARCHAR(30),
    email VARCHAR(150),
    customer_id INT NOT NULL,
    datetime_created DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

-- Leads
CREATE TABLE leads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    customer_id INT NOT NULL,
    item_id INT,
    stage_id INT,
    priority TINYINT DEFAULT 3,
    opportunity TINYINT DEFAULT 5,
    estimated_price DECIMAL(15,2) DEFAULT 0.00,
    assigned_user_id INT,
    status ENUM('open','won','lost') DEFAULT 'open',
    lost_reason_id INT,
    account_id INT NOT NULL,
    datetime_created DATETIME DEFAULT CURRENT_TIMESTAMP,
    datetime_updated DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE SET NULL,
    FOREIGN KEY (stage_id) REFERENCES stage(id) ON DELETE SET NULL,
    FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE
);

-- Activity Types
CREATE TABLE activity_type (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    icon VARCHAR(50)
);

INSERT INTO activity_type (name, icon) VALUES
('Call', 'phone'),
('Meeting', 'users'),
('Email', 'mail'),
('WhatsApp', 'message-circle'),
('SMS', 'message-square'),
('Note', 'file-text'),
('Follow Up', 'refresh-cw');

-- Lead Activities
CREATE TABLE lead_activities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    lead_id INT NOT NULL,
    user_id INT NOT NULL,
    text TEXT,
    activity_type_id INT,
    datetime DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (activity_type_id) REFERENCES activity_type(id)
);

-- Lead Follow-ups (comments / timeline)
CREATE TABLE lead_followup (
    id INT AUTO_INCREMENT PRIMARY KEY,
    lead_id INT NOT NULL,
    user_id INT NOT NULL,
    comment TEXT NOT NULL,
    datetime DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Lost Reasons
CREATE TABLE lost_reason (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    account_id INT NOT NULL,
    date_created DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE
);

-- Reminders
CREATE TABLE reminder (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date DATE NOT NULL,
    time TIME,
    comment TEXT,
    customer_id INT,
    lead_id INT,
    user_id INT NOT NULL,
    activity_type_id INT,
    status ENUM('pending','done','cancelled') DEFAULT 'pending',
    finish_at DATETIME,
    account_id INT NOT NULL,
    datetime_created DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
    FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE SET NULL,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (activity_type_id) REFERENCES activity_type(id),
    FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE
);

-- =============================================
-- SEED DATA - Demo Account
-- =============================================

-- Demo account
INSERT INTO accounts (client_name, business_name, phone, email, status, currency)
VALUES ('Admin User', 'Shark CRM Demo', '+1234567890', 'admin@sharkcrm.com', 'active', 'USD');

-- Default role
INSERT INTO roles (name, permissions, account_id)
VALUES ('Administrator', '{"all": true}', 1),
       ('Sales Rep', '{"leads": true, "customers": true, "reminders": true}', 1);

-- Default admin user (password: admin123)
INSERT INTO users (name, phone, email, password, role_id, account_id)
VALUES ('Admin User', '+1234567890', 'admin@sharkcrm.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1, 1);

-- Default stages
INSERT INTO stage (name, position, color, is_start, is_end, account_id) VALUES
('New Lead', 1, '#00e5c3', 1, 0, 1),
('Contacted', 2, '#3b82f6', 0, 0, 1),
('Proposal Sent', 3, '#f59e0b', 0, 0, 1),
('Negotiation', 4, '#f97316', 0, 0, 1),
('Won', 5, '#10b981', 0, 1, 1),
('Lost', 6, '#ef4444', 0, 1, 1);

-- Default lost reasons
INSERT INTO lost_reason (name, account_id) VALUES
('Price too high', 1),
('Went with competitor', 1),
('No budget', 1),
('Not interested', 1),
('No response', 1);

-- Sample customers
INSERT INTO customers (customer_name, business_name, phone, email, priority, account_id) VALUES
('John Smith', 'TechCorp Inc.', '+1555000001', 'john@techcorp.com', 4, 1),
('Sarah Johnson', 'Creative Agency', '+1555000002', 'sarah@creative.com', 5, 1),
('Mike Davis', 'DataSoft LLC', '+1555000003', 'mike@datasoft.com', 3, 1);

-- Sample items
INSERT INTO items (name, description, price, items_type_id, account_id) VALUES
('CRM Setup Package', 'Full CRM setup and configuration', 2500.00, 2, 1),
('Annual Support Plan', 'Yearly technical support subscription', 1200.00, 2, 1),
('Hardware Bundle', 'Server and networking equipment', 5000.00, 1, 1);

-- Sample leads
INSERT INTO leads (title, customer_id, item_id, stage_id, priority, opportunity, estimated_price, assigned_user_id, account_id) VALUES
('TechCorp CRM Deal', 1, 1, 1, 4, 70, 2500.00, 1, 1),
('Creative Agency Support', 2, 2, 3, 5, 85, 1200.00, 1, 1),
('DataSoft Hardware', 3, 3, 2, 3, 50, 5000.00, 1, 1);
