SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS numbering_sequences (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL,
 entity_type VARCHAR(30) NOT NULL, prefix VARCHAR(20) NOT NULL, padding TINYINT UNSIGNED NOT NULL DEFAULT 6,
 next_value BIGINT UNSIGNED NOT NULL DEFAULT 1, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_numbering_company FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE RESTRICT,
 CONSTRAINT uq_numbering_company_type UNIQUE(company_id,entity_type),
 CONSTRAINT chk_numbering_type CHECK(entity_type IN('customer','supplier')),
 CONSTRAINT chk_numbering_padding CHECK(padding BETWEEN 3 AND 12)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS third_party_categories (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, party_type VARCHAR(20) NOT NULL,
 name VARCHAR(120) NOT NULL, description VARCHAR(255) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_party_categories_company FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE RESTRICT,
 CONSTRAINT uq_party_categories_name UNIQUE(company_id,party_type,name),
 CONSTRAINT chk_party_categories_type CHECK(party_type IN('customer','supplier')),
 INDEX idx_party_categories_filter(company_id,party_type,is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS third_parties (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, party_type VARCHAR(20) NOT NULL,
 code VARCHAR(40) NOT NULL, legal_type VARCHAR(30) NOT NULL, first_name VARCHAR(100) NULL, last_name VARCHAR(100) NULL,
 legal_name VARCHAR(190) NULL, trade_name VARCHAR(190) NULL, ice VARCHAR(40) NULL, tax_id VARCHAR(60) NULL,
 rc VARCHAR(60) NULL, cnss VARCHAR(60) NULL, fiscal_identifier VARCHAR(60) NULL,
 phone_primary VARCHAR(40) NULL, phone_secondary VARCHAR(40) NULL, whatsapp VARCHAR(40) NULL,
 email VARCHAR(190) NULL, website VARCHAR(255) NULL, address_line VARCHAR(255) NULL, city VARCHAR(100) NULL,
 postal_code VARCHAR(20) NULL, region VARCHAR(100) NULL, country_code CHAR(2) NOT NULL DEFAULT 'MA',
 preferred_language VARCHAR(10) NOT NULL DEFAULT 'fr', currency_code CHAR(3) NOT NULL DEFAULT 'MAD',
 category_id BIGINT UNSIGNED NULL, account_manager_id BIGINT UNSIGNED NULL,
 credit_limit DECIMAL(19,4) NOT NULL DEFAULT 0, payment_terms_days SMALLINT UNSIGNED NOT NULL DEFAULT 0,
 preferred_payment_method VARCHAR(80) NULL, default_discount DECIMAL(9,6) NOT NULL DEFAULT 0,
 bank_reference VARCHAR(120) NULL, rib VARCHAR(100) NULL, bank_name VARCHAR(120) NULL,
 internal_notes TEXT NULL, status VARCHAR(20) NOT NULL DEFAULT 'active', archived_at DATETIME NULL,
 created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_parties_company FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE RESTRICT,
 CONSTRAINT fk_parties_category FOREIGN KEY(category_id) REFERENCES third_party_categories(id) ON DELETE SET NULL,
 CONSTRAINT fk_parties_manager FOREIGN KEY(account_manager_id) REFERENCES users(id) ON DELETE SET NULL,
 CONSTRAINT fk_parties_creator FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE RESTRICT,
 CONSTRAINT uq_parties_code UNIQUE(company_id,party_type,code),
 CONSTRAINT chk_parties_type CHECK(party_type IN('customer','supplier')),
 CONSTRAINT chk_parties_status CHECK(status IN('active','inactive','archived')),
 CONSTRAINT chk_parties_credit CHECK(credit_limit>=0), CONSTRAINT chk_parties_discount CHECK(default_discount BETWEEN 0 AND 100),
 INDEX idx_parties_search(company_id,party_type,status,code), INDEX idx_parties_name(company_id,party_type,legal_name,last_name),
 INDEX idx_parties_category(category_id), INDEX idx_parties_city(city), INDEX idx_parties_ice(ice), INDEX idx_parties_email(email), INDEX idx_parties_phone(phone_primary), INDEX idx_parties_created(created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS third_party_addresses (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, third_party_id BIGINT UNSIGNED NOT NULL,
 address_type VARCHAR(20) NOT NULL, label VARCHAR(100) NOT NULL, contact_name VARCHAR(190) NULL, phone VARCHAR(40) NULL,
 address_line VARCHAR(255) NOT NULL, city VARCHAR(100) NOT NULL, postal_code VARCHAR(20) NULL, region VARCHAR(100) NULL,
 country_code CHAR(2) NOT NULL DEFAULT 'MA', is_default TINYINT(1) NOT NULL DEFAULT 0,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_addresses_company FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE RESTRICT,
 CONSTRAINT fk_addresses_party FOREIGN KEY(third_party_id) REFERENCES third_parties(id) ON DELETE CASCADE,
 CONSTRAINT chk_addresses_type CHECK(address_type IN('main','billing','delivery','warehouse','other')),
 INDEX idx_addresses_party(third_party_id,address_type,is_default)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS third_party_contacts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, third_party_id BIGINT UNSIGNED NOT NULL,
 civility VARCHAR(20) NULL, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, job_title VARCHAR(100) NULL,
 department VARCHAR(100) NULL, phone VARCHAR(40) NULL, whatsapp VARCHAR(40) NULL, email VARCHAR(190) NULL, notes TEXT NULL,
 is_primary TINYINT(1) NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_contacts_company FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE RESTRICT,
 CONSTRAINT fk_contacts_party FOREIGN KEY(third_party_id) REFERENCES third_parties(id) ON DELETE CASCADE,
 INDEX idx_contacts_party(third_party_id,is_active,is_primary), INDEX idx_contacts_email(email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS third_party_notes (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, third_party_id BIGINT UNSIGNED NOT NULL,
 author_id BIGINT UNSIGNED NOT NULL, content TEXT NOT NULL, visibility VARCHAR(30) NOT NULL, is_pinned TINYINT(1) NOT NULL DEFAULT 0,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_notes_company FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE RESTRICT,
 CONSTRAINT fk_notes_party FOREIGN KEY(third_party_id) REFERENCES third_parties(id) ON DELETE CASCADE,
 CONSTRAINT fk_notes_author FOREIGN KEY(author_id) REFERENCES users(id) ON DELETE RESTRICT,
 CONSTRAINT chk_notes_visibility CHECK(visibility IN('admin','sales','authorized')),
 INDEX idx_notes_party(third_party_id,is_pinned,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS third_party_documents (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, third_party_id BIGINT UNSIGNED NOT NULL,
 document_type VARCHAR(40) NOT NULL, description VARCHAR(255) NULL, original_name VARCHAR(255) NOT NULL,
 stored_name VARCHAR(255) NOT NULL, mime_type VARCHAR(120) NOT NULL, size_bytes BIGINT UNSIGNED NOT NULL, sha256 CHAR(64) NOT NULL,
 uploaded_by BIGINT UNSIGNED NOT NULL, document_date DATE NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_documents_company FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE RESTRICT,
 CONSTRAINT fk_documents_party FOREIGN KEY(third_party_id) REFERENCES third_parties(id) ON DELETE CASCADE,
 CONSTRAINT fk_documents_user FOREIGN KEY(uploaded_by) REFERENCES users(id) ON DELETE RESTRICT,
 CONSTRAINT uq_documents_stored UNIQUE(stored_name), INDEX idx_documents_party(third_party_id,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS third_party_history (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, third_party_id BIGINT UNSIGNED NOT NULL,
 user_id BIGINT UNSIGNED NULL, action VARCHAR(80) NOT NULL, description VARCHAR(500) NOT NULL, metadata JSON NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_party_history_company FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE RESTRICT,
 CONSTRAINT fk_party_history_party FOREIGN KEY(third_party_id) REFERENCES third_parties(id) ON DELETE CASCADE,
 CONSTRAINT fk_party_history_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX idx_party_history_party(third_party_id,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO numbering_sequences(company_id,entity_type,prefix,padding,next_value) VALUES(1,'customer','CLI-',6,1),(1,'supplier','FOUR-',6,1);
INSERT IGNORE INTO third_party_categories(company_id,party_type,name,description) VALUES
(1,'customer','Client standard','Catégorie client par défaut'),(1,'customer','Client professionnel',NULL),(1,'customer','Grossiste',NULL),(1,'customer','Revendeur',NULL),(1,'customer','Client VIP',NULL),(1,'customer','Administration',NULL),
(1,'supplier','Fournisseur principal','Catégorie fournisseur par défaut'),(1,'supplier','Fournisseur local',NULL),(1,'supplier','Importateur',NULL),(1,'supplier','Prestataire',NULL),(1,'supplier','Sous-traitant',NULL);

INSERT IGNORE INTO permissions(module,action,name) VALUES
('customers','update','Clients — Modifier'),('customers','archive','Clients — Archiver'),('customers','import','Clients — Importer'),('customers','view_financial','Clients — Voir les données financières'),('customers','manage_documents','Clients — Gérer les documents'),('customers','manage_notes','Clients — Gérer les notes'),
('suppliers','update','Fournisseurs — Modifier'),('suppliers','archive','Fournisseurs — Archiver'),('suppliers','import','Fournisseurs — Importer'),('suppliers','view_financial','Fournisseurs — Voir les données financières'),('suppliers','manage_documents','Fournisseurs — Gérer les documents'),('suppliers','manage_notes','Fournisseurs — Gérer les notes');
INSERT IGNORE INTO role_permissions(role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code='administrator' AND p.module IN('customers','suppliers');