-- SQL to create new tables for Regulatory Company Linking System

-- Create regulatory_link_types table
CREATE TABLE regulatory_link_types (
    id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    description varchar(255) NOT NULL,
    active int NOT NULL DEFAULT 1,
    addedby int NOT NULL,
    addedon bigint NOT NULL
);

-- Create regulatory_company_links table
CREATE TABLE regulatory_company_links (
    id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    regulatory_link_type_id int NOT NULL,
    companyid int NOT NULL,
    linked_companyid int NOT NULL,
    stockgroup varchar(255),
    stockref varchar(26),
    addedby int NOT NULL,
    addedon bigint NOT NULL
);

-- Insert default link types
INSERT INTO regulatory_link_types (description, active, addedby, addedon) VALUES
('UK Responsible Person', 1, 1, UNIX_TIMESTAMP()),
('EU Authorised Representative', 1, 1, UNIX_TIMESTAMP()),
('US Agent', 1, 1, UNIX_TIMESTAMP());